parent categories combo box

[Why_not]

Hi,
i insert category and parent in my sql database.
| id | name | pid

now how to show categories with parent in combo box ?

example :

cat1
-- parent
cat2
-- parent
cat3
-- parent
-----child
cat4
--parent
-----parent
----------child

etc...

thanks for any help and reply.

[coopster]

Ah yes, the age-old question and issue of categories and sub-categories. You are using a list model (or adjacency list as some call it) where each category has a unique identifier. If it is a subcategory of an existing category it gets a parent identifier associated with it. And if it is a parent category, the parent identifier is typically zero or null.

You would use a JOIN statement which is going to be compound, joining the table to itself with an ALIAS to get the top category and its direct child categories. But then for each subcategory you are going to need yet another JOIN and another ALIAS. Your query is going to have to be modified to adapt to every possible level or depth of subcategories.

OR

You could execute the top level category query and then execute an additional query for any subcategory. You would do this in control structure (like a loop) until all possible categories have been queried. This method is practiced much more than the previous. Have a look at the old osCommerce shopping cart system as an example.

Another method is to use is commonly called "nested set" or "hierarchical tree" strategies. Here you still have a unique identifier but a left and right column as well, showing where in the set/tree the uniquely identified category falls.

More reading on nested sets can be found from the original author and I'll include a link to a popular MySQL article too.
[kamfonas.com...]
[dev.mysql.com...]