<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
16,909 Points
10,709 Views
2 Endorsements
Last Modified:
Approved
Community Pick
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

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;

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

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

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

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

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

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

Open in new window


Would return:

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

display_children(4, 1);

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

Open in new window


With:

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

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

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 */

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

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

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.
2
Comment
Author:DanielIser
0 Comments

Featured Post

Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Join & Write a Comment

The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month