MySQL vs recursion

Codes here !

Moderators: egami, macek, gesf

Post Reply
New php-forum User
New php-forum User
Posts: 23
Joined: Tue Jun 24, 2003 4:49 am

Tue Jun 24, 2003 5:00 am


I would define my problem as a classic one, but I have not found any way to solve it yet. It deals with SQL and recursion.

Let's assume that I want to create my own forum. A forum is defined by a set of categories, a set of subcategories for each of those categories, a set of subsubcategories and so on.
I want to display all of them using the pattern:
- subcategory1
-- subsubcategory1
-- subsubcategory2
- subcategory2

I know it would be quite easy to do so by generating a lot of SQL requests: for instance, by creating a recurrent PHP function taking a category ID as a parameter and finding all its subcategories and for each of them call again that function by passing the subcategory ID as a parameter.
Ok it is easy to do; But the problem is I want to limit the number of transactions between the SQL server and the HTTP server. In other words, I do not want to send 1000 requests if I can do it using a couple of them only...

I have thought of some field in the database that would help to sort the tuples according to their expected order while being displayed. I mean, category1 would get something like #1, subcategory1 #2 and so on. But I found it hard to define such a "flag", especially because subcategories can be created at any time and are not know at the global creation of the database...

Do you have any idea or any hint?

Thanks a lot for your help!


PS: I forgot to say: each category is made of c_id (category id), c_rootId (top-level ancestor id), c_parentId (parent id).

User avatar
Posts: 826
Joined: Tue Jan 21, 2003 10:42 pm
Location: Michigan USA

Tue Jun 24, 2003 11:59 pm

personally I do not know if it is possible to do it without a bunch of sql calls like you mentioned. BUT. you may beable to limit the calls if you call the hierachy once and store the info in a global array. But then again it won't be 100% accurate if you have a busy site with lots of new sub cats added.

P.S. if you find a way to do that solely in sql let me know. I only know how to do it with the ancestor/child functions like you mentioned.

Post Reply