Create Dynamic Drop Down Menu from Database

I assumed that you have already know about PHP and mysql. Now we are talking about the algorithm to create a recursive menu. After getting the data from the database, we need to 'echo' the value in the specific HTML format with id and class name like we have talked about in the previous post.

For example we have a table for storing the menu data as describe below

id	name	alias	parent	link
1	HOME	home	0	#
2	DROP DOWN	drop-down	0	#
3	CATALOG	catalog	0	#
4	FAQ	faq	0	#
5	CONTACT	contact	0	#
6	First Child	child-1	2	#
7	Grand Child 1	grand-child-1	6	#
8	Grand Child 2	grand-child-2	6	#

NOTE For the example table :

alias is used to shorten the link
parent refers to menu id, if there is no parent, the value is 0 (default)
See the parent field value, First Child has a parent with menu id = 2 (DROP DOWN menu)
Grand Child 1 and 2 have a parent with menu id = 6 (First Child menu)

Let's continue to our main focus.

Call the css file

The css file (ja-sosdmenu.css) is the same as we have used in the previous post, you can download it there

<head>   
  <link rel="stylesheet" href="css/ja-sosdmenu.css" type="text/css" />
</head>

Create the main function

function printMenu($id){
  $dbhost = 'localhost';
  $dbuser = 'root';
  $dbpass = '';
  $conn = mysql_connect($dbhost, $dbuser, $dbpass) 
    or die ('Error connecting to mysql);
  mysql_select_db('blanc_developer');
  
  $parentMenu = mysql_query("SELECT id, name, alias, link FROM jos_menu
      WHERE parent = '$id'");

  while ($row = mysql_fetch_object($parentMenu)) {
    // first, display the menu
    echo "<li><a href=\"".$row->link."\">" . $row->name . "</a>";
    
    if (hasChildren($row->id)) {      
      echo "<ul>\r\n";

      //Call the function again with menu's id as the parameter
      printMenu($row->id);
      
      echo "</ul>";
    }// end of if loop
    
    echo "</li>\r\n";
  }//end of foreach loop

}

Create a function to check whether the menu has children

function hasChildren($menuId){
  
  $parentMenu = mysql_query("SELECT id, name, alias FROM jos_menu
      WHERE parent = '$menuId'");
      
  if(mysql_num_rows($parentMenu) > 0){
      return true;
  }else{
      return false;
  }    
}

Here we go

//Pre recursive loop
  echo "<div id='ja-mainnav'>";
  echo "<ul class='menu'>\r\n";
  
// our main focus (create menu through recursive function)
  printMenu(0);  // 0 is the value for first parent menu

//Post recursive loop
  echo "</ul>\r\n";
  echo "</div>";




Check it in your browser, a menu may have a child, and the
children may have their children and so on.


Hope this post helps.

Web Experiences

Tuesday, October 16, 2012