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

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.

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