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

Since writing this post i have enhanced the script and now use V2 which you can check out here:Single Query Dynamic Multi-level Menu

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 found on Cssplay.co.uk, but i wanted to build the menu on the fly from a MySQL database. After many search attempts and several posts on php/mysql boards 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 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.

The MySQL Table

id label link parent sort

The id is you Primary Key field here, followed by label and link being the name and action of the option. The parent will be the id of the parent menu item and finally the sort field is used if you want any control over the order of your menu items.

Here is the sql

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=248 DEFAULT CHARSET=latin1;

Now that we have a table to take information from we need some 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

The links can be in any form as you will build them later. eg. ?p=84, http://url, #anchor.

The PHP Function

Now 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>";
}

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

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

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

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

Would return:

  • Home
  • Code
    • PHP
      • Scripts
        • Archive
          • Snippet
        • Help
    • CSS
  • Contact
display_children(4, 1);

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

  • Scripts
    • Archive
      • Snippet
    • Help

This second use can come in handy if you want to build a sub menu any where in your site.

Styling The Menu

Now after all that it doesnt look like much yet. But im gonna show you how to make this into a horizontal dropdown 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>";

With:

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

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

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 ie. Unordered and Ordered lists you can use just about any css menu around. I recommend checking out the ones over at Cssplay.co.uk, of course if you like there stuff make sure you donate. Another 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 */

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.

I wont go over all the styling as we would be here forever so if you have any questions feel free to ask and ill be glad to help.

Since writing this post i have enhanced the script and now use V2 which you can check out here: Single Query Dynamic Multi-level Menu