Displaying looped results

micmac

Hi all,
Having a hard time getting this to work. Lets say i have the following:

<SELECT name="knowledge_category_parent"> 
  <OPTION value="1">Category Root</OPTION> 
<?
$query_knowledgebase_categories = mysql_query("SELECT * FROM knowledgebase_categories where knowledge_category_parent = '1' AND knowledge_category_status = 'yes' ORDER BY knowledge_category_order");
while($row = mysql_fetch_array($query_knowledgebase_categories)){
$knowledge_category_id = $row["knowledge_category_id"];
$knowledge_category_parent = $row["knowledge_category_parent"];
$knowledge_category_name = stripslashes($row["knowledge_category_name"]);
?> 
<option value="<? echo $knowledge_category_id ?>"><? echo $knowledge_category_name ?></option> 
<? 
} 
?>				
</SELECT>

and here is an example of my database structure:

knowledge_category_id  |  knowledge_category_parent  |  knowledge_category_name
1  |  0  |  Home
2  |  1  |  FAQ
3  |  1  |  Support
4  |  1  |  Manual
5  |  2  |  Ordering
6  |  5  |  Credit Cards

What i want the select box to display is the $knowledge_category_name (which it does now) but if the category has a sub category i want it to display ots parent as well as the knowledge_category_name.
So, using the above DB as the example, the select box results should look something like:

<OPTION value="1">Category Root</OPTION>
<OPTION value="1">FAQ</OPTION>
<OPTION value="1">Support</OPTION>
<OPTION value="1">Manual</OPTION>
<OPTION value="1">FAQ - Ordering</OPTION>
<OPTION value="1">FAQ - Ordering - Credit Cards</OPTION>

Any help would be appreciated.
Cheers,
micmac

yelvington

This is probably a SQL problem.

Check the value returned by the query, and if it is zero, echo a diagnostic using mysql_error(). Another technique when you get stumped like this is to use the mysql command-line tool and pass the query to it ... see if you get back the results you were seeking.

Your SQL query uses quote marks around '1' ... if that's supposed to be an integer, don't pass it as a string. And where in the table is the column knowledge_category_status? Why would you use a string ('yes') to indicate status rather than an integer value (perhaps boolean)?

Your PHP looks (at this time of the morning) to be valid, but you're doing a remarkable amount of redundant copying of information. You know, you can directly echo those array variables.