Listing Categories and Sub Categories nicely in a Drop Down

[sanchez_1960]

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(3) 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=6 ;

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.

Any ideas on the PHP code to do this?

[holyhttp]

The first potential problem I can see in your database design is there is no way of distinguishing a category from a subcategory.
I suggest you have tow different tables: one for the categories and another for subcategories:

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


CREATE TABLE `subcategories` (
`SubCategory_ID` int(3) NOT NULL auto_increment,
`SubCategory_Name` varchar(50) NOT NULL Unique,
`SubCategory_Image` varchar(60) NOT NULL,
`SubCategory_Date` date NOT NULL,
`SubCategory_Order` varchar(5) NOT NULL,
`Category_IDt` int(3) NOT NULL,
PRIMARY KEY (`SubCategory_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

Notice the UNIQUEness of the Category_Name and SubCategory_Name.

First in your SQL select statement make sure you order the resultset by Category_Name first then SubCategory_Name:

$query="Select t1.Category_ID, t1.Category_Name, t2.SubCategory_ID, t2.SubCategory_Name from t1 as categories, t2 as subcategories where t2.Category_ID=t1.Category_ID order by t1.Category_Name asc, t2.SuCategory_Name asc";

$result=mysql_query($query, $dblink); /*$dblink is your database link; */


$NavStr=''; /* your navigation HTML string */

if($result!=false){
$curid=''; $counter=0;
while($row=mysql_fetch_assoc($result)){
$NavStr.=GetCategoryStr($row, $curid, $counter);
$NavStr.=GetSubCategoryStr($row);
$curid=$row['Category_ID'];
$counter++;
}
if($NavStr!=''){$NavStr.='</ul></li></ul>'; }
}
else{$ErrMsge.=mysql_error(); }



function GetCategoryStr($row, $curid, $counter){
if($curid!=$row['Category_ID']){
if($counter==0){$Str='<ul>';}
else {$Str='</ul></li>'; }
$Str.='<li><a href="category.php?id='..$row['Category_ID']'">'.$row['Category_Name'].'</a><ul>';
}
else {$Str=''; }
return $Str;
);

function GetSubCategoryStr($row){
$Str='<li><a href="subcategory.php?id='.$row['SubCategoryID'].'">'.$row['SubCategory_Name'].'</a></li>';

return $Str;
};


===============================
For the CSS visit http://www.alistapart.com/articles/horizdropdowns

[temp.user123]

The first potential problem I can see in your database design is there is no way of distinguishing a category from a subcategory.
That's what `Category_Parent` is for. Though that column aught to be indexed for performance reasons. But, if it is a small table, then no matter.

[holyhttp]

It's about good database design: defining entities clearly. By the way each Category is identified by Category_ID int(3) whereas Category_Parent int(10). How about being more consistent or more problems to follow.

[temp.user123]

I would agree with that -- I was just pointing out the intent in the design.

[sanchez_1960]

Firstly, thank you for the replies. The Category_Parent should have been 10, it was a typo in my DB creation. I'm not THAT stupid normally lol.

I've used the Category and SubCategory many times before. However, I need the categories to be multi level and nest down as many times as required on this perticular one i'm doing.

[temp.user123]

The Category_Parent should have been 10...
You mean Category_ID should have been 10.

[sanchez_1960]

You mean Category_ID should have been 10.

Yes! You'll have to excuse me I was really hungover on Friday.

Anyone any ideas of using my single table approach.

[sanchez_1960]

Bump....any ideas people?

[temp.user123]

I would use a query like this for subcategories:

Select * from `categories` as subc
Left Join `categories` as main
On subc.Category_Parent = main.Category_ID;

Then you just have to control break when subc.Category_Parent changes. However, nesting the subcategories in an HTML SELECT is not so easy -- an unordered list is easier.

[cluettr]

Use javascript rather than PHP :) It would allow you to dynamically populate the second select based on the first without refreshing a page.

[sanchez_1960]

I only want a single drop down tho.

[sanchez_1960]

anyone? :(