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
And:
SELECT parent.name AS parent
, sub1.name AS sub1
, sub2.name AS sub2
FROM categories
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!
It seems the SQL is quite easy, so I guess handling the actual coding would be the trickier part. I guess it would take a lot of IF statements to see if the categories went any deeper.
for example, if you wrote the query to go 4 levels deep, you could display 3 levels, indented or whatever, and then have a “more…” link, that, when clicked, would call the same page, which would then go 4 levels down from there
Well I was thinking more like a case like my photo album, where you drill down from one page to the next. Something like gallery -> sub1 -> sub2 -> pics in one album,. and gallery -> sub1 -> pics in another.
Right now I have only one possible level of subcategory, so that’s easy to code for without doing much thinking (and I probably won’t need anything beyond that) but I was looking at some open source galleries, and one of them said “infinite levels of sub-albums” and I sort of wondered how they did it.
