Dropdown and nested Categories and Sub Categories

I’ve got a form for adding a product to and I want to select the categories via a drop down box.

My Categories DB makeup is as follows:

CREATE TABLE `categories` (
`Category_ID` int(10) NOT NULL auto_increment,
`Category_Name` varchar(50) NOT NULL,
`Category_Image` varchar(60) NOT NULL,
`Category_Date` date NOT NULL,
`Category_Parent` int(10) NOT NULL,
`Category_Order` varchar(5) NOT NULL,
PRIMARY KEY (`Category_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

When the category is a sub category the parents category ID is used in the parent box.

What I want to do is create my drop down HTML box so that the category names are ordered nicely and nested under each other.

I’ve written the following PHP which works for up to 2 levels, but I want it to keep nesting until the levels are finished. I’m guessing I need a while loop somewhere but can’t work it out. Any one care to crack this for me?

[code=php]<select name=”VenueID” size=”1″ class=”FormTB”>
<OPTION value=””>SELECT</OPTION><?
require(‘inc_dbconnect.php’);
$currentquery = “select * from categories
WHERE Category_Parent = 0
Order by Category_Name asc”;

$result = mysql_query($currentquery);
$num_results = mysql_num_rows($result);

for ($i=0; $i < $num_results; $i++)
{
$row = mysql_fetch_array($result);
$current_cat_name = $row[‘Category_Name’];
$current_cat_id = $row[‘Category_ID’];

$sub_qry_1 = “select * from categories
WHERE Category_Parent = ‘”.$current_cat_id.”‘
Order by Category_Name asc”;

$result_1 = mysql_query($sub_qry_1);
$num_results_1 = mysql_num_rows($result_1);

if ($num_results_1 > 0)
{
?><optgroup label=”<?=$current_cat_name;?>”><?
for ($i2=0; $i2 < $num_results_1; $i2++)
{
$row_1 = mysql_fetch_array($result_1);
?><OPTION value=”<?=$row_1[‘Category_ID’];?>”><?=$row_1[‘Category_Name’];?></OPTION>
<?
}
?></optgroup><? }
else { ?><OPTION value=”<?=$current_cat_id;?>”><?=$current_cat_name;?></OPTION><? }
}

?>
</select>[/code]

Also in #PHP _↴