Returning a tree structure from table

Hello all!

I'm trying to get a tree structure from a table.
The table looks like this:
id
parent_id
description
sortorder

rows with parent_id = 0 is the nodes of the tree

I would like to have a list returned ordered by sortorder but structured like a tree.

Like this (number indicates sortorder):
parent1
child1
child4
child8
child9
child6
parent2
child2
child3
parent3
child5
child7
child8

Please help me with a smart select statement

Regards, Larre

Where is the child table and relational field(s)?
-Dave

All is contained in the same table:

The table looks something like this:

id-------parent_ID-------description
1----------0----------------Main1
2----------0----------------Main2
3----------0----------------Main3
4----------1----------------Sub1
5----------1----------------Sub1
6----------2----------------Sub1
7----------3----------------Sub1
8----------5----------------Sub2
9----------5----------------Sub2
10---------3----------------Sub1

It also contains a sortOrder column to sort the individual nodes under same parent.

Is this clearer?

Regards, Larre

?? I'm not clear on what you want the output to look like ?? Order By parent_ID, description? Can you give me what you want the output to look like given the data you supplied in the previous example?
-Dave

OK, given this table:

id-------parent_ID-------description
1----------0----------------Main1
2----------0----------------Main2
3----------0----------------Main3
4----------1----------------Sub1
5----------1----------------Sub1
6----------2----------------Sub1
7----------3----------------Sub1
8----------5----------------Sub2
9----------5----------------Sub2
10---------3----------------Sub1

The output should be:

Main1 (id1)
---Sub1 (id4)
---Sub1 (id5)
------Sub2 (id8)
------Sub2 (id9)
Main2 (id2)
---Sub1 (id6)
Main3 (id3)
---Sub1 (id7)
---Sub1 (id10)

Thanks for your time so far

Best regards, Larre

this is called threading. nuke's forums used to do this (I really liked it) but now they gave it up along with all the rest of the most popular forums. I like it cuz it keeps everything more organized and you can have infanent conversations under the same topic, insted of mods deleting or locking double's all the time. that's something I really hate. but anyway.. threading is a great way to do a lot of things if it's done right. it's hard to keep the code running fast. that's the biggest problem.

here's how I do it. it's basicly a controlled endless loop. it will open a function in itself over and over until it reaches the end of a branch, then goes back until it finds more branches to process. this is only an example. you'll have to use my theory to write your own code

function do_thread($thread_id){
query("SELECT id FROM table WHERE thread=".$thread_id)
if(row_count!=0){
while($row){
echo $row[id]
do_thread($row[id])
}
}
}
do_thread(0)

that should give you the basic idea of it. you might also want a safety in that function to keep it from doing a real endless loop somehow. say, if it goes more then 10 levels down into the tree, it will stop going down that branch and finish the rest. yo umight also want some kind of cache, cuz it will have to do a lot of query's each time the code is run. if anyone has any ideas on how to do threading like this with just one query, please let me know!