menu unlimited levels ?

[pixelsoul]

What would be the best way to retrieve a menu structure with all the childeren?
I have a column in the database like

id | parent_id | name
1                   animals
2    1              cows
3    1              birds
4    3              eagles
5    3              sparrows

etc etc..

All i can think off right now is to get all items then create a function to loop every item.. then do a query on every item to see if any rows have the same with parent id as it's own id.. and do the same again for that child. But that would require me to do many queries if the menu would grow large. Does anybody have any suggestions, example's, ideas to create this in a cleaner way ?

[gavwvin]

see this thread:
http://www.sitepoint.com/forums/showthread.php?t=348369

[pixelsoul]

hmmm i don't know if that really helps me:
http://www.sitepoint.com/article/hie...ata-database/2

Storing the relationships like that so that the numbers are between a left and right number makes it pretty confusing :s

Not too mention it still uses a query to retrieve each child of a node.. which if its becomes many can perform many queries.. there should be one way. To don't have to make that many queries when you got all the data from the table with one query it should be possible to calculate the entire tree..

[gavwvin]

Not too mention it still uses a query to retrieve each child of a node

You don't need a query for each child, you can get the whole thing in one query (or all the descendents, or ancestors in one go)
You might be able to write some sort of script which looped through the entire result set sorting it into parents and children just with parent id's, if you wanted to do it that way, or if you were prepared to limit the number of levels you could fetch it using a join as r397 suggested in that thread.

[pixelsoul]

THe use of the left and right is so silly.. let's say for example home contains 1 to 10 and suddenly you get 11 childeren...

You will need to write a function which checks this everytime and if it needs to be changed.. all and every items need to be re-calculated. So it creates more problems then solve them

[pixelsoul]

Nobody has a good solution for this

[Dr Livingston]

> Does anybody have any suggestions, example's, ideas to create this in a cleaner way ?

Have you checked out the script that I posted on this thread?

http://www.sitepoint.com/forums/showthread.php?t=261258

Pretty much makes the Modified Preorder Traversal Tree method obsolete

[NikJazz]

pixelsoul, before you decide to go for the 'easier' parent field as an identifier you should think about performance etc. as well.

Using treetraversal is as far as I know the only way so you can see rellations between nodes who are not neighbours in one go. If you only use a parent field you have do make a lot of queries to find out the relationship between nodes.

You are right though that you have to update all nodes ones you insert a new one but thats really simple SQL, and still only requires very few queries.
So for performance I can only suggest tree traversal.
(What if you want to fint the 50 parents of one node? You want to make 50 queries if you can make one?)

If you use treetraversal though, don't do it with a parent field at all. Its kindof obsolete since you define relations with the left and right ids. Use a level field instead which will always tell you how deep in the tree you are...

So think about it again - I can only suggest tree traversal over using simple parent ids - especially if you want to generate nested menus. The code for that is so much shorter than otherwise...

[ikeo]

If you are using PHP 5 you might want to consider using XML to store your menu.
IK

What would be the best way to retrieve a menu structure with all the childeren?
I have a column in the database like

 id | parent_id | name
 1                   animals
 2    1              cows
 3    1              birds
 4    3              eagles
 5    3              sparrows

etc etc..

All i can think off right now is to get all items then create a function to loop every item.. then do a query on every item to see if any rows have the same with parent id as it's own id.. and do the same again for that child. But that would require me to do many queries if the menu would grow large. Does anybody have any suggestions, example's, ideas to create this in a cleaner way ?

[Dr Livingston]

> If you only use a parent field you have do make a lot of queries to find out the relationship between
> nodes.

Normally this is how it's done - the tree recursion is based on the database recordset, however with the approach I use, and linked to, you do not recurse over the database, but the results that you pull out; In this case, it's only the one database query

To be honest with you, I couldn't recommend the MPTT approach due to the complexities required to do anything useful with it, based on the fact that the script I posted gives a higher performance over other, more tradional Adjacency List scripts.

Also, if you create a cache, you can cache the generated structure, so you basically only regenerate the cache on an Insert, Update or Delete of that tree, otherwise you use the cache

[NikJazz]

Also, if you create a cache, you can cache the generated structure, so you basically only regenerate the cache on an Insert, Update or Delete of that tree, otherwise you use the cache

Thats totally true! And probably the performance issue in fact is not that big - especially if you retrieve all data in one query..
Still I like the approach of numbering nodes since that is the only way to really simply see relationships between nodes (you have to keep a level field though..)..
How do you easily want to explain the relationship between the node with the parent id '45' and the node with the parent id '75'?

Writing a class with methods like nodeAdd(), nodeRemove(), nodesRender(), nodeMove() is no problem and data integrity is no issue as well (I haven't gotten any problems so far) as long as you use transactions...

I totally get your point though and probably we could discuss for hours what is the better approach.

Personally I find it more elegant since MPTT gives you an overview of the whole rather then a layered (recursive) view...

By the way - if you google MPTT in google.de you get 'Mehrdimensionale Psychodynamische Trauma-Therapie – MPTT' which translates to Multidimensional Psychodynamical Trauma Therapy MAybe thats what we're looking for?

[pixelsoul]

Ah i finally got it working with one query...

I have php5 btw and am very interested in the approach that xml, would take.. does anybody know of a good example, I only saw examples where there was a list of items being generated, but now how to fetch nested childs and see at which level they are... would be cool to test it out what is really the fastest approach.

    function BuildMenu($rows, $id = 0, $level = 0) {
        
        $numerOfElements = 0;
        $menu = "";
     
        for($i = 0; $i < sizeof($rows); $i++) {
          if($rows[$i]['parent_id'] == $id) {
              
              $categoryid = $rows[$i]['id'];
            $menu .= "<li>
                        <a href=\"index.php?category=$categoryid\" class=\"menuItem{$level}\">{$rows[$i]['name']}</a>
                      </li>";
            $menu .= $this->BuildMenu($rows, $rows[$i]['id'], $level + 1);
     
            $numerOfElements++;
          }
        }
     
        if($numerOfElements > 0 && !empty($menu))
        {
          $menu = "<ul class=\"menuLevel\">{$menu}</ul>";
        }
     
        return $menu;
      }