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, scroll down to the 'The Nested Set Model' heading, the first bit's about a different technique — Stu
– Stu, Commented Mar 18, 2013 at 7:27

Adam Plocher · Accepted Answer · 2013-03-18 07:47:21Z

3

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

edited Mar 18, 2013 at 7:47

answered Mar 18, 2013 at 7:37

Adam Plocher

14.3k6 gold badges51 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adam Plocher Over a year ago

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 Over a year ago

Oh awesome. You're welcome. I'm going to change my answer so it doesn't have that parent.name condition.

Marek Lisiecki · Accepted Answer · 2013-06-14 01:34:38Z

1

<?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

answered Jun 14, 2013 at 1:34

Marek Lisiecki

3094 silver badges4 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related