Spitman Posted August 5, 2009 Share Posted August 5, 2009 Hi All, Apologies for asking a question in my very first post, but I am completely stuck here and really need some help. I have a MySQL database which has 3 tables storing information relating to the pages of a website. The first table contains pretty much all the main info and is called tbl_pages : page_id (primary key) page_meta_title page_meta_desc page_content page_menu_title (which is what is to be displayed as the anchor text for it in the menu) etc etc The second table is called tbl_pages_parents and contains two fields used to specify which page is the parent of each page: page_id parent_page_id The final table is called tbl_pages_order and is used to store the order of each page should be show within its parent page group page_id page_order So for example I'll give a indented list of pages in the format [page_menu_title][page_id][parent_page_id][page_order] as they would currently be stored in the database [Home] [1][1][1] [services] [2][1][2][Window Cleaning] [5][2][1] [sweeping] [6][2][2] [Car Wash] [7][2][3][Wash Only] [9][7][1] [Wash and Wax] [10][7][2] [Wash, Wax and Vac] [11][7][3] [*][Gardening] [8][2][4] [*][About Us] [3][1][3] [*][Contact us] [4][1][4] As you can see the page_id values have no bearing on either the order, or the menu level of the page, they are merely used as a reference in the other tables. What I am struggling to do is create a query which can run through each of the tables, build a multidimensional array containing all relevant details of each page and then output it to create an indented list (like the one above) which is in the correct order, and has sub menu items correctly nested. Any help would be greatly appreciated, and if any further clarification is needed please shout and I'll do my best to explain. Many thanks in advance Quote Link to comment Share on other sites More sharing options...
Bjom Posted August 5, 2009 Share Posted August 5, 2009 I'll try and come up with something. A few questions in advance: Why do put the order and parent info into separate tables? That only makes sense if one table ever can possibly be the child of more than one page or have different values for order respectively. As I see it, one table is sufficient. That's rather intricate, so don't expect me to come back with a solution within minutes Quote Link to comment Share on other sites More sharing options...
Spitman Posted August 5, 2009 Author Share Posted August 5, 2009 Hi Bjom, Thanks for the response. This was just how the database was set up when I inherited it. The front end of the website all works fine using these tables and I don't really want to get into changing the structure and then having to change all the front end code too... Basically I guess you could describe it that from this structure I am looking to create a sitemap page (although this is not the actual reason, it is exactly what I want to produce) Quote Link to comment Share on other sites More sharing options...
Bjom Posted August 5, 2009 Share Posted August 5, 2009 ok. was just wondering. the DB structure works fine like this. I'll give it a go then. Quote Link to comment Share on other sites More sharing options...
Spitman Posted August 5, 2009 Author Share Posted August 5, 2009 Thanks Bjom - I just keep going round in circles! Also just to point out, one problem I am having is that there isn't a limit to how deep the menu can go, so for example some pages may be 6 levels in (i.e the child of a child of a child of a child of a child of the top) Quote Link to comment Share on other sites More sharing options...
Bjom Posted August 5, 2009 Share Posted August 5, 2009 yes that's what I thougt. I'm doing this as an entertainment while I have to do some annoying work as my main task....so give me a few hours and I'll come back to you with a generic solution. Hope you can stop your feet until then Quote Link to comment Share on other sites More sharing options...
Spitman Posted August 5, 2009 Author Share Posted August 5, 2009 Sounds great, cheers Bjom - no rush I've been racking my brains for ever on it! Quote Link to comment Share on other sites More sharing options...
mcjwb Posted August 12, 2009 Share Posted August 12, 2009 Hi, First I need to change some of the data. I need to change those items with a parent_page_id of 1 to have a parent_page_id of 0. I hope this is acceptable. This is because we want these items to be top level ones and not the children of page_id 1. You need to use a recursive function to build the array: <?php function build_menu($tmp, $parent_page_id, $page_menu_title, $page_id){ if($parent_page_id==0){ //if it's a top level item we can just add it straight to the array. //NB the result set must be ordered so the items with parent_page_id=0 must all come first. $tmp[$page_id]=array('name'=>$page_menu_title); } else{ if(array_key_exists($parent_page_id, $tmp)){ //print 'parent('.$parent_page_id.') found at current level... adding item('.$page_menu_title.')<br>'; //If the parent is in the current level of the menu array, we can add the sub item... $tmp[$parent_page_id][$page_id]=array('name'=>$page_menu_title); } else{ //print 'parent('.$parent_page_id.') not found at current level, looping...<br>'; //... otherwise we need to do a recursive loop through the menu array, so we will eventually find the parent no matter where it lives in the menu. (as we've ordered the results we're looping over, the parent will have always been created by this stage therefore eventually the if statement above will hold true) foreach($tmp as $id => $next_level){ if(is_array($next_level)){ $tmp[$id]=build_menu($next_level, $parent_page_id, $page_menu_title, $page_id); } } } } return $tmp; } ?> and you'd call it whilst looping over the result of the database query like this: <?php $menu=array(); while($row = mysql_fetch_array($result)){ $menu = build_menu($menu, $row['parent_page_id'], $row['page_menu_title'], $row['page_id']); } ?> In order to produce a html menu from this array you'll need another recursive function like so: <?php function build_menu($menu){ $m='<ul>'."\r\n"; foreach($menu as $id=>$data){ if(is_array($data)){ $m.='<li>'.$data['name']; if(count($data)>1){ $m.=build_menu($data); } $m.='</li>'."\r\n"; } } $m.='</ul>'."\r\n"; return $m; } ?> This will produce html like this: <ul> <li>Home</li> <li>Services <ul> <li>Window Cleaning</li> <li>Sweeping</li> <li>Car Wash <ul> <li>Wash only</li> <li>Wash and wax</li> <li>Wash, wax and vac</li> </ul> </li> <li>Gardening</li> </ul> </li> <li>About Us</li> <li>Contact Us</li> </ul> Hope this helps, Joe Quote Link to comment Share on other sites More sharing options...
djones Posted February 8, 2010 Share Posted February 8, 2010 Hi, First I need to change some of the data. I need to change those items with a parent_page_id of 1 to have a parent_page_id of 0. I hope this is acceptable. This is because we want these items to be top level ones and not the children of page_id 1. You need to use a recursive function to build the array: <?php function build_menu($tmp, $parent_page_id, $page_menu_title, $page_id){ if($parent_page_id==0){ //if it's a top level item we can just add it straight to the array. //NB the result set must be ordered so the items with parent_page_id=0 must all come first. $tmp[$page_id]=array('name'=>$page_menu_title); } else{ if(array_key_exists($parent_page_id, $tmp)){ //print 'parent('.$parent_page_id.') found at current level... adding item('.$page_menu_title.')<br>'; //If the parent is in the current level of the menu array, we can add the sub item... $tmp[$parent_page_id][$page_id]=array('name'=>$page_menu_title); } else{ //print 'parent('.$parent_page_id.') not found at current level, looping...<br>'; //... otherwise we need to do a recursive loop through the menu array, so we will eventually find the parent no matter where it lives in the menu. (as we've ordered the results we're looping over, the parent will have always been created by this stage therefore eventually the if statement above will hold true) foreach($tmp as $id => $next_level){ if(is_array($next_level)){ $tmp[$id]=build_menu($next_level, $parent_page_id, $page_menu_title, $page_id); } } } } return $tmp; } ?> and you'd call it whilst looping over the result of the database query like this: <?php $menu=array(); while($row = mysql_fetch_array($result)){ $menu = build_menu($menu, $row['parent_page_id'], $row['page_menu_title'], $row['page_id']); } ?> In order to produce a html menu from this array you'll need another recursive function like so: <?php function build_menu($menu){ $m='<ul>'."\r\n"; foreach($menu as $id=>$data){ if(is_array($data)){ $m.='<li>'.$data['name']; if(count($data)>1){ $m.=build_menu($data); } $m.='</li>'."\r\n"; } } $m.='</ul>'."\r\n"; return $m; } ?> This will produce html like this: <ul> <li>Home</li> <li>Services <ul> <li>Window Cleaning</li> <li>Sweeping</li> <li>Car Wash <ul> <li>Wash only</li> <li>Wash and wax</li> <li>Wash, wax and vac</li> </ul> </li> <li>Gardening</li> </ul> </li> <li>About Us</li> <li>Contact Us</li> </ul> Hope this helps, Joe This works perfect for me, but I have another row with a page sort id. How do I sort the array with the page sort id? $page_sort_id = $row['page_sort_id']; Quote Link to comment Share on other sites More sharing options...
ignace Posted February 8, 2010 Share Posted February 8, 2010 Keep it simple: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Quote Link to comment Share on other sites More sharing options...
djones Posted February 8, 2010 Share Posted February 8, 2010 I've read that article before and cannot find a way to create a dynamic menu from it. This part of the article where it shows the concat()... SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name 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; ... illustrates the idea but to create a valid <ul> list is different. You have to nest the tags correctly, and I cannot figure that part out. Quote Link to comment Share on other sites More sharing options...
ignace Posted February 8, 2010 Share Posted February 8, 2010 That's the wrong query as he uses it to show the results of his actions. I also discourage the use of a Binary Tree if you are not familiar with it's structure. Just use the self-join: SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4 FROM category AS t1 LEFT JOIN category AS t2 ON t2.parent = t1.category_id LEFT JOIN category AS t3 ON t3.parent = t2.category_id LEFT JOIN category AS t4 ON t4.parent = t3.category_id Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.