Hi Geeks,
Today i am sharing you ” Steps to create dynamic multilevel menu using php and mysql” which can be called “example of recusion in php & mysql”.
What Exactly recursion is ?
Recursion is a technique or Algorithm by which we can go and return the collection of sequenced data with n depth. It is a condition based child search and return the output to the previous stepped varriable’s scope.
we can solve many of the programming problems using recursion.
In following Example we will sort out a problem where we need to show nested n number of depth relation saved in mysql menu table with having proper opening and closng the <ul> and <li> tags in html.
Following are the steps :
Step 1. Create a mysql Table in your database by executing following sql statement
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
-- -- Table structure for table `menu` -- CREATE TABLE IF NOT EXISTS `menu` ( `menu_id` int(11) NOT NULL AUTO_INCREMENT, `menu_name` varchar(255) NOT NULL, `parent_id` int(11) NOT NULL DEFAULT '0' COMMENT '0 if menu is root level or menuid if this is child on any menu', `link` varchar(255) NOT NULL, `status` enum('0','1') NOT NULL DEFAULT '1' COMMENT '0 for disabled menu or 1 for enabled menu', PRIMARY KEY (`menu_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ; -- -- Dumping data for table `menu` -- INSERT INTO `menu` (`menu_id`, `menu_name`, `parent_id`, `link`, `status`) VALUES (1, 'Home', 0, '#home', '1'), (2, 'Web development', 0, '#web-dev', '1'), (3, 'WordPress Development', 0, '#wp-dev', '1'), (4, 'About w3school.info', 0, '#w3school-info', '1'), (5, 'AWS ADMIN', 2, '#', '1'), (6, 'PHP', 2, '#', '1'), (7, 'Javascript', 2, '#', '1'), (8, 'Elastic Ip', 5, '#electic-ip', '1'), (9, 'Load balacing', 5, '#load-balancing', '1'), (10, 'Cluster Indexes', 5, '#cluster-indexes', '1'), (11, 'Rds Db setup', 5, '#rds-db', '1'), (12, 'Framework Development', 6, '#', '1'), (13, 'Ecommerce Development', 6, '#', '1'), (14, 'Cms Development', 6, '#', '1'), (21, 'News & Media', 6, '#', '1'), (22, 'Codeigniter', 12, '#codeigniter', '1'), (23, 'Cake', 12, '#cake-dev', '1'), (24, 'Opencart', 13, '#opencart', '1'), (25, 'Magento', 13, '#magento', '1'), (26, 'Wordpress', 14, '#wordpress-dev', '1'), (27, 'Joomla', 14, '#joomla-dev', '1'), (28, 'Drupal', 14, '#drupal-dev', '1'), (29, 'Ajax', 7, '#ajax-dev', '1'), (30, 'Jquery', 7, '#jquery-dev', '1'), (31, 'Themes', 3, '#theme-dev', '1'), (32, 'Plugins', 3, '#plugin-dev', '1'), (33, 'Custom Post Types', 3, '#', '1'), (34, 'Options', 3, '#wp-options', '1'), (35, 'Testimonials', 33, '#testimonial-dev', '1'), (36, 'Portfolios', 33, '#portfolio-dev', '1'); -- |
Step 2. Connect with database
1 2 3 4 5 6 7 8 9 |
<?php //create a mysql connection $con=mysqli_connect("localhost","root","","test");// we have used db name test you can change your db name // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } ?> |
Step 3. Implement the recursion logic
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<?php function get_menu_tree($parent_id) { global $con; $menu = ""; $sqlquery = " SELECT * FROM menu where status='1' and parent_id='" .$parent_id . "' "; $res=mysqli_query($con,$sqlquery); while($row=mysqli_fetch_array($res,MYSQLI_ASSOC)) { $menu .="<li><a href='".$row['link']."'>".$row['menu_name']."</a>"; $menu .= "<ul>".get_menu_tree($row['menu_id'])."</ul>"; //call recursively $menu .= "</li>"; } return $menu; } ?> |
Step 4: Ininitialize start the Recursion in Parent Content Holder
1 2 3 |
<ul class="main-navigation"> <?php echo get_menu_tree(0);//start from root menus having parent id 0 ?> </ul> |
Step 5: Close the Mysql connection
1 |
<?php mysqli_close($con); ?> |
Step 6. <ul> <li> depth css implementation in style tag in head section
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
<style> ul { list-style: none; padding: 0; margin: 0; background: #1bc2a2; } ul li { display: block; position: relative; float: left; background: #1bc2a2; } /* This hides the dropdowns */ li ul { display: none; } ul li a { display: block; padding: 1em; text-decoration: none; white-space: nowrap; color: #fff; } ul li a:hover { background: #2c3e50; } /* Display the dropdown */ li:hover > ul { display: block; position: absolute; } li:hover li { float: none; } li:hover a { background: #1bc2a2; } li:hover li a:hover { background: #2c3e50; } .main-navigation li ul li { border-top: 0; } /* Displays second level dropdowns to the right of the first level dropdown */ ul ul ul { left: 100%; top: 0; } /* Simple clearfix */ ul:before, ul:after { content: " "; /* 1 */ display: table; /* 2 */ } ul:after { clear: both; } </style> |
Conclusion : Complete Code in one File.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 |
<!doctype html> <html> <head> <meta charset="utf-8"> <title>create dynamic multilevel menu using php and mysql</title> <style> ul { list-style: none; padding: 0; margin: 0; background: #1bc2a2; } ul li { display: block; position: relative; float: left; background: #1bc2a2; } /* This hides the dropdowns */ li ul { display: none; } ul li a { display: block; padding: 1em; text-decoration: none; white-space: nowrap; color: #fff; } ul li a:hover { background: #2c3e50; } /* Display the dropdown */ li:hover > ul { display: block; position: absolute; } li:hover li { float: none; } li:hover a { background: #1bc2a2; } li:hover li a:hover { background: #2c3e50; } .main-navigation li ul li { border-top: 0; } /* Displays second level dropdowns to the right of the first level dropdown */ ul ul ul { left: 100%; top: 0; } /* Simple clearfix */ ul:before, ul:after { content: " "; /* 1 */ display: table; /* 2 */ } ul:after { clear: both; } </style> </head> <body> <?php $con=mysqli_connect("localhost","root","","test"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } // Perform queries function get_menu_tree($parent_id) { global $con; $menu = ""; $sqlquery = " SELECT * FROM menu where status='1' and parent_id='" .$parent_id . "' "; $res=mysqli_query($con,$sqlquery); while($row=mysqli_fetch_array($res,MYSQLI_ASSOC)) { $menu .="<li><a href='".$row['link']."'>".$row['menu_name']."</a>"; $menu .= "<ul>".get_menu_tree($row['menu_id'])."</ul>"; //call recursively $menu .= "</li>"; } return $menu; } ?> <h1>Create Nested menu Tree by Mysql php</h1> <ul class="main-navigation"> <?php echo get_menu_tree(0);//start from root menus having parent id 0 ?> </ul> </body> </html> <?php mysqli_close($con); ?> |
A wonderful example for diplaying a dynamic menu.
Any chance we get a code (hint) on how to write the sortorder of each menu element back into the MySQL database, once the order of the menu is changed?
hi, on localhost function works perfectly but after web upload i am getting fatal error of memory size. is there any other solution to fix it ? suppose this is caused by infinite loop in call recursively. thank you
To deal with the issue just add $menu = str_replace(”,”,$menu); after the $menu .= “”;
can you give me this code using select box on email id patelgopi277@yahoo.com
Hi,Can you please help me in the following scenario
In your program how to insert data dynamically based on user view and also is there any possible to add data dynamically by clicking links.please try to give solution as soon as possible .its very urgent to me
I will first of all appreciate you for this script.
I discovered that where there is no child node, empty UL tags () will be left behind. Please how can we remove that base on this code
Home
Web development
AWS ADMIN
Elastic Ip
Load balacing
Cluster Indexes
Rds Db setup
PHP
Framework Development
Codeigniter
Cake
Ecommerce Development
Opencart
Magento
Cms Development
WordPress
Joomla
Drupal
News & Media
Javascript
Ajax
Jquery
WordPress Development
Themes
Plugins
Custom Post Types
Testimonials
Portfolios
Options
About w3school.info
Thanks a lot its very helpful to me. I am searching about this script nearly one day.I am using sub while loop . Because I want sidebar list with subitems.Anyway I will modify it.Once again Thank u.
please do you save the code in a .html file or a .php file?
Complete Code: .php
i need tp parse json nested array in php code anybody help me
Loоking for sеxting
https://www.google.com/#btnI=rutidgunu&q=fuckster.club&t43j=pvmuesdyhs
Add me, my id 470966
SEO frendly page link click on menu link so please help me
sir your code is very use full but one issue how can open click on menu link display contant diffrence so please help mi amitabhkr4u@gmail.com
how to make use this code inside bootstrap menu
You can follow my way which I have uploaded in pastebin – https://pastebin.com/qfQz8Xrv
Hope it will help you. If you find something better than my one, please share with me.
why u use parent_id
and from which tabel u get parent_id
same table; menu
Thanks for the speedy response to my mail. Your suggestion fixed the bug. Thanks alot. I have been surfing the net for hours yet couldn’t get a solution.
can you please describe how to add 3rd and more level menus in the database
Warning: mysqli_query() expects parameter 1 to be mysqli,
Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result
Hi, even i am getting the same error. what does it mean> pls hlp 🙂
Thanks a lot. It is the best solution I have got after searching for 2 hours.
Welcome Anil . Its my pleasure , if you need any problem you can discuss with me directly by email on jeetendra.apps@gmail.com
Ok i’ve set it up like said. What do i have to name my PHP files for the navigation to find them?
I have provided a function in step number 3 , so either you can put that function in your existing functions.php file or if functions.php file not exists then you can create functions.Php file and put that function in it and include by using include or include_once php methods in your running file like index.php and call that function inside tag like step 4,
If any problem in it then let me know your email I’d I will send a demo to you but by tomorrow as it’s too late in India ,hope you can understand. Cheers
r99t01@gmail.com
I also have another problem, but ill let you know via email by then, thank you!
No issues you can also post your another problem in public comments and I will send you complete working demo by tomorrow on your email id
Warning: mysqli_query() expects parameter 1
Warning: mysqli_fetch_array() expects parameter 1
Warning: mysqli_close() expects parameter 1
What am i doing wrong?
Hi Envyy i have sent you a mail having complete demo , i that zip file there is a readme.txt file please read that
This is two level menu. But if I have 3,4,5 levels?
No Exal this is 4 level menu and this code works with 3,4,5 and nth level of relation ,you can add n number of iterations in menu table please check with adding more entries and see the screenshot , this is 4th level working very fine.
Thats great.
I have one more query, may i ask ?
yes please what is your query?
how to display contant on click menu link sir seo frendly amitabhkr4u@gmail.com
disqus_ibjR7UwScI more