How to display hierarchical "NESTED SET" data with PHP?

Question

I'm trying to figure out how to display nested MySQL data using php. I've managed to set aside all the "leaf nodes" but then I got stucked. I need to display a whole tree and all of it's element's relations. Here's the table

category_id, name, lft, rgt
1 Saws 1 12
2 Chainsaws 2 7
3 Red 3 4
4 Yellow 5 6
5 Circular saws 8 9
6 Other saws 10 11

Here is code:

$query = 'SELECT node.name, node.lft, node.rgt
    FROM item_cats AS node,
        item_cats AS parent
    WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.name = "' . SAWS . '"
    ORDER BY node.lft';
$result = mysql_query($query, $db) or die (mysql_error($db));
while ($row = mysql_fetch_assoc($result)) {
    if ($row['rgt'] == $row['lft']+1) {
        echo '==>';
    }
    echo $row['lft'];
    echo $row['name'];
    echo $row['rgt'];
    echo '<br />';
    echo '<br />';
}

And this is what i get:

1Saws12
2Chainsaws7
==>3Red4
==>5Yellow6
==>8Circular saws9
==>10Other saws11

@AdamPlocher [Nested Set Model](http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/), scroll down to the 'The Nested Set Model' heading, the first bit's about a different technique — Stu, Mar 18 '13 at 07:27

Adam Plocher · Accepted Answer · 2013-03-18T07:47:21.147

Based on the link Stu showed me, the tutorial shows this query for determining depth:

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft

So something like this should work:

<?PHP
$query = 'SELECT node.name, (COUNT(parent.name) - 1) AS depth
    FROM nested_category AS node,
            nested_category AS parent
    WHERE node.lft BETWEEN parent.lft AND parent.rgt
    GROUP BY node.name
    ORDER BY node.lft';

$result = mysql_query($query, $db) or die (mysql_error($db));
while ($row = mysql_fetch_assoc($result)) {
    for ($i = 0; $i < $row['depth']; $i++) {
        echo '==>';
    }

    echo $row['name'];
    echo '<br />';
    echo '<br />';
}
?>

This should output:

Saws
==>Chainsaws
==>==>Red
==>==>Yellow
==>Circular Saws
==>Other Saws

Ahh shoot, sorry I just found a bug... that `where parent.name="saws"` is causing the depth to return 0... Let me try to fix it. — Adam Plocher, Mar 18 '13 at 07:48
Oh awesome. You're welcome. I'm going to change my answer so it doesn't have that `parent.name` condition. — Adam Plocher, Mar 18 '13 at 07:51

score 1 · Answer 2 · answered Jun 14 '13 at 01:34

<?PHP
$query = '
    select if(
        count(a.name) - 1 = 0, 
        a.name, 
        concat(repeat('   ', count(a.name) - 2), '+--', b.name)
    )name
    from nested_category b, nested_category a
    where node.lft between a.lft and a.rgt
    group by b.name
    order by b.lft';

$result = mysql_query($query, $db) or die (mysql_error($db));
while ($row = mysql_fetch_assoc($result)) echo "{$row['name']}<br>";
?>

Should do sth like:

Saws
+--Chainsaws
   +--Red
   +--Yellow
+--Circular Saws
+--Other Saws

How to display hierarchical "NESTED SET" data with PHP?

2 Answers2

Linked