I'd recommend you search the PHP archives for an answer. This question comes up a few times a year and the last time I remember seeing it, somebody (John Holmes, I think) provided an excellent discourse on the different ways to approach this solution. You'll probably get other new responses with recommendations as to how to proceed as well, but the archives should yield a wealth of information on this issue in a relatively short period of time. HTH, Rich > -----Original Message----- > From: Age Bosma [mailto:agebosma@xxxxxxx] > Sent: Friday, March 19, 2004 9:48 AM > To: php-db@xxxxxxxxxxxxx > Subject: MySQL category tree db sorting > > > 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 > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
