Single Query Dynamic Multi-level Menu
Since writing Dynamic Multi-level CSS Menu with PHP and MySQL. SEO Ready 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 $menu = array( 'items' => array(), 'parents' => array() ); // Builds the array lists with data from the menu table while ($items = mysql_fetch_assoc($result)) { // Creates entry into items array with current menu item id ie. $menu['items'][1] $menu['items'][$items['id']] = $items; // Creates entry into parents array. Parents array contains a list of all items with children $menu['parents'][$items['parent']][] = $items['id']; }
The $menu 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, $menu) { $html = ""; if (isset($menu['parents'][$parent])) { $html .= " <ul>n"; foreach ($menu['parents'][$parent] as $itemId) { if(!isset($menu['parents'][$itemId])) { $html .= "<li>n <a href='".$menu['items'][$itemId]['link']."'>".$menu['items'][$itemId]['label']."</a>n</li> n"; } if(isset($menu['parents'][$itemId])) { $html .= " <li>n <a href='".$menu['items'][$itemId]['link']."'>".$menu['items'][$itemId]['label']."</a> n"; $html .= buildMenu($itemId, $menu); $html .= "</li> n"; } } $html .= "</ul> n"; } return $html; } echo buildMenu(0, $menu);
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.
Hi Daniel, found this script and really love it, but I couldn’t make it work. Already spent 8 hours figuring out how to make it work and really wish I could use it, please I really need your help.
Since I am using a wrapper function and cant make use of mysql_fetch_assoc($result) to handle the db which returns the data in this way:
array(25) {
[0]=>
array(4) {
[“id”]=>
int(4)
[“name”]=>
string(0) “”
[“link”]=>
string(10) “login.html”
[“parent”]=>
int(0)
}
…
}
When using your script, I get:
array(2) {
[“items”]=>
array(1) {
[“”]=> array(25) {
[“id”]=>
int(4)
[“name”]=>
string(0) “”
[“link”]=>
string(10) “login.html”
[“parent”]=>
int(0)
…
}
}
[“parents”]=> array(0)
}
Can you let me know how could I implement this?
Here is the script:
$result = $db->queryAllRows(‘SELECT id, name, link, parent FROM ‘. $settings->dbprefix .’menus_options WHERE active = “1” ORDER BY parent, sort, name’);
// Create a multidimensional array to contain a list of items and parents
$menu = array( ‘items’ => array(),
‘parents’ => array()
);
// Builds the array lists with data from the menu table
while ($items = $result)
{
// Creates entry into items array with current menu item id ie. $menu[‘items’][1]
$menu[‘items’][$items[‘id’]] = $items;
// Creates entry into parents array. Parents array contains a list of all items with children.
$menu[‘parents’][$items[‘parent’]][] = $items[‘id’];
}
Hi there,
Very nice work π
I’m trying to add a css class to the script, but I can’t make it work.
The idea is that when you click on a specific menu point, it will be highlighted by inserting a class e.g.
I’m using p=[id] instead of link (#home).
Do you happens to have an idea on how I can build that function into the script?
Cheers,
Razzer
Very nice Script,
I trying to add the active class to the Script, like Danieliser have done it, but it doesnΒ΄t work.
I donΒ΄t have an Page ID, i have a Page Variable with description like this: $page = news; and my Links of this Script are named same as the $page variabel.
How do i make it work ?
How I make it work ?
Best thing you can do in this case is to try printing the variables using print_r(); Try
This allows you to debug issues.
Im assuming you are using the :active pseudo class for your clicked link. And also assuming $_GET[‘p’] is a page id passed with the link to the next page or with the page load itself. so you could do it like this
then replace
with
that should add the class if the item is = to the current page id. If its not then the $p will be empty
Hi danieliser,
This is perfect – you’re the man π
Thanks a lot.
R.
@Razzer
No problem. It took me quite a while to get a grasp and come up with this menu. There is more info on styling this menu on in this article: Dynamic Multi-level CSS Menu with PHP and MySQL. SEO Ready.
thanks for the feedback. if you wanna support us link to us. Thanks again
OMG mate this little script is so amazing and i have now made a little menu system for phpbb3 with it Thanks
Dave
Thank you very much. If you’d like to support us link to us or to our content. Again glad to see people getting use of this.
Hi,
I would like to add the level 0 items with class=”header” and it’s child with class=”nav_body”.
How can I do it?
Thanks,
bruce2046
@bruce2046, Sorry it took so long to reply. Been extremely busy.
You can add level functionality by changing
to
now you can easily add classes by level by change all <li> lines in the script to
Now each li will have a level class like ‘level1’
hope this helps
Hi
I've been racking my brains to come up with a way to only display and sub menus if they are a child of the level you are in. So similar to what you have shown I am setting up the query string so I know what page I'm on and have that highlighting. I then put in
if($pageID == $menu['items'][$itemID]['id']){
$html .= buildLeftMenu($itemID, $menu, 'y');
}
However if you are then on a page that is a child then the menu is not drawn.
Cheers
Neil
@Niel, sorry it took so long to reply, been very busy lately.
What you will need to do is create another function that takes the current page id and returns an array of all parents.
Then you can use
if((in_array($menu['items'][$itemID]['id',$parent_id_array)) || ($pageID == $menu['items'][$itemID]['id']))
Hope this helps.
Hi,
After reading your tutorial, I realise that my biggest problem is the association of the given script to all the other parts.
I mean, in this case, if you want to work with a MySQL database you'll need to get the connection with an host.inc.php and than
include "host.inc.php";
mysql_connect($host,$user,$pass);
mysql_select_db($db);
However, I always wanted to link this to a vertical menu… I already have the vertical menu ), now I just need to
think a little bit, on how this could be done…
@Alex, I assumed most people would know to include db connections on their own.
As for converting it to a vertical menu that is simple.. no changes are necessary to the php script at all..
You simply need to restyle it via css.. Instead of the li using display:inline-block, you could use block and that would make each on a new line.
You might have to modify the script for your JS dropdowns depending on how your selecting them.
Thx!
Hi danieliser,
I hope you can still help me with this awesome dynamic menu structure you have build.
I am trying to build a dynamic accordion menu which is as follows:
-Home
-Products
– Cookware
– Homeware
– Decorative
– Contact Us
Home
Products
Cookware
Homeware
Cookware
Decorative
Contact Us
I don’t know if the above makes any sense but the basic function of the menu is that if you click on “Products” it will expand and if you click on “Homeware” under “Products” it will expand as well.
My main problem is that I each parent to have its own structure so that it can be hidden and displayed when you click on its parent.
Hope you can assist me
Thank you,
Andre
Ok.. This makes several requests for a similar feature.. I can add functionality like that in the next update. If you know a little jquery and dont want to wait it can be done by setting the menu to show all children and hide them via jquery.. The only thing that is missing to make the menu work like you want is the jquery.
Hi,
Sorry I could not get the code that I inserted to show. I surrounded my code with
tags but it did not work unless I’m being blond and doing it wrong?
I am actually using jquery to display and hide the different parents.
The problem I just found with the jquery example I’m using is that it only allows for a two menu accordion menu!!!!! Which will not work.
Looking forward to your update on this using jquery or some sort of accordion menu.
Thank you for replying so quickly,
Andre
@Andre, I have to appoligize, i thought your original comment was on my Vertical Menu Widget plugin for wordpress. You should be able to format the menu any way you want based on this script. You can customize this to use divs instead of li if you wanted, try to find a demo of a jquery menu that you like and look at how it needs to be structured, then imitagte that structure in your menu script.
Fantastic work danieliser… π
I was making Sub Menus by using two functions.
1. To get all the Parent elements first.
2. To get all the childs (called in the above function with parameter as id)
But the Disadvantage was that it increased number of SQL queries.
So my Friend Lelebart suggested me this and it works very nice…
Well done π
I have modified the code to:
1. Hide content of submenus beyond top level.
2. If the file is # then the submenu opens without a link.
$menuiterations = 0; // Check how deep is the function call
// Menu builder function, parentId 0 is the root
function buildMenu($parent, $menu) {
global $menuiterations;
$html = '';
if (isset($menu['parents'][$parent])) {
if ($menuiterations<2) { // Top level menu
$html .= ''."\n";
}
else { // Deeper nested menu does not display submenus
$html .= ''."\n";
}
foreach ($menu['parents'][$parent] as $itemId) {
if ($menu['parents'][$itemId]==0) {
$html .= ''.$menu['items'][$itemId]['title_el'].$menuiterations.''."\n";
}
else {
$html .= ''.$menu['items'][$itemId]['title_el'].$menuiterations.'';
$menuiterations = $menuiterations+1; // Increase function call depth
$html .= buildMenu($itemId, $menu);
$html .= '';
}
}
$html .= ''."\n";
$menuiterations = $menuiterations-1; // Decrease function call depth
}
return $html;
}
However, I still need that the submenu is open when I load a page that belongs to the submenu. That is the display: none part should be so only when the current file is not in the current menu tree branch. I am afraid I didn’t understand your reply to Neil about the additional array.
Basically you will need a function that checks if # is a child of this menu item. Use that check before rendering each item.
Oops, the sanitizer removed parts of my code!
I was trying to use CSS to style this menu but still cannot figure out how to apply class to the . Can you post the full code with using class?
Thanks I’ve been looking for it. You saved my day cheers!
Hey man, nice work there. V2 is way better than V1.
1 issue though:
the “if” in line 15 is redundant since you already did the check in line 11.
if isset returned true then all you had to do was add the code inside the if block of line 15 inside an else block instead of wasting another if check.
keep up the good work!
Ty, I will adjust this. Most likely just an oversite at the time this was written. Nowadays my experience with php in general has massively improved. Ty for your comments..
Nice script! Saves me a lot of work!
The only thing that ain’t working for me is the url builing. I can’t figure it out….
In case of displaying available childs it doesn’t add the url(s) of the parent(s).
That’s fine when working with anchors like you do in your example, but doesn”t work if you use url rewriting.
This is what I would hope for: href=”/parent_url/child_url” , also recursive.
Do you think this is possible in your script?
HI! First sorry for bad English
how can i change order of menu item by using of sort field?
thanks
Hello Mr Green,
The query on line #2 sorts the results by the sort id. To use this for each item in your menu table just add order numbers to each row. like.
ID – Name – Parent – Sort
1 – Home – 0 – 0
2 – About – 0 – 1
The above means that Home will come before About. If you changed the sort column for home from 0 to 1 and the sort column of about from 1 to 0 then about would come first.
It is simply sorted in numerical order.
How can i sort stuff in the sub menues?
As for now, i can only sort the root items.
Like… i want sub item Dog to show before Cat in root menu Animals.
Now i can’t seem to make the menu do that. I can only sort “root” items.
I hope you understand me. π
Thanks!
Excellent brother …. I worked great. Thank you.
Hey,
I tried your code, but it is not displaying anything. I checked my database connection and it is working, but still can not figure out why it is not working….:(
here is my code in a file named “test.php”
=======================================
$db_host = “localhost”;
// Place the username for the MySQL database here
$db_username = “root”;
// Place the password for the MySQL database here
$db_pass = “password”;
// Place the name for the MySQL database here
$db_name = “menu_database”;
// Run the connection here
$myConnection = mysqli_connect(“$db_host”,”$db_username”,”$db_pass”, “$db_name”) or die (“could not connect to mysql”);
// Now you can use the variable $myConnection to connect in your queries
$sqlCommand1 = “SELECT id, label, link, parent FROM menu ORDER BY parent, sort, label”;
$result = mysqli_query($myConnection, $sqlCommand1) or die(“I am dead”);
// Create a multidimensional array to conatin a list of items and parents
$menu = array(
‘items’ => array(),
‘parents’ => array()
);
// Builds the array lists with data from the menu table
while ($items = mysqli_fetch_assoc($result))
{
// Creates entry into items array with current menu item id ie. $menu[‘items’][1]
$menu[‘items’][$items[‘id’]] = $items;
// Creates entry into parents array. Parents array contains a list of all items with children
$menu[‘parents’][$items[‘parent’]][] = $items[‘id’];
}
// Menu builder function, parentId 0 is the root
function buildMenu($parent, $menu)
{
$html = “”;
if (isset($menu[‘parents’][$parent]))
{
$html .= ”
n”;
foreach ($menu[‘parents’][$parent] as $itemId)
{
if(!isset($menu[‘parents’][$itemId]))
{
$html .= “n “.$menu[‘items’][$itemId][‘label’].”n n”;
}
if(isset($menu[‘parents’][$itemId]))
{
$html .= ”
n “.$menu[‘items’][$itemId][‘label’].” n”;
$html .= buildMenu($itemId, $menu);
$html .= ” n”;
}
}
$html .= ” n”;
}
return $html;
}
buildMenu(0, $menu);
I have written a PHP class also available on my blog that will convert flat data such as results from a table into a Parent > Child > Child type array that can easily be parsed by that script. Give it a shot and let me know.
Good job Danieliser, Found this after much search.
I only have a challenge with the links especially when rewriting. How do you think the links should be written?
e.g.
Page link
Home home
School school
-Student school/student
-Staff school/staff
Contact contact
-Feedback contact/feedback
-Form contact/form
In rewriting the url, I would have an issue with calling the child pages. What is the best way of solving this?
You are great man u saved my lots of time. I modified it for my needs and it works perfectly. Thank you so much.
Great to hear.. there is also a PHP class that will take a flat array similar to the results from a table and stack them in parent child relationships. It is also located in my blog.
How would the script work withe ezsql ?
thx for your answer
Hello.
Excellent script bro’. Just customized it and it works perfectly. I was wondering.
Do you have any idea how to make it a collapse menu with no JS/Jquery only pure php?
Right now all the parents are expanded, i would like them to be closed and clickable :-).
Again, great work!
Regards, Simon
Hi danieliser,
I’m a newbie. Can u show me how to implement the CSS for horizontal menu of the above code.I’m getting only the vertical menu, i have tried using classes but failed.
Thanks.
Hi π
Great work man!
I have a little problem to highlight current page.
I’m try using jQuery but without success.
On my website i have one big class and there functions. I put menu array to buildMenu function but i’m stuck with working highlight current page (with )
Hey
I use your code at many diverent homepages. And all is very nice!
But now i have a problem.
I know you have write about active Item when the id is the same. But now i want this:
here the menu:
start | products | contact
– item 1
– item 2
– item 3
if i click at the button item2, and the page is new open, i want that the products-button get a css-class active.
And the same, when i click at level 0 buttons.
i know you understand me.
can you help me?
To accomplish this you basically need to loop children first before outputting the parents html so that you can know if its child is active first. Or you need to determine whether the current item has an active child. i would have to work it out before i could give a definitive answer.
Hi and thank you very much for your script π
I have modified the code to:
1. personalize li class style
2. If the link have an anchor #
Very simple to do with adding columns class and anchor in the Bdd and 1 more if in your function. But now I have a problem because I want that the script detects the active menu or sub-menu and modify the li class by add “open” for the css of course.
I have an id page in the url = id_page of Bdd so i try several things like this :
if (isset($_GET[‘id’]) == $menu[‘items’][$itemId][‘id_page’]) ….
But with this in the function, I have for example all menu level 0 active… Have you an idea to do this ?
Thanks
In fact I found to have the class active, it was simple – add like this :
if ($_GET[‘id’] == $menu[‘items’][$itemId][‘id_page’]) {
$html .= “<li class=\"open ";
} else {
$html .= "<li class=\"";
}
…
But that work just for the active menu, not for the parent of this menu… for this I don't see how to do
Hello and thanks for the tutorial I have been helpful, but I have a problem with the menu smaller id is not shown, it can be.
this is my script:
array(),
‘parents’ => array()
);
// Builds the array lists with data from the menu table
while ($items = mysql_fetch_assoc($menuq))
{
// Creates entry into items array with current menu item id ie. $menu[‘items’][1]
$menu[‘items’][$items[‘id’]] = $items;
// Creates entry into parents array. Parents array contains a list of all items with children
$menu[‘parents’][$items[‘parent’]][] = $items[‘id’];
}
// Menu builder function, parentId 0 is the root
function buildMenu($parent, $menu)
{
$html = “”;
if (isset($menu[‘parents’][$parent]))
{
$html .= ”
\n”;
foreach ($menu[‘parents’][$parent] as $itemId)
{
if(!isset($menu[‘parents’][$itemId]))
{
$html .= “\n “.$menu[‘items’][$itemId][‘label’].”\n \n”;
}
if(isset($menu[‘parents’][$itemId]))
{
$html .= ”
\n “.$menu[‘items’][$itemId][‘label’].” \n”;
$html .= buildMenu($itemId, $menu);
$html .= ” \n”;
}
}
$html .= ” \n”;
}
return $html;
}
echo buildMenu(0, $menu);
?>