Infinite-layers of categorization

Hi,

I'm wondering if there's a way to imlement an infinite amount of layers of categorizations.

For examle, my forums on my site are categorized in forum categories.
Thus, I have a table called forumTopics like this:
int forumId
int forumCategory

And a table called forumCategories
int forumCategory
text forumCategoryName

I'm wondering if there's a way to support an infinite amount of layers of categorization. For simplicity, my child layers do not need to support more than 1 parent.

Thus, it could just be something like this:
1a
2a
3a
4a-1 4a-1
5a-1-1 5a-1-2 5a-1-1

Er... I hope that made sense! Thanks.

Well, here's the way I've always done thing like that...for simplicity, I will only deal with a table with two columns. id and parent_id. Each item in the table will have it's own unique id. Each will have a parent_id that corresponds to another id in the table. Top level items have a parent of 0.

So, when you want to pull all top level items, just pull WHERE parent_id = 0. Then, once an items is clicked and you are looking for subs under it, WHERE parent_id = id_that_was_clicked.

Ah, thanks...

Hm... Yup, I was thinking of a way to do it with only 2 tables, but this way's pretty good. With only 1 table, min. of 3 fields: int id primary key, int id parent_id, text description

Is it possible to fetch all parents of a layer without using n mysql calls if it is n layers deep? Can I sort-of hack it, something like:
select * from layers a left join layers b where b.id=a.parentId left join layers c where c.id=b.parentId ?

yes, but only if you know how "deep" the category is. if you know that cat is on 7th level, you can use 7 joins much like you shown.

(u can add another column that holds level, and store it when you add category)

but it wont work any better than when you do it from php. sql server still has to do it 7 times... (it may even work slower?)

Hm... how big is the overhead to do mysql_query() in PHP?

I always assumed that it is just better to lessen the number of calls PHP needs to do... but I think you're right in that the performance for the mySQL server is no better and could even be worse.