Dynamic Multi-level CSS Menu with PHP and MySQL. SEO Ready.

I was recently working on a project for a client and couldn't find a menu solution to fit my needs. I was looking to use a menu style similar to those others I've seen on the web, but I wanted to build the menu on the fly from a MySQL database. After many search attempts and several posts on PHP/MySQL forums I realized I was going to have to work this out myself. I needed infinite levels and most tutorials only had options for 1 or 2 sub levels, or weren't completely dynamic and would require a table rebuild every time you add a new page.


Creating The Menu

My solution was a mix of several styles of menus I found online. I decided to use a flat table and a simple recursive PHP function. The first step is working out the table structure. Here is a look at the table I used.

1

The MySQL Table

ID     label     link     parent     sort

Select all Open in new window

The "ID" is the primary key field, followed by "label" and "link" which are the name and action of the menu option. The "parent" field will be the ID of the parent menu item and finally the "sort" field is used if you want to control the order of your menu items.

This is the SQL for creating the table

CREATE TABLE `menu` (
                        `id` int(11) NOT NULL auto_increment,
                        `label` varchar(50) NOT NULL default '',
                        `link` varchar(100) NOT NULL default '#',
                        `parent` int(11) NOT NULL default '0',
                        `sort` int(11) default NULL,
                         PRIMARY KEY  (`id`),
                      )  ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
                      

Select all Open in new window

Now that we have a table to retrieve the menu information, we need some menu data.

ID	label	link		parent	sort
                      1	Home	#home		0	0
                      2	Code	#code		0	0
                      3	Contact	#contact	0	0
                      4	PHP	#php		2	0
                      5	CSS	#css		2	0
                      6	Scripts	#scripts	4	0
                      7	Help	#help		4	0
                      8	Archive	#archive	6	0
                      9	Snippet	#snippet	8	0

Select all Open in new window

Note that the links can be in any form you like, such as a URL parameter like: ?p=84, a URL like: http://www.example.com, or a URL hash like: #anchor.

2

The PHP Function
Lets take a look at the php. This is a simplified version
 

function display_children($parent, $level) {
                          $result = mysql_query("SELECT a.id, a.label, a.link, Deriv1.Count FROM `menu` a  LEFT OUTER JOIN (SELECT parent, COUNT(*) AS Count FROM `menu` GROUP BY parent) Deriv1 ON a.id = Deriv1.parent WHERE a.parent=" . $parent);
                          echo "<ul>";
                          while ($row = mysql_fetch_assoc($result)) {
                              if ($row['Count'] > 0) {
                                  echo "<li><a href='" . $row['link'] . "'>" . $row['label'] . "</a>";
                      			display_children($row['id'], $level + 1);
                      			echo "</li>";
                              } elseif ($row['Count']==0) {
                                  echo "<li><a href='" . $row['link'] . "'>" . $row['label'] . "</a></li>";
                              } else;
                          }
                          echo "</ul>";
                      }
                      

Select all Open in new window

So lets start with the query.
 

$result = mysql_query("SELECT a.id, a.label, a.link, Deriv1.Count FROM `menu` a  LEFT OUTER JOIN (SELECT parent, COUNT(*) AS Count FROM `menu` GROUP BY parent) Deriv1 ON a.id = Deriv1.parent WHERE a.parent=" . $parent);
                      

Select all Open in new window

It looks complicated but all it is doing is getting the information for each menu item for a parent and a count of how many children it has. The results would look like this:

ID	label		link		count
                      1	Home 		#home 		0
                      2	Code 		#code 		2
                      3	Contact 	#contact 	0

Select all Open in new window

These results are the contents of the top layer or main menu, just after the sql query comes a simple echo to create our opening <ul> tags. Followed by the brains of the function inside this while statement:

while ($row = mysql_fetch_assoc($result)) {
                        if ($row['Count'] > 0) {
                          echo "<li><a href='". $row['link'] ."'>". $row['label'] ."</a>";
                          display_children($row['id'], $level + 1);
                      	echo "</li>";
                        } elseif ($row['Count']==0) {
                          echo "<li><a href='". $row['link'] ."'>". $row['label'] ."</a></li>";
                        } else;
                      }
                      

Select all Open in new window

This statement simply outputs the appropriate <li> tags and links for each item on the menu and then checks to see if that item has any children. If the count is more than zero then it calls the entire function over to build the child menu. Then a simple closing </ul> tag to finish it off.

To call the menu simply run the function with the level you want to display for example:

display_children(0, 1);
                      

Select all Open in new window

Would return:

    * Home
    * Code
          o PHP
                + Scripts
                      # Archive
                            * Snippet
                      # Help
          o CSS
    * Contact

display_children(4, 1);
                      

Select all Open in new window

Would return only the children of PHP or (id #4)

    * Scripts
          o Archive
                + Snippet
          o Help

This second way of calling the menu code can come in handy if you want to build a sub menu any where in your site.

You can easily sort the menu by adding a sort statement to the SQL statement and giving your menu items a sort order.

3

Styling The Menu
Now after all that it doesn't look like much yet. But I'm gonna show you how to make this into a horizontal drop down menu although you could use this to make any sort of menu type.

Modifying the PHP

The first thing we have to do now is add add a class to the output of the PHP script.

Start by replacing:

echo "<li><a href='" . $row['link'] . "'>" . $row['label'] . "</a></li>";
                      

Select all Open in new window

With:

echo "<li class='list'><a class='list_link' href='" . $row['link'] . "'>" . $row['label'] . "</a></li>";
                      

Select all Open in new window

If you want a different style for your lower levels then you will want to use the code below instead of the one above.

echo "<li class='level".$level."'><a class='level".$level."' href='" . $row['link'] . "'>" . $row['label'] . "</a></li>";
                      

Select all Open in new window

Now that our links have been assigned a class we can style them any way we choose. If you want to style layer 2 differently then simply use the .layer2 class to change that. Now that you have your menu in the right format i.e. unordered and ordered lists, you can use just about any CSS menu around. A way that you can code your CSS menus is to give your menu a class, lets say .menu, apply that class to your top container and assign the levels of the menu as:

.menu ul{color:#FFF;} /* Main container, includes the background of the static portion of the menu */
                      .menu ul li{color:#FFF;} /* This is the style for the main menu items */
                      .menu ul ul{color:#FFF;} /* This is the container for the first submenu */
                      .menu ul ul li{color:#FFF;} /* This is the style for the submenus */
                      

Select all Open in new window

And so on, and so on. You can style the links themselves as well by using the .menu a{}. This will alter all links in the menu. To define a different style for a lower level in the menu you would simply change it to .menu ul ul a{} for the second level.

The styling of the menu can be as simple or complex as you wish. I won't attempt to discuss all the styling options as we would be here forever so if you have any questions feel free to ask and ill be glad to help.

4

More efficient rebuild of the menu
Since writing the sections above I have learned quite a lot and in doing so found a much more efficient way of building this menu. This method varies in that it only makes one query to the menu table and compiles the results into a multidimensional array. The basic recurring function was just about the same, just taking into account the changes in data structure. Lets start with the query and array.

 

// Select all entries from the menu table
                      $result=mysql_query("SELECT id, label, link, parent FROM menu ORDER BY parent, sort, label");
                      // Create a multidimensional array to conatin a list of items and parents
                      $menuData = array(
                          'items' => array(),
                          'parents' => array()
                      );
                      // Builds the array lists with data from the menu table
                      while ($menuItem = mysql_fetch_assoc($result))
                      {
                          // Creates entry into items array with current menu item id ie. $menuData['items'][1]
                          $menuData['items'][$menuItem['id']] = $menuItem;
                          // Creates entry into parents array. Parents array contains a list of all items with children
                          $menuData['parents'][$menuItem['parent']][] = $menuItem['id'];
                      }
                      

Select all Open in new window

The $menuData contains 2 other arrays, items holds every result from the menu table query, the parents array holds a list of all item ids that have children. Next we use a while statement to run through the sql results and assign items to the arrays.  If the items parent id already exists in the parents array it will be overwritten so there will only be 1 of each parent id listed.

 

// Menu builder function, parentId 0 is the root
                      function buildMenu($parent, $menuData)
                      {
                          $html = "";
                          if (isset($menuData['parents'][$parent]))
                          {
                              $html .= "<ul>\n";		
                              foreach ($menuData['parents'][$parent] as $itemId)
                              {
                      			if(!isset($menuData['parents'][$itemId]))
                      			{
                      				$html .= "<li>\n  <a href='".$menuData['items'][$itemId]['link']."'>".$menuData['items'][$itemId]['label']."</a>\n </li>\n";
                                  }
                      			if(isset($menuData['parents'][$itemId]))
                      			{
                      				$html .= "<li>\n  <a href='".$menuData['items'][$itemId]['link']."' >".$menuData['items'][$itemId]['label']."</a> \n";
                                  	$html .= buildMenu($itemId, $menuData);
                      				$html .= "</li>\n";		
                      			}            
                              }
                              $html .= "</ul>\n";
                          }
                          return $html;
                      }
                      echo buildMenu(0, $menuData);
                      

Select all Open in new window

This version signifigantly reduces the strain on your server if you have hundreds or thousands of pages and still allows you to keep a completely dynamic menu.