Here's an article that looks right ... (quick google search) http://www.dbazine.com/tropashko4.shtml
More information on this is contained in the "SQL for Smarties" book (which is an excellent book) by Joe Celko. There are some other online
articles dealing with nested set -- try google.
*Also* to make your life much easier you might want to have a look at the PEAR DB_NestedSet package. This makes the nested set queries much simpler.
Cheers, Hans
Age Bosma wrote:
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
