Multilevel Tree

Question

I have db table where menu items are placed. My table have 3 columns: id, parent_id and name. I want to build tree from this table. How it can be done?

You need a recursive function, look up subentries using the current entries parent_id by querying them using `parent_id = $id`. — mario, Feb 25 '13 at 17:58
See also http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/192462#192462 — Bill Karwin, Feb 25 '13 at 18:42

Shikhar Kapoor · Answer 1 · 2013-02-25T18:38:39.690

2

Try this code

$query = "SELECT * FROM menu_items";
$result = mysql_query($query);

if (mysql_num_rows($result) > 0) {
    $myTreeArray = array();
    while ($row = mysql_fetch_assoc($result)) {
        if(!isset($myTreeArray[$row['parent_id']])){
            $myTreeArray[$row['parent_id']] = array();
        }
        array_push($myTreeArray[$row['parent_id']], array($row['id'] => $row['name']));
    }
}

echo '<pre>';
print_r($myTreeArray);
echo '</pre>';

This would give you an associative array with a list of menu items indexed by their parent id's. You can then loop over $myTreeArray and print the list.

edited Feb 25 '13 at 18:38

answered Feb 25 '13 at 18:16

Shikhar Kapoor

41
4

it it not working maybe there is some error in this line array_push($myTreeArray[$row['parent_id']], array($row['id'] => $row['name'])); – moonvader Feb 25 '13 at 18:26
It would be great if you could post the line number and the error that you encountered :) – Shikhar Kapoor Feb 25 '13 at 18:28
thnk you! now it is working and i can see associative array. do i need recursive php function to build tree now? – moonvader Feb 25 '13 at 18:41
Just a plain nested foreach($myTreeArray as $key => $value) should do the trick now. – Shikhar Kapoor Feb 25 '13 at 18:44
can you suggest this nested foreach? i don't know how to do it recorsive – moonvader Feb 25 '13 at 18:50

Matt Browne · Answer 2 · 2013-02-25T18:07:38.800

1

While it's possible to do this without nested sets, nested sets will make your queries much more efficient.

Here are some good tutorials on how to set it up:

http://www.sitepoint.com/hierarchical-data-database/ (it doesn't actually use the term nested sets, but page 2 describes the same concept under the heading "Modified Preorder Tree Traversal")

http://mycrazydream.net/2009/04/nested-sets-for-category-structure-in-a-mysql-database/

edited Feb 25 '13 at 18:07

answered Feb 25 '13 at 18:02

Matt Browne

12,169
4
59
75

thank you! first tutorial looks like very similar to my case. nested sets are good but this time i have table structure without left and right keys. – moonvader Feb 25 '13 at 18:04
@Matt - Your suggestion on using nested sets is helpful to me on the mysql side. I am also handling the hierarchical data in my database and the tree has around 1 million nodes in total. Will the nested sets still work for me (considering the amount of nodes to be updated for left and right values whenever a new node is added in the tree)? – Slowcoder Feb 25 '13 at 18:25
@Slowcoder Yes I think nested sets would be a good solution in your case. It will most likely be faster than any other approach using a relational database. Non-SQL ("NoSQL") databases are becoming increasingly popular these days, and for many good reasons, two of which are better support for hierarchical data and better scalability, but in most cases especially if you haven't built a lot of systems yet I would say stick with SQL as there are more resources on learning it and it works well for most systems. – Matt Browne Feb 25 '13 at 18:33
Thank you Matt. I read the article in [link](http://www.fliquidstudios.com/2008/12/23/nested-set-in-mysql/) . The Drawback section says that Nested Sets may not work out well for a tree with large number of nodes so we can use Nested Intervals instead. Have to read what it is. – Slowcoder Feb 25 '13 at 19:10
@Slowcoder Ah, interesting, I always thought nested sets were fastest regardless but I'm not an expert in the area. Of course you should consider how you're going to be querying the tree, e.g. how much data you plan to query at once and whether there are any natural divisions that could be split across different tables or something. – Matt Browne Feb 25 '13 at 19:14
@MattB. - Querying will definitely be faster even in larger tree. But everytime when a new node is added deep down the tree, the left and right values have to be updated for all the nodes in that hierarchy for the left right and right. I will analyze how it performs in my case. Thanks for your suggestions. – Slowcoder Feb 25 '13 at 19:24

Multilevel Tree

2 Answers2