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.

Faster WordPress Hosting

47 Comments

  1. Milo on January 2, 2017 at 2:42 am

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

  2. Razzer on July 26, 2010 at 1:43 pm

    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.

    <a href="?p=1">Home</a>;

    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?

    if (isset($_GET['p']) && $_GET['p'] != '') { 
    	$p = $_GET['p'];
    } else {
    	$p = 1;	
    }
    function getSelected($v) {
    	global $p; 
    	if ($p == $v) {
    		echo ' class="selected"';	
    	}
    }

    Cheers,
    Razzer

    • Starfox on November 17, 2011 at 8:11 am

      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 ?

      if (isset($_GET["page"])) {
      $page = $_GET["page"];
      }
      
      if (in_array($page, $menu['items'][$itemId]['link'])) { $activeclass="selected"; }
      
      $html .= "<a>".$menu['items'][$itemId]['label'].""</a>";

      How I make it work ?

      • danieliser on November 22, 2011 at 10:03 pm

        Best thing you can do in this case is to try printing the variables using print_r(); Try

        print_r($menu['items'][$itemId]['link']);

        This allows you to debug issues.

  3. danieliser on July 26, 2010 at 7:04 pm

    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

    function is_active($p_id){ 
    	$active_class = ''; 
    	if (isset($_GET['p']) && $_GET['p'] == $p_id) { 
    		 $active_class = 'class="active_class"'; 
    	} 
    	return $active_class;
    }
    

    then replace

    $html .= "<a href='".$menu['items'][$itemId]['link']."'>".$menu['items'][$itemId]['label']."</a>";

    with

    $html .= "<a ".is_active($itemId)." href='".$menu['items'][$itemId]['link']."'>".$menu['items'][$itemId]['label']."</a>";

    that should add the class if the item is = to the current page id. If its not then the $p will be empty

  4. Razzer on July 27, 2010 at 3:09 am

    Hi danieliser,

    This is perfect – you’re the man πŸ™‚
    Thanks a lot.

    R.

  5. Dave M on November 28, 2010 at 11:56 am

    OMG mate this little script is so amazing and i have now made a little menu system for phpbb3 with it Thanks

    Dave

    • danieliser on January 17, 2011 at 10:36 am

      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.

  6. bruce2046 on January 17, 2011 at 4:50 am

    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

    • danieliser on May 22, 2011 at 3:23 pm

      @bruce2046, Sorry it took so long to reply. Been extremely busy.
      You can add level functionality by changing

      $html .= buildMenu($itemId, $menu);

      to

      if(!isset($level)) $level = 0;
      $level++;
      $html .= buildMenu($itemId, $menu);
      $level--;

      now you can easily add classes by level by change all <li> lines in the script to

      <li class='level" . $level . "'>

      Now each li will have a level class like ‘level1’

      hope this helps

  7. Neil on April 20, 2011 at 12:08 am

    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

    • danieliser on May 22, 2011 at 3:33 pm

      @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.

  8. Alex on April 22, 2011 at 5:07 am

    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…

    • danieliser on May 22, 2011 at 3:09 pm

      @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.

  9. Jelmer Visser on July 3, 2011 at 11:52 am

    Thx!

  10. Andre van der Walt on July 19, 2011 at 11:40 am

    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

    • danieliser on July 19, 2011 at 3:05 pm

      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.

      • Andre van der Walt on July 20, 2011 at 12:18 am

        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

        • danieliser on July 20, 2011 at 7:40 pm

          @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.

  11. Ankur Thakur on September 11, 2011 at 6:19 am

    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 πŸ™‚

  12. T.G.D. on October 5, 2011 at 7:22 am

    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.

    • danieliser on October 5, 2011 at 12:29 pm

      Basically you will need a function that checks if # is a child of this menu item. Use that check before rendering each item.

  13. T.G.D. on October 5, 2011 at 7:23 am

    Oops, the sanitizer removed parts of my code!

  14. adcounts86 on December 21, 2011 at 12:42 pm

    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?

  15. smronju on February 2, 2012 at 2:01 pm

    Thanks I’ve been looking for it. You saved my day cheers!

  16. Varemenos on March 25, 2012 at 3:29 pm

    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!

    • danieliser on March 25, 2012 at 8:51 pm

      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..

  17. Ross on May 7, 2012 at 8:29 pm

    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?

  18. Re on June 16, 2012 at 8:57 am

    HI! First sorry for bad English
    how can i change order of menu item by using of sort field?
    thanks

    • danieliser on June 17, 2012 at 2:06 pm

      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.

      • Nicke on July 9, 2012 at 4:21 pm

        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!

  19. Cardex on August 4, 2012 at 7:17 pm

    Excellent brother …. I worked great. Thank you.

  20. Amit Yadav on August 27, 2012 at 10:37 am

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

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

      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.

  21. infinitizon on August 30, 2012 at 2:03 pm

    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?

  22. Sajid Javed on September 15, 2012 at 3:31 pm

    You are great man u saved my lots of time. I modified it for my needs and it works perfectly. Thank you so much.

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

      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.

  23. Daniel on October 7, 2012 at 2:40 pm

    How would the script work withe ezsql ?

    thx for your answer

  24. Simon on October 19, 2012 at 2:21 pm

    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

  25. kiki on October 26, 2012 at 6:34 am

    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.

  26. Lukas on November 26, 2012 at 2:59 pm

    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 )

  27. Lorenz on January 1, 2013 at 10:02 am

    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?

    • danieliser on January 5, 2013 at 10:20 pm

      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.

  28. Angel on February 8, 2013 at 11:32 am

    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

    • Angel on February 12, 2013 at 4:28 pm

      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

  29. Pablo on February 16, 2013 at 5:07 pm

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

Leave a Comment