Dynamically building horizontal menu

Question

I have this JavaScript code that allows me to create a horizontal menu with sub menus like so:

<ul id="menu">
<li>Menu 1
  <ul>
   <li>Sub Menu 1</li>
  </ul>
</li>
</ul>

I can create as many sub menus as I want, the problem is I'm using PHP to grab the links from a MySQL database and don't know how I can dynamically build these sub menus without manually checking a sub menu over and over again. For example in the MySQL table:

Fields: Menu_ID Menu_Name Menu_Link Menu_ParentID

So menu ID is just an auto increment and the menu_parentid allows me to assign a sub menu name/link to a parent menu. But in order to do it I currently do this for 2 sub menu checks:

$query = "SELECT * FROM site_menu WHERE Menu_ParentID = 0";

foreach($query AS $q)
{
//run through the results
$query2 = "SELECT * FROM site_menu WHERE Menu_ParentID = $q['id']";

foreach($query2 AS $q2)
{
//run through the results
}
}

As you can see I have to query twice to get just the first sub menu, what if there is a third sub menu? Do I have to run 3 queries? Any suggestions?

score 1 · Accepted Answer · answered Apr 18 '13 at 02:11

Perhaps a function or a do..while loop may be in order? Proof of concept:

function menuQuery($id)
{
    $query = "SELECT * FROM site_menu WHERE Menu_ParentID = $id";

    if ($query) {
        foreach($query AS $q) {
            //run through the results
            menuQuery($q->id);
        }
    }
}

//initial call of top level menu items
menuQuery(0);

score 0 · Answer 2 · answered Apr 18 '13 at 02:01

I would suggest to put a sort or order column in your menu table. You could then pull back all of the results using the sort column. This would allow you to order the Top level of the menu with the sub menu items right underneath each other.

With that you could create just one loop with the database results and choose what section of the menu to write based on whether the parent_id is populated or not.

score 0 · Answer 3 · answered Apr 18 '13 at 02:22

Write a single query, ordered by Menu_ParentID
Process that query one row at a time. You'll be starting with the root since it's ID is 0. For each row, loop through all the other rows and find the children of that row, ie, those rows whose parent id equals the id of the row you are processing. Use this process to build a data structure like so:
```
[ Root Item 1, [Child1, Child2, [SubChild1, SubChild2], Child3], Root Item 2, ...]
```

Write a helper function like so (untested):

function displayItem($item) {
  if (is_array($item)) {
    $html = '<li><ul>';
    foreach ($item as $subitem) $html .= displayItem($subItem);
    $html .= '</ul></li>';
    return $html;
  }
  else return '<li>' . $item . '</li>';
}

Loop through the structure you created in 2., calling displayItem for each element

medina · Answer 4 · 2013-04-18T05:33:09.570

here is an example which runs just once on the database and just using a simple recursive function it brings the array hierarchically.

# table
CREATE TABLE foo (
    menu_id int not null auto_increment primary key, 
    menu_name varchar(255) not null, 
    menu_link varchar(255) not null default '#',
    menu_parent_id int not null default 0
);

# data 
INSERT INTO `test`.`foo`
    (`menu_id`,`menu_name`,`menu_link`,`menu_parent_id`)
VALUES
    (1, 'Catalog', '#', 0),
    (2, 'Reports', '#', 0),
    (3, 'Products', '#', 1),
    (4, 'Sales', '#', 2),
    (5, 'Customers', '#', 2),
    (6, 'Tvs', '#', 3);

Here is your php code:

<?php
$db = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '****');

$stmt = $db->query("SELECT * FROM foo");
$resultSet = $stmt->fetchAll(PDO::FETCH_ASSOC);


$setMenu = function ($data, $index = 0) use (&$setMenu){
                $menu = array();

        foreach ($data as $row) {
            if ((int) $row['menu_parent_id'] !== $index)
                continue;

            $menu[$row['menu_id']] = array(
                'name' => $row['menu_name'],
                'link' => $row['menu_link'],
                'submenus' => $setMenu($data, (int) $row['menu_id']),
            );
        }

        return $menu;
};

// your menu 
$menu = $setMenu($resultSet, 0);

var_export($menu);

It is gonna give you the information ready to go in your list (ul).

Cheers!

score 0 · Answer 5 · edited May 23 '17 at 10:09

0

The best technique for storing website navigation trees is called the Nested Set Model.

There is a good example implementation here: Dealing with nested sets in mysql?

edited May 23 '17 at 10:09

Community

1
1

answered Apr 18 '13 at 04:16

Abhi Beckert

32,787
12
83
110

JOE LEE · Answer 6 · 2013-04-18T04:57:29.603

$data=array(
array('Menu_ID'=>1, 'Menu_Name'=>'Catalog', 'Menu_Link'=>'#', 'Menu_ParentID'=>0),
array('Menu_ID'=>2, 'Menu_Name'=>'Reports', 'Menu_Link'=>'#', 'Menu_ParentID'=>0),
array('Menu_ID'=>3, 'Menu_Name'=>'Products','Menu_Link'=> '#','Menu_ParentID'=> 1),
array('Menu_ID'=>4, 'Menu_Name'=>'Sales','Menu_Link'=> '#', 'Menu_ParentID'=>2),
array('Menu_ID'=>5, 'Menu_Name'=>'Customers','Menu_Link'=> '#', 'Menu_ParentID'=>2),
array('Menu_ID'=>6, 'Menu_Name'=>'Tvs','Menu_Link'=> '#','Menu_ParentID'=> 3));

print_r(loop_menu($data));

// Menu_ID Menu_Name Menu_Link Menu_ParentID
function loop_menu($rows,$parent = 0){
 $arr=array();
 $i=0;
  foreach ($rows as $row)
  { 
        if (array_key_exists('Menu_ParentID',$row) && $row['Menu_ParentID'] == $parent){

                if(array_key_exists($i,$arr)){
                    $arr[$i]=array();
                }
                $arr[$i]['data']=$row;
                $arr[$i]['child']= loop_menu($rows,$row['Menu_ID']);
                $i++;
        }
  }
    return $arr;
}

then

then code something like array to ul

http://sandbox.onlinephpfunctions.com/code/2b3ab04f959413ebf75b65034edd60da61ed0020

update

another array style

$arr[$i]['data'] = $row;
$arr[$i]['child']= loop_menu($rows,$row['Menu_ID']);

change to

$row['child'] = loop_menu($rows,$row['Menu_ID']);
$arr[$i] = $row;

get

Dynamically building horizontal menu

6 Answers6

Linked