SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru
    Join Date
    Jul 2004
    Location
    Netherlands
    Posts
    672
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    menu unlimited levels ?

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

    Code:
    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 ?
    Go visit my site :-D you know you want to ;-)
    www.mech7.net

  2. #2
    SitePoint Guru gavwvin's Avatar
    Join Date
    Nov 2004
    Location
    Cornwall, UK
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  3. #3
    SitePoint Guru
    Join Date
    Jul 2004
    Location
    Netherlands
    Posts
    672
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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..
    Go visit my site :-D you know you want to ;-)
    www.mech7.net

  4. #4
    SitePoint Guru gavwvin's Avatar
    Join Date
    Nov 2004
    Location
    Cornwall, UK
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Guru
    Join Date
    Jul 2004
    Location
    Netherlands
    Posts
    672
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Go visit my site :-D you know you want to ;-)
    www.mech7.net

  6. #6
    SitePoint Guru
    Join Date
    Jul 2004
    Location
    Netherlands
    Posts
    672
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nobody has a good solution for this
    Go visit my site :-D you know you want to ;-)
    www.mech7.net

  7. #7
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    > 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

  8. #8
    SitePoint Enthusiast
    Join Date
    Jan 2006
    Location
    Amsterdam
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...
    Last edited by NikJazz; Feb 26, 2006 at 03:50. Reason: typo

  9. #9
    SitePoint Evangelist ikeo's Avatar
    Join Date
    Oct 2004
    Location
    Austin Texas
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you are using PHP 5 you might want to consider using XML to store your menu.
    IK

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

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

  10. #10
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    > 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

  11. #11
    SitePoint Enthusiast
    Join Date
    Jan 2006
    Location
    Amsterdam
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  12. #12
    SitePoint Guru
    Join Date
    Jul 2004
    Location
    Netherlands
    Posts
    672
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
        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 && !empty($menu))
            {
              
    $menu "<ul class=\"menuLevel\">{$menu}</ul>";
            }
         
            return 
    $menu;
          } 
    Go visit my site :-D you know you want to ;-)
    www.mech7.net


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •