Creating menu from database data

This post will walk you through a detailed tutorial on how to make a vertical menu system from our categories that stored in database. In this script, when the user add the new categories dynamically, the system will render the added categories as the menu items automatically. Below is our final result screenshot.

Firstly, we need to create a database. I named it as catmenu and below is our categories table for our menu system.

CREATE TABLE IF NOT EXISTS `categories`
`cat_id` int(5) NOT NULL AUTO_INCREMENT,
`cat_name` varchar(255) NOT NULL,
`parent_id` int(5) NOT NULL,
PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

The next thing we need to do is to create a form to add our menu data from our site dynamically.
Open new document and type below code and save as category.php

<!DOCTYPE html>
<html>
<head>
 <title>Adding category for menu</title>
</head>
<body>
<?php
 if(isset($_POST['submit']))
 {
  $parent_name=addslashes($_POST['parent_name']);
  $cat_name=addslashes($_POST['cat_name']);
  mysqli=mysqli_connect('localhost','root','','catmenu');
  $sql="INSERT INTO categories(cat_name, parent_id) VALUES ('$cat_name', '$parent_name')";
  $result=mysqli_query($mysqli,$sql);
  if($result){
   header("location: category.php");
   exit; 
  }
 }
?> 
<form class="cat_form" id="cat_form" method="post" action="category.php" >
 <p>
  <label>Parent:</label>
  <?php
   $mysqli=mysqli_connect('localhost','root','','catmenu');
   $sql="SELECT cat_id,cat_name FROM categories WHERE parent_id=0";
   $result=mysqli_query($mysqli,$sql);
   echo "<select name='parent_name' id='parent_name' length='15'>";
   echo "<option  value='0'>Root</option>";
   while($row=mysqli_fetch_array($result)){
     $cat_id=$row['cat_id'];
     $cat_name=$row['cat_name'];
     echo "<option value=".$cat_id." >".$cat_name."</option>";
   }
   echo "</select>";
  ?>
 </p>
 <p>
  <label>Category Name:</label>
  <input type="text" id="cat_name" name="cat_name" />
 </p>
 <p>
  <input type="submit" value="Add" name="submit" />
 </p>
</form>
</body>
</html>

If you load this script in your browser, you will see like below. Type the category name for our menu in the text box.

I type it as Tutorials and click Add button. You will see this form again. Now you can choose your parent category from Parent drop down list if you want to add as a sub menu like below.

I also add some menu data for my site and now it is time to write a index.php for our site. Below is our index.php.

<!DOCTYPE html>
<html>
  <head>
   <title>Vertical menu</title>
  <style type="text/css">
  #navigation {
   width:200px; 
   float:left; 
   margin-top:5px;
  }
  #navigation ul{
   margin:0px; 
   padding:0px; 
   background-color: #CCC;
  }
  #navigation ul li {
     height:25px;
   line-height:25px;
   list-style:none;
   padding-left:10px;
   border-top:#fff solid;
   border-bottom:#fff solid;
   border-width:1px;
   cursor:pointer;
  }
  #navigation ul li a{
   text-decoration:none;
   color: #fff;
  }
  #navigation ul li:hover{
   background-color: #39F; 
   position:relative;
  }
  #navigation ul ul {
   display:none;
   position:absolute;
   left:150px;
   top:0px;
   border:#fff solid;
   border-width:1px;
   background-color: #CCC;
  } 
  #navigation ul li:hover ul {
   display:block;
  }
  #navigation ul ul li {
   border:none; 
   width:150px; 
   float:left; 
   display:inline;
  }
  #navigation ul ul li:hover {
   border:none;
  }
  </style>
  </head>
  <body>
  <div id="navigation">
  <ul>
  <?php
   $mysqli=mysqli_connect('localhost','root','','catmenu');
   $sql="SELECT * FROM categories WHERE parent_id = 0";
   $result=mysqli_query($mysqli,$sql) or die(mysqli_error());
   while($row=mysqli_fetch_array($result)){
    $cat_id=$row['cat_id'];
    $cat_name=$row['cat_name'];
    echo "<li>
    <a href='index.php?cat=$cat_id'>".$cat_name."</a>";
    echo "<ul>";
    $sql2="SELECT * FROM categories WHERE parent_id=$cat_id and cat_id<>0";
    $result2=mysqli_query($mysqli,$sql2) or die(mysqli_error());

    while($row=mysqli_fetch_array($result2)){
     $sub_cat_id=$row['cat_id'];
     $sub_cat_name=$row['cat_name'];
     echo "<li><a href='index.php?cat=$sub_cat_id'>".$sub_cat_name."</a></li>";
    }
    echo "</ul>";
    echo "</li>";
  
   }
  ?>
  </ul>
  </div>
  </body>
</html>

If you run this site you will see like below but can change according our menu items that added in the above form.

TutsforWeb

Pages

Creating menu from database data

6 comments: