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

44 Comments

  1. Christopher on March 11, 2010 at 2:36 pm

    Just something to consider about generating dynamic content and scalability.

    There are no problems with the way you’ve chosen to store and retrieve the data, but when that data is accessed is where the issues are located. It’s a pretty good bet that the menu won’t be changing that much. I would venture to guess not very often, and probably a worst case scenario of once a week. Due to the fact that it’s essentially static data, retrieving it from the database every single time isn’t a very good idea.

    In looking at the penalties incurred from generating the menu every time, it can become quite a burden to have all those pointless database accesses. Every time any page is viewed, the entire menu has to be retrieved from the database. In going with the example you have above, there are five separate queries per access. This might not look like a problem initially, but once you realize that is just for one access by one user, you can start to see that it’s going to cause troubles if the user base grows. What happens when this goes into an environment where there are possibly millions of people using this? The result is millions of accesses to the database to display a simple menu, which rarely ever changes. This alone isn’t enough to bring your server to its knees, but it sure does provide for senseless use of resources.

    The simple addition of a caching layer would take all this away. It doesn’t have to be anything fancy. I would imagine that every time the menu is edited, you simply call the code above to generate the menu, and then save it out as an HTML fragment that you can include in your code. This would cause no more of a penalty than the stock serving of plain HTML, and is a great speedup.

    In the end, the storage solution looks great, and is a very nice way to be able to provide a means of creating and editing a very nice menu system. The simple addition of the extra caching layer would go a long way to increase the efficiency of the application and make it even better.

    • DanielIser on March 11, 2010 at 3:57 pm

      I agree about the number of querys. if you look at the bbmetals.net site under products there are many calls to be made. The cacheing idea is pretty good. cache it to a php fwrite file. Since posting this i have been working on a recode of the menu to reduce the calls to get the entire structure in a single call. I will be posting a follow up to this already.. Its been months since i have looked at this code, so when i started writing this i started thinking again. I might try to work the cacheing idea into my next post.. thanks @Christopher

  2. DanielIser on March 15, 2010 at 10:27 am

    @Jenna T, It is probably a setting in outlook or windows mail. The servers used by cpanel for mail are usually mail.domain.com but will vary depending on you hosting provider. But if you go into cpanel, then click email accounts, there will be an option next to each account either showing or under the more box. After clicking that you will get a few links that will set up specific mail clients for that account but at the bottom are the mail server settings.

  3. Moshe on April 25, 2010 at 9:58 am

    Hi
    Great script. i want to use it , but with one exception:
    i need that the Menu structure will show me the Current Selected menu+its fathers and its sons only (not son of son) meaning showing parent items+subitems of the current node only (the current node can be posted and passed to the function.)
    any ideas ?
    Thanks for help
    moshe

    • DanielIser on April 26, 2010 at 3:57 am

      I have actually rewritten this to be more server friendly. I will be posting an update in a few days that only uses 1 query to retrieve the entire menu. I am not completely sure i understand what your needing. If you only want the children of the current parent and nothing else you could set a TRUE/FALSE check and another parameter, If True rerun the funtion for children of children. If False then it doesnt recall the funtion. You could use this function with another input parameter for the current option. This parameter will be checked against all items, if its a match use some css classes to make it active. If you tell me more about your needs i can help you get it working.

  4. Joe on May 8, 2010 at 1:12 am

    Hello Daniellser,

    Thanks a lot for sharing this wonderful script and the nice explanation. I would like to know when will you have the opportunity to post the server friendly upgrade of your dynamic menu?
    Best regards
    Joe

  5. danieliser on July 6, 2010 at 2:44 am

    I have been very busy lately but i will have the update to this article posted tomorrow. It will include the necessary modifications that you need to create this menu with only 1 query to the table.

  6. danieliser on July 9, 2010 at 4:17 pm

    Thank you all for your response. I have been putting a lot of work into this site lately, ie. the blogroll links were due to the fact i did a new install of WP and hadnt redone them yet but the site is coming together. I have put up the follow up to this article which can be found on the main menu as V2.

  7. Amar on May 1, 2011 at 8:41 am

    Great code but cannot convert it into horizontal need help

  8. marzieh on August 15, 2011 at 3:51 pm

    thanks a lot!

  9. adrian on October 10, 2011 at 1:09 pm

    what to do if a want to make link like this code/php/archive…
    this code build link like this code or php or archive

  10. Manuel Alejandro on November 1, 2011 at 12:10 pm

    Hi! thank you very much by share the code, it helped me much.
    Regards from Venezuela 😀
    —————————————-
    Hola gracias por compartir el código me ayudó muchisimo.
    Saludos desde Venezuela. 😀

    • danieliser on November 1, 2011 at 12:33 pm

      Thanks you very much. Glad I can help.
      ——————————————————–
      Muchas gracias. Me alegro de que le puede ayudar.

  11. Shishir on November 3, 2011 at 12:09 pm

    Hello,

    Thanks a lot for providing such a nice code, you saved my life. Thanks again.

  12. Simon Duun on November 14, 2011 at 6:55 pm

    Hello.

    Thanks for your article. Really helpfull. I’m trying to make the list as a’ menu,
    so the contents of the (lets say) category you clicked on appears when you click at it.
    How do you do that? 🙂

    Best, Simon

    • danieliser on November 14, 2011 at 7:04 pm

      Sounds like something you will want to do with javascript. Jquery would be easy. Add a click even to each menu item. When clicked show children.

      • Simon Duun on November 30, 2011 at 7:58 am

        Thanks for your quick reply. I don’t want javascript or Jquery. Wan’t it to be pure php. So when i click at a
        cateory, a new page opens – could be. Category description page. 🙂

        • Simon Duun on November 30, 2011 at 8:10 am

          The category and links is not a problem. The problem is to hide the submenus, if a user has not clicked them yet 🙂

  13. Kareem Folke on November 25, 2011 at 5:41 am

    Thank alot for this tutorial. I wonder is it possible to enhance this by joining the product categories table with a product table and make the products fall under their categories in the menu system?

    • danieliser on November 29, 2011 at 3:09 am

      It would be possible. You will need to pull the information from the products table and add it to the array of children with parent id being the id of the category. Hope this makes since. If you ask in some of the MySQL boards im sure they could help you with a query to do this all in one shot.

  14. Cesar on December 1, 2011 at 8:33 pm

    Hi,

    I don’t know why but my query is not working like it is suppose:

    mysql> 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=0;
    +—–+———+——-+——-+
    | id | label | link | Count |
    +—–+———+——-+——-+
    | 248 | Home | #home | NULL |
    | 249 | Code | #home | NULL |
    | 250 | Contact | #home | NULL |
    +—–+———+——-+——-+
    3 rows in set (0.00 sec)

    The column COUNT is not showing the correct information.

    Thanks in advance,

    Cesar

    • danieliser on December 5, 2011 at 4:25 am

      It appears that the count for each is null. If these are the only items in the table do any of them have an id in the parent column?

      • Cesar on December 6, 2011 at 3:08 pm

        Hi,

        Thanks for your answer. I think that the problem is in the SQL code:

        AUTO_INCREMENT=248

        I drop the auto_increment and It is working well now. But I still see the NULL value in COUNT:

        mysql> 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=0;
        +—-+———+———-+——-+
        | id | label | link | Count |
        +—-+———+———-+——-+
        | 1 | Home | #home | NULL |
        | 2 | Code | #code | 2 |
        | 3 | Contact | #contact | NULL |
        +—-+———+———-+——-+
        3 rows in set (0.00 sec)

        Thanks in advance!

  15. Fabi on January 28, 2012 at 6:10 am

    Hi… @First sorry for my english im not a native speaker =)

    The tutorial is really great but now i got the problem i cant add a submenu to the 2. root – it will be a submenu of root 1 – can you tell me/do you know wheres the problem =)

    Thanks =)

    Best wishes from Germany

    • danieliser on January 30, 2012 at 3:14 am

      I think i need a little more information.

      All root items should have a parent of 0.

      If your root items are 1=>Home, 2=>Contact or similar then all submenu items for Contact will have a parent of 2 matching the id of the parent item.

  16. Brian on February 17, 2012 at 10:54 am

    Thanks for this tutorial, this helps me with the recursion logic!

  17. Sakhsen on May 15, 2012 at 6:53 am

    Thanks a lot! It helped to build dynamic menu in a short time.

  18. makarand on May 31, 2012 at 2:12 pm

    Great? This helped me a lot. Worked with no error.. Fine result..

    I want to create a dynamic with user access level.. What I mean that Menu will displayed according to user access level. How to do that.

    adding a user access level column and fetching at database query level.

    or handle at php level

  19. sang on June 13, 2012 at 6:36 am

    Thank you very much. This is what I need.

  20. karan on July 2, 2012 at 2:04 am

    ThNx….BrO…

  21. didoex on July 9, 2012 at 8:00 am

    can i download the script wiht Css Code beacuse i have some problem with Css it’s not working in level two
    it’s stoped and heddin when mouse put in ?

    • danieliser on October 1, 2012 at 3:25 pm

      I built the script to output in a way that you should be able to use any CSS menu design from CssPlay or similar sites.

  22. Surendra N Roy on August 25, 2012 at 10:20 am

    We studied your post, it is very good, I hope it will help us to make a dynamic menu for our web site. One thing I want extra, I did not find it here, we have many users they are in different group that means they have different users rights and assignments. I want a menu where it will be user or group specific, that means different users or group will get different menu (multi level ) dynamically. I hope that you under stood my problem. Thanks a lot in advance……

    • danieliser on October 1, 2012 at 3:25 pm

      In this case you just need to add a simple check in the function before outputting each item as well as possibly adding an extra field in your DB table to say which user group/groups have access to that menu item.

      Then in your script

      If user is logged in to specific group with access to this item
      {
      Show item
      }

  23. Eagle on September 29, 2012 at 5:02 pm

    Thanks for this tut.

  24. Falmesino on October 24, 2012 at 1:38 am

    How to print the menu like this : parent > parent > parent > child

  25. yuda on December 18, 2012 at 2:43 am

    thankssss

  26. Siddhartha Dutta on December 23, 2012 at 2:44 pm

    Thank you very very much……

  27. Nimatullah razmjo on March 3, 2013 at 1:30 am

    work great bro,
    it’s awsome
    thanx alot

  28. Joseph on June 7, 2013 at 12:57 am

    Thank you for providing this GREAT menu. I have to admit i’m no php guinness and need some help! 🙂 I have gotten it up and running and even made a page to dynamically add new pages. The menu works great and css styling… no problem. LOVE IT. However I cant figure out how to sort the menu. You stated to just add a sort statement but if you could let me know what is looks like that would be great cause I can figure it out. Thanks in advance for the help!

  29. anil on July 4, 2013 at 5:19 am

    how to create dynamic mega menu using php

  30. Semih on October 21, 2017 at 8:53 pm

    Thank you man. The best category code.

Leave a Comment