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:
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).