WEBProject WEBProject - 1 year ago 73
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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download