Category with Subcategory PHP/MySQL

PHP
1

Hello all,

Im having difficulties with the concept of implementing Categories with x levels of sub category.

So far I have decided on a MySQL table such as:

ID Name ParentID
1 Cat 1
2 Cat 2
3 Cat 3 1
4 Cat 4 2
5 Cat 5 1

This problem with this design comes with how to extract the data?

How would I display the data such as;

Main Cat: Cat 1
Sub Cats: Cat 3, Cat 5

Main Cat: Cat 2

etc.

Preferably in an unordered list?

Can anyone recommend the best way to implement this? Any links to good resources? Anyone done this before? How did you implement it?

Thanks in advance! :cool:

2

a quick example (you may want to convert this down to objects later)


// Get top level categories
$res_top = mysql_query("SELECT * FROM Categories WHERE ParentID IS NULL");
while($cat_top = mysql_fetch_object($res) {
// Get current cat's sub level categories
$res_sub = mysql_query("SELECT * FROM Categories WHERE ParentID = ".$cat_top->id);
while($cat_sub = mysql_fetch_object($res_sub) {
// Handle each occurrence of a sub level category
}
}
3

Thanks CoderMaya! What is the best way to implement a system that allows unlimited category and subcategory lookup? If I know there is only one level of sub category I can search for 1 level, 2 levels I can search for two etc. How would I make it possible to have unlimited sub categories and be able to search for them without hard coding a search for each level?

4

The first way that comes to mind right now is a recursive function.

5

a query inside a loop is almost always a very bad idea

i would rewrite the stuff in post #2 as a single join query

unlimited number of levels usually means you should be using the nested set data model and not the adjacency model (which uses parent_id)

however, one big problem with unlimited levels is how do you show them on a web page? what kind of masochistic user is going to be willing to drill down seventeen levels???

see Categories and Subcategories

:slight_smile:

6

if the sub categories do not have sub sub categories I would do.

category table
id |category name |

sub cat table
parent id | sublevel name | sub level (int) |

query cat table echo out the category name.
query the subcat table select and select where cat.id =‘subcat.parentid’ order by sublevel.
echo out each sub level name that is ORDERED BY on the sublevel table.

Im a total noob but thats my 2c.

I would probably have to ask r937 how to optimally pull off the query tho.

7

like i said, a query inside a loop is almost always a very bad idea

:cool:

8

I was thinking it could be done with a join, I have something similar going and need to look to see if there is a query inside of a loop, I’ll bet I did the unspeakable.