Recursive categories with a single query?

I have a website with articles and sections, each sections can have a parent section, as much as they like for example:

subject 1
 -subject 2 
 --subject 3
 -subject 4
 --subject 5
 --subject 6
 ---subject 7
subject 8
subject 9

etc..

Now, i want to fetch them recursively, what is the most efficient way to do it via php and mysql?

Tnx in advanced.

Solution 1:

If the tree isn't too large, you can simply build the tree in PHP using some clever references.

$nodeList = array();
$tree     = array();

$query = mysql_query("SELECT category_id, name, parent FROM categories ORDER BY parent");
while($row = mysql_fetch_assoc($query)){
    $nodeList[$row['category_id']] = array_merge($row, array('children' => array()));
}
mysql_free_result($query);

foreach ($nodeList as $nodeId => &$node) {
    if (!$node['parent'] || !array_key_exists($node['parent'], $nodeList)) {
        $tree[] = &$node;
    } else {
        $nodeList[$node['parent']]['children'][] = &$node;
    }
}
unset($node);
unset($nodeList);

This will give you the tree structure in $tree with the children in the respective children-slot.

We've done this with fairly large trees ( >> 1000 items) and it's very stable and a lot faster than doing recursive queries in MySQL.

Solution 2:

That depends on how you have stored your data. There is a good article on MySQL.com called Managing Hierarchical Data in MySQL that talks about this.

Solution 3:

Well, you can fetch all the categories in an array in just the one query, as you know:

$query = "SELECT `name`,`id` from `table`";

Having that in an array, you can build the tree with some nested loops. It won't be fast, but is simple, and faster than using recursive queries. Also, you can cache the built tree and not having to rebuild it every time.

Solution 4:

You could have a look at this topic : how to get the hierarchical menu from mysql that was opened yesterday and is about the same thing.