Listing Categories and Sub Categories nicely in a Drop Down

@sanchez_1960Jul 05.2007

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?

to post a comment

PHP

12 Comments(s) _↴

@holyhttpJul 05.2007 — #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_user123Jul 05.2007 — #The first potential problem I can see in your database design is there is no way of distinguishing a category from a subcategory.[/QUOTE]
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.

@holyhttpJul 06.2007 — #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_user123Jul 06.2007 — #I would agree with that -- I was just pointing out the intent in the design.

@sanchez_1960authorJul 06.2007 — #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_user123Jul 06.2007 — #The Category_Parent should have been 10...[/QUOTE]
You mean Category_ID should have been 10.

@sanchez_1960authorJul 09.2007 — #You mean Category_ID should have been 10.[/QUOTE]

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

Anyone any ideas of using my single table approach.

@sanchez_1960authorJul 11.2007 — #Bump....any ideas people?

@temp_user123Jul 11.2007 — #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 [B]subc.Category_Parent[/B] changes. However, nesting the subcategories in an HTML SELECT is not so easy -- an unordered list is easier.

@cluettrJul 12.2007 — #Use javascript rather than PHP ? It would allow you to dynamically populate the second select based on the first without refreshing a page.

@sanchez_1960authorJul 12.2007 — #I only want a single drop down tho.

@sanchez_1960authorJul 24.2007 — #anyone? ?

Also in #PHP _↴

Please help with this timed sql_query Creating a new file within a new directory read folder

Success!

Help @sanchez_1960 spread the word by sharing this article on Twitter...

Tweet This

Listing Categories and Sub Categories nicely in a Drop Down

12 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Listing Categories and Sub Categories nicely in a Drop Down

12 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

12 Comments(s) _↴

Also in #PHP _↴