How to handle an unknown number of subcategories
A while ago I read rudy's page on Categories and Subcategories, which was not only a big help with my photo gallery, but also with a few projects at work. But rudy's sample begged a question that I'm just now getting around to asking.
Consider the following:
id | cat_name | parent_id
SELECT parent.name AS parent
, sub1.name AS sub1
, sub2.name AS sub2
LEFT JOIN categories AS sub1
ON sub1.parent_id = categories.id
LEFT JOIN categories AS sub2
AS sub2.parent_id = sub1.id
WHERE parent.parent_id IS NULL
ORDER BY parent, sub1, sub3
This all works really well IF you know how many levels of subcategory you have. But what if this is an application where a user can create subcategories, and you have no idea how many levels deep they'll go?
Do you programatically limit how deep the user can go? Do you count the number of levels deep they've gone and alter the query to suit? Just curious!