Creating a Dynamic Drop Down Menu using PHP and MySQLi

We have already seen tutorial about simple Drop Down Menu using CSS3 and jQuery, and this tutorial will cover creating a Dynamic Horizontal Drop Down Menu with its sub menu using PHP and MySQLi, it’s a simple concept and easy to create with PHP and MySQLi, you can also add and set links in main menu and sub menu from database data using PHP, I’ve used MySQLi object method, so let’s take a look at this tutorial.

Download Script

consider following two tables

table 1 : main_menu
this is the main table and stores main menu links like ‘home, about, contact’.


CREATE TABLE IF NOT EXISTS `main_menu` (
  `m_menu_id` int(2) NOT NULL AUTO_INCREMENT,
  `m_menu_name` varchar(20) NOT NULL,
  `m_menu_link` varchar(50) NOT NULL,
  PRIMARY KEY (`m_menu_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

table 2 : sub_menu
this table stores sub menu of main menu table and shows links within it.


CREATE TABLE IF NOT EXISTS `sub_menu` (
  `s_menu_id` int(2) NOT NULL AUTO_INCREMENT,
  `m_menu_id` int(2) NOT NULL,
  `s_menu_name` varchar(20) NOT NULL,
  `s_menu_link` varchar(50) NOT NULL,
  PRIMARY KEY (`s_menu_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

add_menu.php

this file contains few lines of php code to insert main menu and sub menu,
sub menu can be added by selecting main parent menu for it i have used here html select box to select main menu, after selecting parent menu you can create sub menus with anchor links.


<?php
$dbcon = new MySQLi("localhost","root","","dbmenu");
if(isset($_POST['add_main_menu']))
{
 $menu_name = $_POST['menu_name'];
 $menu_link = $_POST['mn_link'];
 $sql=$dbcon->query("INSERT INTO main_menu(m_menu_name,m_menu_link) VALUES('$menu_name','$menu_link')");
}
if(isset($_POST['add_sub_menu']))
{
 $parent = $_POST['parent'];
 $proname = $_POST['sub_menu_name'];
 $menu_link = $_POST['sub_menu_link'];
 $sql=$dbcon->query("INSERT INTO sub_menu(m_menu_id,s_menu_name,s_menu_link) VALUES('$parent','$proname','$menu_link')");
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Dynamic Dropdown Menu using PHP and MySQLi</title>
<link rel="stylesheet" type="text/css" href="style.css" media="all" />
</head>
<body>
<div id="head">
<div class="wrap"><br />
<h1><a href="https://codingcage.com/">Coding Cage - programming blog</a></h1>
</div>
</div>
<center>
<pre>
<form method="post">
<input type="text" placeholder="menu name :" name="menu name" /><br />
<input type="text" placeholder="menu link :" name="mn_link" /><br />
<button type="submit" name="add_main_menu">Add main menu</button>
</form>
</pre>
<br />
<pre>
<form method="post">
<select name="parent">
<option selected="selected">select parent menu</option>
<?php
$res=$dbcon->query("SELECT * FROM main_menu");
while($row=$res->fetch_array())
{
 ?>
    <option value="<?php echo $row['m_menu_id']; ?>"><?php echo $row['m_menu_name']; ?></option>
    <?php
}
?>
</select><br />
<input type="text" placeholder="menu name :" name="sub_menu_name" /><br />
<input type="text" placeholder="menu link :" name="sub_menu_link" /><br />
<button type="submit" name="add_sub_menu">Add sub menu</button>
</form>
</pre>
<a href="index.php">back to main page</a>
</center>
</body>
</html>

index.php

this file shows dynamic horizontal drop down menu and all the menu and sub menu links are shown from the database tables.
there are two while loops I’ve created here first one is to display main menu from ‘main_menu’ table and inner while loop is to display sub menu from ‘sub_menu’ table see the below code.


<?php
$dbcon = new MySQLi("localhost","root","","dbmenu");
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Dynamic Dropdown Menu using PHP and MySQLi</title>
<script type="text/javascript" src="jquery.js"></script>
<link rel="stylesheet" type="text/css" href="style.css" media="all" />
</head>
<body>
<div id="head">
<div class="wrap"><br />
<h1><a href="https://codingcage.com/">Coding Cage - programming blog</a></h1><label><a href="add_menu.php">add menu here</a></label>
</div>
</div>

<div class="wrap">
<ul id="nav">
<li><a href="#">Homepage</a></li>
<?php
$res=$dbcon->query("SELECT * FROM main_menu");
while($row=$res->fetch_array())
{
 ?>
 <li><a href="<?php echo $row['m_menu_link']; ?>"><?php echo $row['m_menu_name']; ?></a>
 <?php
 $res_pro=$dbcon->query("SELECT * FROM sub_menu WHERE m_menu_id=".$row['m_menu_id']);
 ?>
        <ul>    
  <?php  
  while($pro_row=$res_pro->fetch_array())
  {
   ?><li><a href="<?php echo $pro_row['s_menu_link']; ?>"><?php echo $pro_row['s_menu_name']; ?></a></li><?php
  }
  ?>
 </ul>
 </li> 
 
    <?php
}
?>
</ul> 
</div>

<script type="text/javascript">
$(document).ready(function() 
{
 $('#nav li').hover(function() 
 {
  $('ul', this).slideDown('fast');
 }, function() 
 {
  $('ul', this).slideUp('fast');
 });
});
</script>
</body>
</html>

style.css


* {
 margin: 0;
 padding: 0;
}
body {
 font-family: "Comic Sans MS", cursive;
 font-size: 15px;
 color: #232323;
}

#head {
 background: #f9f9f9;
 height: 100px;
 padding-top: 15px;
 border-bottom: 1px solid #d5dce8;
}
.wrap {
 width: 1000px;
 margin: 0 auto;
}
#head h1
{
 float:left;
}
#head a
{
 float:right;
}
input,select
{
 width:300px;
 height:35px;
}

/* nav menu */
#nav {
 margin: 0;
 padding: 0;
 list-style: none;
 border-left: 1px solid #d5dce8;
 border-right: 1px solid #d5dce8;
 border-bottom: 1px solid #d5dce8;
 border-bottom-left-radius: 4px;
 -moz-border-radius-bottomleft: 4px;
 -webkit-border-bottom-left-radius: 4px;
 border-bottom-right-radius: 4px;
 -moz-border-radius-bottomright: 4px;
 -webkit-border-bottom-right-radius: 4px;
 height: 50px;
 padding-left: 15px;
 padding-right: 15px;
 background: #f9f9f9;
}
#nav li {
 float: left;
 display: block;
 background: none;
 position: relative;
 z-index: 999;
 margin: 0 1px;
}
#nav li a {
 display: block;
 padding: 0;
 font-weight: 700;
 line-height: 50px;
 text-decoration: none;
 color: #818ba3;
 zoom: 1;
 border-left: 1px solid transparent;
 border-right: 1px solid transparent;
 padding: 0px 12px;
}
#nav li a:hover, #nav li a.hov {
 background-color: #fff;
 border-left: 1px solid #d5dce8;
 border-right: 1px solid #d5dce8;
 color: #576482;
}
/* subnav */
#nav ul {
 position: absolute;
 left: 1px;
 display: none;
 margin: 0;
 padding: 0;
 list-style: none;
 -moz-box-shadow: 0 1px 3px rgba(0, 0, 0, 0.2);
 -o-box-shadow: 0 1px 3px rgba(0, 0, 0, 0.2);
 box-shadow: 0 1px 3px rgba(0, 0, 0, 0.2);
 -webkit-box-shadow: 0 1px 3px rgba(0, 0, 0, 0.2);
 padding-bottom: 3px;
}
#nav ul li {
 width: 180px;
 float: left;
 border-top: 1px solid #fff;
 text-align: left;
}
#nav ul li:hover {
 border-left: 0px solid transparent;
 border-right: 0px solid transparent;
}
#nav ul a {
 display: block;
 height: 20px;
 line-height: 20px;
 padding: 8px 5px;
 color: #666;
 border-bottom: 1px solid transparent;
 text-transform:  uppercase;
 color: #797979;
 font-weight: normal;
}
#nav ul a:hover {
 text-decoration: none;
 border-right-color: transparent;
 border-left-color: transparent;
 background: transparent;
 color: #4e4e4e;
}

that's it we have created here a simple but Dynamic Drop Down Menu with CSS3 using PHP and MySQLi object method, you can download the code and try it in your localhost.