How to get structed result using nested set in MySQL and PHP?

Question

There is no limitation on the depth.

How to get the structed branch or even entire tree?

The definition is from here:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Answer 1

Even if the mysql-side data structure is somewhat exotic, the data is still retrieved using normal query methods. Issue the appropriate select statement, loop over the results, and stuff it into a PHP array. Though I don't know why you'd want to, as it would be much harder to do the set operations in PHP than in MySQL.

Answer 2

I use a similar, but not quite the same, approach, which also keeps the a reference to the parent in the child; this makes building a tree structure from the data easier. If this is useful, I can post the code for extracting the data into a tree in PHP.

@Marc, the data structure described isn't necessarily for doing set operations; it just makes working with the structure easier. If you want to get an entire tree of data and each record just stores a pointer to the parent record, then you need to recursively query the database to get the full tree of data. If you use the approach described there, then you can extract the entire set in one query.

Edit: here's the code that builds a tree structure IF you maintain a child -> parent reference as well as the lft/right stuff. I prefer to do this, because it's actually still faster this way if you only want to get the direct descendents of a single level of the tree.

I've tried to strip it back to demonstrate the essentials, so there may be some typos etc. but you should get the idea. The key parts are

• Order your query by "lft ASC", this way you will always process a parent node before its children.
• Store a reference to each node by ID; this way any child of that node can easily find it and add itself to the parent.
• Iterate through the results, store reference for each by ID (as above) and add this node to the children of its parent.

Anyway, here's the code -

<?php
$children = mysql_query('SELECT * FROM nested_category ORDER BY lft ASC');

/* Get the first child; because the query was ordered by lft ASC, this is
   the "root" of the tree */
$child          = mysql_fetch_object($children);
$root           = new StdClass;
$root->id       = $child->folderID;
$root->children = array();
/* Store a reference to the object by the id, so that children can add
   themselves to it when we come across them */
$objects        = array($root->id => $root);

/* Build a tree structure */
while ($child = mysql_fetch_object($children)) {
    /* Create a new wrapper for the data */
    $obj           = new StdClass;
    $obj->id       = $child->id;
    $obj->children = array();
    /* Append the child to the parent children */
    $parent = $objects[$child->parent];
    $parent->children[] = $obj;
    $objects[$obj->id] = $obj;
}   

Answer 3

Looking at your link I would do it with Left Joins. Look at the example for Retrieving a Full Tree.

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4 FROM category AS t1 LEFT JOIN category AS t2 ON t2.parent = t1.category_id LEFT JOIN category AS t3 ON t3.parent = t2.category_id LEFT JOIN category AS t4 ON t4.parent = t3.category_id WHERE t1.name = 'ELECTRONICS';

You wold need an LEFT JOIN for every hierarchical level you want to include. The result then can be parsed by php into any desirable data structure. Just ignore NULL results.

| ELECTRONICS | TELEVISIONS | TUBE | NULL |

| ELECTRONICS | TELEVISIONS | LCD | NULL |

| ELECTRONICS | TELEVISIONS | PLASMA | NULL |

| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |

| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL |

| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL |

When you have a deep structure this would be a worse methode because MySQL Joins need a long time to execute when many tables need to be joined.

I hope I didn't misunderstand your question.

Answer 4

Hello user198729,

I have to inform you about the method through which you can work on tree structures using php.. without recursive. I think you are very much known with standard php library (SPL). You can use Iterators for your question.

http://www.php.net/~helly/php/ext/spl/

here is the link for the documentation for SPL. here are some solutions for your example above of Mysql link :- By simply retrieving your array from table you can work on them and display as your prefrence

For :- The Adjacency List Model

You can use "RecursiveIteratorIterator" which will show all results including all the childs.

If you only want to show the childs. you can use "ParentIterator"