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:
display_children(4, 1);
Would return only the children of PHP or (id #4)
This second use can come in handy if you want to build a sub menu any where in your site.
The menu can be further customized as i mentioned above with css as can be seen in use at B&B Metals.
The code for the menu on that site is slightly more complex due to different css classes being used for lower levels in the menu. You can easily sort the menu by adding a sort statement to the sql statement and giving your menu items a sort order.
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
Incoming search terms:
- php dynamic menu
- dynamic menu in php
- dynamic menu php
- dynamic menu php mysql
- php multi level menu
- php mysql menu
- menu php mysql
- menu in php
- php dynamic menu from database
- php mysql dynamic menu
- recursive menu php mysql
- php menu mysql
45 Comments
work great bro,
it’s awsome
thanx alot
Thank you very very much……
thankssss
How to print the menu like this : parent > parent > parent > child
Thanks for this tut.
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……
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
}
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 ?
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.
ThNx….BrO…
Thank you very much. This is what I need.
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
Thanks a lot! It helped to build dynamic menu in a short time.
Thanks for this tutorial, this helps me with the recursion logic!
Glad i could help.
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
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.
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
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?
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!
You can view demo as well as download the code for such a dynamic multi level menu at http://ewizard.co.cc/blog/how-to-build-dynamic-multi-level-CSS-menu-with-PHP-and-MySQL
Hi,
thanks so much – this seems to be exactly what I need. The download link seems to be down, can you please re-up it?
Thanks!
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?
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.
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
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.
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.
The category and links is not a problem. The problem is to hide the submenus, if a user has not clicked them yet
Hello,
Thanks a lot for providing such a nice code, you saved my life. Thanks again.
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.
Thanks you very much. Glad I can help.
——————————————————–
Muchas gracias. Me alegro de que le puede ayudar.
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
thanks a lot!
Great code but cannot convert it into horizontal need help
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.
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.
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
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
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.
This is such a great resource that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource for free. It?s the old what goes around comes around routine.
Amiable fill someone in on and this fill someone in on helped me alot in my college assignement. Say thank you you seeking your information.
I am having trouble with my email. I get an error message that reads: An unknown error has occured. Subject”, Account:’ mail.hammerdowntrailers.com’, Protocol:SMTP, server Response: ’554 5.7.1 IP Blocked, send your mail to postmaster@node91.myserverhosts.com to ensure delivery’, Port: 25, Secure (SSL): No Server Error:554, Error Number 0x800CC6F.
The problem started when I went to http://www.hammerdowntrailers.com/cpanel and changed the text on my website. I know I had to of messed something up because it was working fine before I started to change things. I am not an expert at html and I really need this fixed can you guide me in fixing my problems or tell me how I can find what incoming mail POP3 and outgoing SMTP I should use. I am using Windows Mail. Thank you so much.
@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.
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.
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
One Trackback
[...] Googled and found something that can work for me (wizardinternetsolutions.com/we…). I plan to modify it a bit, to tailor it for my needs, once I get it to work. -Ej. I want to use [...]