MySQL category tree db sorting

[Age Bosma <agebosma@xxxxxxx>]

I'm trying to work out what the most efficient way will be to get the 
complete tree structure from top to bottom of a category tree db. 
Starting with the first main branch listing it's first child branch 
followed by it's children, after that the second child branch, ect. When 
the first main branch is done it should start with the second main 
branch, etc.

I've got a category tree db with the following columns:

cat_id - Unique id for each row
parent_id - cat_id of its parent, 0 if it has none
prev_sibling_id - cat_id of it's previous sibling, 0 if it has none
next_sibling_id - cat_id of it's next sibling, 0 if it has none.

To get the complete tree stucture from the db, is it possible to get the 
sequence from top to bottom by one sql query, should the whole table be 
gotten and sorted using php or does it require multiple (nested) 
query's? (or a combination of both)
If all the rows are correctly sorted I can use php to determine which 
position it has in the tree if I run by each row one by one.

I could at least group by parent_id but sorting in the quiry can hardly 
be done because the id itself tells nothing about the position or 
sequence in the tree.
If the prev_sibling=0 it could be placed first and if next_silbing=0 it 
could be placed last (per parent group) but again you have no controle 
over the rows in between, am I right about this so far?

What would be the best solution?

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php