Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

MySQL vs recursion

Codes here !

Moderators: macek, egami, gesf

MySQL vs recursion

Postby Flood » Tue Jun 24, 2003 5:00 am

Hi!

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:
category1
- subcategory1
-- subsubcategory1
-- subsubcategory2
- subcategory2
category2
...

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!

/Flood

PS: I forgot to say: each category is made of c_id (category id), c_rootId (top-level ancestor id), c_parentId (parent id).
Flood
New php-forum User
New php-forum User
 
Posts: 23
Joined: Tue Jun 24, 2003 4:49 am

Postby Redcircle » 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.
User avatar
Redcircle
Moderator
Moderator
 
Posts: 830
Joined: Tue Jan 21, 2003 10:42 pm
Location: Michigan USA


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 2 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.