Recursion example php,recursion in php mysql

Steps to create dynamic multilevel menu using php and mysql

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

--
-- 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');
--

-- 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

<?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();
}
?>

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

<?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;
} 
?>

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

<ul class="main-navigation">
<?php echo get_menu_tree(0);//start from root menus having parent id 0 ?>
</ul>

<?php echo get_menu_tree(0);//start from root menus having parent id 0 ?>

</ul>

Step 5: Close the Mysql connection

<?php mysqli_close($con); ?>

1	<?php mysqli_close($con); ?>

Step 6. <ul> <li> depth css implementation in style tag in head section

<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>

<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.

<!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); ?>

100

101

102

103

104

105

106

107

108

109

110

111

<!doctype html>

<html>

<head>

<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>

<?php echo get_menu_tree(0);//start from root menus having parent id 0 ?>

</ul>

</body>

</html>

<?php mysqli_close($con); ?>

Published by

Jeetendra Singh

Jeetendra Singh is a UI Architect who has 10+ Years of Experience as Full Stack Developer. He is a Microsoft certified technology Specialist and AWS Certified Associate Architect. His Major Interests are in Mongo Db, Express Js, React Js, Node Js (MERN STACK). He build variety of applications using Mern, Mean, Node, JavaScript & jQuery View all posts by Jeetendra Singh

34 thoughts on “Steps to create dynamic multilevel menu using php and mysql”

Richard Doktor says:

November 13, 2020 at 3:21 pm

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?
Rado says:

May 22, 2020 at 7:20 am

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
Stania says:

October 31, 2019 at 4:16 pm

To deal with the issue just add $menu = str_replace(”,”,$menu); after the $menu .= “”;
Patel Gopi says:

May 19, 2019 at 12:46 pm

can you give me this code using select box on email id patelgopi277@yahoo.com
gurram santhosh says:

April 22, 2019 at 9:46 am

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
Abdul Mumin Yahaya says:

March 20, 2019 at 3:40 pm

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
lakshmi prasu says:

February 25, 2019 at 6:08 am

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.
T!! says:

January 3, 2019 at 11:05 am

please do you save the code in a .html file or a .php file?
1. Fakrudin abu bakar says:
  
  January 9, 2019 at 7:26 am
  
  Complete Code: .php
Aravinth Sakthivel says:

October 25, 2018 at 5:21 pm

i need tp parse json nested array in php code anybody help me
1. acpodam29 says:
  
  March 17, 2019 at 1:59 am
  
  Loоking for sеxting
  https://www.google.com/#btnI=rutidgunu&q=fuckster.club&t43j=pvmuesdyhs
  Add me, my id 470966
amitabh kumar says:

October 22, 2018 at 8:42 am

SEO frendly page link click on menu link so please help me
amitabh kumar says:

October 22, 2018 at 8:41 am

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
bikers travellingsouls says:

June 27, 2018 at 7:34 am

how to make use this code inside bootstrap menu
1. Iftekhar says:
  
  August 2, 2018 at 8:07 am
  
  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.
zeeshan chaudhary says:

June 23, 2018 at 9:33 am

why u use parent_id
and from which tabel u get parent_id
1. Fakrudin abu bakar says:
  
  January 9, 2019 at 7:28 am
  
  same table; menu
Chukwudi onwumere says:

June 3, 2018 at 1:40 pm

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.
Manoj Kumar Vattakkat says:

June 12, 2017 at 1:32 pm

can you please describe how to add 3rd and more level menus in the database
Billa Mounika says:

May 7, 2017 at 11:28 am

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 🙂
Anil Reddy says:

March 23, 2017 at 5:53 am

Thanks a lot. It is the best solution I have got after searching for 2 hours.
1. w3school says:
  
  March 23, 2017 at 4:39 pm
  
  Welcome Anil . Its my pleasure , if you need any problem you can discuss with me directly by email on jeetendra.apps@gmail.com
envyyy says:

November 20, 2016 at 1:18 pm

Ok i’ve set it up like said. What do i have to name my PHP files for the navigation to find them?
1. w3school says:
  
  November 20, 2016 at 5:38 pm
  
  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
  1. envyyy says:
    
    November 20, 2016 at 5:46 pm
    
    r99t01@gmail.com
    I also have another problem, but ill let you know via email by then, thank you!
    1. w3school says:
      
      November 20, 2016 at 5:51 pm
      
      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
      1. envyyy says:
        
        November 20, 2016 at 6:05 pm
        
        Warning: mysqli_query() expects parameter 1
        
        Warning: mysqli_fetch_array() expects parameter 1
        
        Warning: mysqli_close() expects parameter 1
        What am i doing wrong?
        
        w3school says:
        
        November 21, 2016 at 6:21 am
        
        Hi Envyy i have sent you a mail having complete demo , i that zip file there is a readme.txt file please read that
Exal says:

December 22, 2015 at 11:36 am

This is two level menu. But if I have 3,4,5 levels?
1. w3school says:
  
  December 22, 2015 at 12:09 pm
  
  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.
Dhruv says:

December 22, 2015 at 9:53 am

Thats great.
I have one more query, may i ask ?
1. w3school says:
  
  December 22, 2015 at 10:21 am
  
  yes please what is your query?
2. amitabh kumar says:
  
  October 22, 2018 at 12:01 pm
  
  how to display contant on click menu link sir seo frendly amitabhkr4u@gmail.com
  1. ajandco says:
    
    December 22, 2018 at 3:58 am
    
    disqus_ibjR7UwScI more

Comments are closed.