recursive SQL and SQL result to array...

[Anon]

Basicly I want to make something like a tree view forum (like the one at http://forum.qb45.com/index.php?board=php )

Say my table has the following fields

id, reply_to, message, user, date, subject

The reply_to field points the 'id' name of the parent message. If the message is the root message of the thread, the value will be 0.

what I want to know, is there a way of making a SQL query that is recursive (I guess that's the right spelling), so that I could use one single SQL query to read out a message, and all of it's replies in to a result that held the 'tree' structure of the replies. Once this is done, how would I make this result into an array, without lossing the 'tree' structure.

If someone could give me some pusdeo-cod for this. I'm using MySQL (but using the PEAR DB Lib).

Thanks in advance to all who reply

Jason

[Anon]

I guess there is no way of do ing recursion in a SQL query, and I would like to retain compatiblity (not have to make it so my code isn't hard coded to one RMDBS).

Anyway, is there an easy way to format this data into an array, even if it means not being able to do recursion at a SQL level.

[sayoko]

How about you set a "level" column in your database. 1 till how many levels you want.
Get the data from the database and with each level, start a new branch.
Or you could read the values into and array, something like this:

$data[level][message]
$data[level][subject]

and so on.

[sayoko]

Oh, boo boo, this would erase data, the array should be like this:

$data[unique_index][level][message]