SitePoint Sponsor |
|
User Tag List
Results 1 to 12 of 12
Thread: menu unlimited levels ?
-
Feb 21, 2006, 15:49 #1
- 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
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
-
Feb 21, 2006, 16:52 #2
- Join Date
- Nov 2004
- Location
- Cornwall, UK
- Posts
- 686
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
see this thread:
http://www.sitepoint.com/forums/showthread.php?t=348369
-
Feb 21, 2006, 18:38 #3
- 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
-
Feb 22, 2006, 09:46 #4
- 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 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.
-
Feb 22, 2006, 10:44 #5
- 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 themGo visit my site :-D you know you want to ;-)
www.mech7.net
-
Feb 23, 2006, 07:13 #6
- 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
-
Feb 25, 2006, 21:33 #7
- 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
-
Feb 26, 2006, 04:48 #8
- 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 04:50. Reason: typo
-
Feb 26, 2006, 05:39 #9
- 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
Originally Posted by pixelsoul
-
Feb 26, 2006, 07:13 #10
- 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
-
Feb 26, 2006, 07:37 #11
- 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
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 TherapyMAybe thats what we're looking for?
-
Feb 26, 2006, 13:26 #12
- 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 > 0 && !empty($menu))
{
$menu = "<ul class=\"menuLevel\">{$menu}</ul>";
}
return $menu;
}
Go visit my site :-D you know you want to ;-)
www.mech7.net
Bookmarks