WEBProject WEBProject - 2 months ago 5x
PHP Question

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


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

Tnx in advanced.


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()));

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

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.