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

Question

There is no limitation on the depth.

How to get the structured branch or even entire tree?

The definition is from here: Managing Hierarchical Data in MySQL

Spoonbill

Answer 1

I'm not sure it's quite what you're asking for, but it's worth noting you can get the entire tree, one line per path, each path as a string as follows purely in MySQL by using GROUP_CONCAT and expanding on the "Retrieving a Single Path" example from http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

SELECT 
  GROUP_CONCAT(parent.name ORDER BY parent.lft ASC SEPARATOR '|') 
FROM nested_category AS node
     CROSS JOIN nested_category AS parent 
WHERE 
  node.lft BETWEEN parent.lft AND parent.rgt 
GROUP by node.id 
ORDER BY node.lft;

This will output the paths for every node in the tree.

Note that nested_category AS node CROSS JOIN nested_category AS parent is equivalent to nested_category AS node, nested_category AS parent.

This uses specifies the string '|' as the separator, if you want to explode this into an array of path elements & you know there's a string that's not in your data you could specify that instead.

Epifocal

with this solution, you can use a well-formed eval() inside of a foreach() to quickly parse into a nested array in 2 lines of code. Security-wise, this assumes category data is fully trusted. – Soso

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;
}   

Odontology

Also, this link here (articles.sitepoint.com/article/hierarchical-data-database/2) is much more easily digestible, but describes exactly the same technique. – Odontology

Answer 3

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.

Bink

Answer 4

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.

Manicotti

The whole point of the technique in the article is to avoid having left joins / multiple queries. The problem here is not the query (with the approach described all you need is "SELECT * FROM nested_category ORDER BY lft ASC") but how to convert the results of the query into a structure afterwards. – Odontology

Ok, in this case ignore my post. Sorry – Manicotti

Answer 5

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"

Ingrain