Navigation menu with hierarchical nested lists...

shonuff

Hey guys, I'm trying to wrap my head around this problem. I have a horizontal menu that is dynamically created from a MySQL database. Getting it to display the menu items at the top-level is easy, but I was wondering what exactly I need to do to get it to display any submenu items that match the parent's id? I'm having trouble sorting out the pseudo code on paper that will allow this to work.

My main purpose is to have an easy way to add/edit main menu items and/or submenu items on the backend without having people delve into the actual website code. All of that logic has pretty much been coded already though, what I really am stuck on is displaying menus and the corresponding menu's sub menus.

Here is my database schema for the navigation...

CREATE TABLE `nav_menu_main` (
  `menu_id` tinyint(5) unsigned NOT NULL auto_increment,
  `menu_text` varchar(50) NOT NULL,
  `menu_url` varchar(50) NOT NULL,
  `menu_active` tinyint(1) unsigned NOT NULL,
  `menu_is_editable` tinyint(1) unsigned NOT NULL,
  `menu_pos` tinyint(5) unsigned NOT NULL,
  PRIMARY KEY  (`menu_id`))

CREATE TABLE `nav_menu_submenu` (
  `sub_menu_id` tinyint(5) unsigned NOT NULL auto_increment,
  `menu_id` tinyint(5) unsigned NOT NULL,
  `sub_menu_text` varchar(50) NOT NULL,
  `sub_menu_url` varchar(50) NOT NULL,
  `sub_menu_active` tinyint(1) unsigned NOT NULL,
  `sub_menu_external` tinyint(1) unsigned NOT NULL,
  `sub_menu_pos` tinyint(5) unsigned NOT NULL,
  PRIMARY KEY  (`sub_menu_id`))

Here's the code I use to get the main navigation items out of the database into an unordered list.

<?php

$query = "SELECT menu_id, menu_text, menu_url, menu_pos
		  FROM nav_menu_main
		  WHERE menu_active = 1
		  ORDER BY menu_pos";

// Run the query
if (!($result = mysql_query($query, $conn)))
   die("***Error running query:" . mysql_error());
   while ($row = mysql_fetch_assoc($result))
   {
       $menu_id = $row["menu_id"];
   	$menu_text = $row["menu_text"];
   	$menu_url = $row["menu_url"];
        //$pageid is set somewhere else in the page's code
	if ($pageid == $menu_id){
	$style = " class=\"active\"";
	}else{
	$style = "";
   }
	echo "\t\t<li><a href=\"" . $menu_url . "\"" . $style . ">" . $menu_text . "</a></li>\n";
}
?>

How do I make it so that when a menu contains children it will nest them under the correct parent?

For example:

<div id="nav">
	<ul>
		<li><a href="/">Home</a></li>
                <!--This parent item contains a child-->
		<li><a href="/schedule/">Schedule</a>
                       <ul>
                              <li>2007 Schedule of Events</li>
                       </ul>
                </li>
		<li><a href="/results/">Race Results</a></li>
		<li><a href="/points/">Points Standings</a></li>
		<li><a href="/gallery/">Photo Gallery</a></li>
		<li><a href="/visitors/">Visitors</a></li>
		<li><a href="/contact/">Contact Us</a></li>
		<li><a href="/forums/">Forums</a></li>
	</ul>
</div>

I hope I was clear enough in what I am trying to accomplish. Any help would be greatly appreciated.

bogu

Here is an interesting article: Storing Hierarchical Data in a Database

liquorvicar

It depends if you need to load all menus and submenus on each page (i.e. for some kind of expanding menu/dropdown).
If you are then:

SELECT m.menu_id, menu_text, menu_url, menu_pos, sub_menu_text, sub_menu_url, sub_menu_pos
          FROM nav_menu_main m LEFT JOIN nav_menu_submenu s using (menu_id)
          WHERE menu_active = 1 AND sub_menu_active=1
          ORDER BY menu_pos,sub_menu_pos

would work. Run through the query and load the results into a multidimensional array. You can then use foreach() to loop through the array and generate your HTML.

shonuff

Yeah, liquorvicar that is the method I'm going for. Thanks for the help.

I've never dealt with arrays, much less multidimensional arrays, I guess my time has come. :-)

So the query that you wrote will get all main menu items and the submenu items all in one query?

I'm not sure I'm postive how to set up my results into a multidimensional array or how to use the foreach on the array, but I will google like crazy to find an answer.

If I get stuck I'll be sure to post back.

shonuff

OK, I just don't get it.

I had to modify the query:

SELECT m.menu_id, menu_text, menu_url, menu_pos, sub_menu_text, sub_menu_url, sub_menu_pos
FROM nav_menu_main m LEFT JOIN nav_menu_submenu s using (menu_id)
WHERE menu_active = 1 AND sub_menu_active=1
ORDER BY menu_pos,sub_menu_pos

SELECT m.menu_id, menu_text, menu_url, menu_pos, sub_menu_text, sub_menu_url, sub_menu_pos
FROM nav_menu_main m LEFT JOIN nav_menu_submenu s using (menu_id)
WHERE menu_active = 1
ORDER BY menu_pos,sub_menu_pos

because it was only pulling results where the menu and submenu where classified as active.

Other than that I've gotten nowhere. Can someone please help me understand how exactly to get where I need to be? I think I may be overthinking this, but it is just as possible that this is beyond my skill level.

shonuff

OK, another update. I tried the following:

$query = "SELECT m.menu_id, menu_text, menu_url, menu_pos, sub_menu_text, sub_menu_url, sub_menu_pos
          FROM nav_menu_main m LEFT JOIN nav_menu_submenu s using (menu_id)
          WHERE menu_active = 1
          ORDER BY menu_pos,sub_menu_pos";

// Run the query
if (!($result = mysql_query($query, $conn)))
   die("***Error running query:" . mysql_error());

$result = mysql_query($query, $conn);
$rs = array();
while ($row = mysql_fetch_assoc($result))
{
    array_push($rs, $row);
}
echo "<pre>";
print_r($rs);
echo "</pre>";

and got the following results... (Only schedule contains a child right now)

Array
(
    [0] => Array
        (
            [0] => 1
            [1] => Home
            [2] => /home/
            [3] => 1
            [4] => 
            [5] => 
            [6] => 
        )

[1] => Array
    (
        [0] => 2
        [1] => Schedule
        [2] => /schedule/
        [3] => 2
        [4] => 2007 Schedule of Events
        [5] => /schedule/mode=getall&?y=2007
        [6] => 1
    )

[2] => Array
    (
        [0] => 3
        [1] => Race Results
        [2] => /results/
        [3] => 3
        [4] => 
        [5] => 
        [6] => 
    )

[3] => Array
    (
        [0] => 4
        [1] => Points Standings
        [2] => /points/
        [3] => 4
        [4] => 
        [5] => 
        [6] => 
    )

[4] => Array
    (
        [0] => 5
        [1] => Photo Gallery
        [2] => /gallery/
        [3] => 5
        [4] => 
        [5] => 
        [6] => 
    )

[5] => Array
    (
        [0] => 6
        [1] => Visitors
        [2] => /visitors/
        [3] => 6
        [4] => 
        [5] => 
        [6] => 
    )

[6] => Array
    (
        [0] => 7
        [1] => Contact Us
        [2] => /contact/
        [3] => 7
        [4] => 
        [5] => 
        [6] => 
    )

[7] => Array
    (
        [0] => 8
        [1] => Forums
        [2] => /forums/
        [3] => 8
        [4] => 
        [5] => 
        [6] => 
    )

)

Am I getting any closer? Where do I go from here? 😕

shonuff

Anyone? I'm not looking for handouts here, I'm looking to learn. :-)

liquorvicar

Close. The key here is to make the main menu_id the key to the top level of the array, something like this:

 $query = "SELECT m.menu_id, menu_text, menu_url, menu_pos, sub_menu_text, sub_menu_url, sub_menu_pos
          FROM nav_menu_main m LEFT JOIN nav_menu_submenu s using (menu_id)
          WHERE menu_active = 1
          ORDER BY menu_pos,sub_menu_pos";

// Run the query
if (!($result = mysql_query($query, $conn)))
   die("***Error running query:" . mysql_error());

$result = mysql_query($query, $conn);
$rs = array();
while ($row = mysql_fetch_assoc($result))
{
    if (isset($row[$rs['menu_id']])) {
        $row[$rs['menu_id']]['sub_menu'][] = array('text'=>$rs['sub_menu_text'],
                'url'=>$rs['sub_menu_url'],
                'pos'=>$rs['sub_menu_pos']);
    } else {
        $row[$rs['menu_id']] = array('text'=>$rs['menu_text'],
              'url'=>$rs['menu_url'], 
              'pos'=>$rs['menu_pos'],
              'sub_menu'=>array('text'=>$rs['sub_menu_text'],
                      'url'=>$rs['sub_menu_url'],
                      'pos'=>$rs['sub_menu_pos']));
    }
}
echo "<pre>";
print_r($rs);
echo "</pre>";

shonuff

Thanks for the help, liquorvicar.

OK I tried the above code and when I ran it all I got was an empty array. I looked for any errors I could find in the code and there is only one that I could spot, and that was this line:

$row[$rs['menu_id']]['sub_menu'][] = array('text'=>$rs['sub_menu_text'],

should be: (i think)

$row[$rs['menu_id']]['sub_menu_id'][] = array('text'=>$rs['sub_menu_text'],

Still printed out an empty array when I ran

print_r($rs);

The code seems logical to me, is there something I'm missing? It should have printed out something, am I right?

I'll keep working with it...

liquorvicar

No that bit was right. The reason it's messed up is my fault I got $row and $rs the wrong way round. Try this instead:

// Run the query
if (!($result = mysql_query($query, $conn)))
   die("***Error running query:" . mysql_error());

$result = mysql_query($query, $conn);
$row = array();
while ($rs = mysql_fetch_assoc($result))
{

$row is now the array you are building and $rs is the recordset retrieved from the database.

shonuff

Sorry it's taking so long to reply, my ftp is in and out this morning and causing other headaches.

Anyway.

Ok now it looks like we're getting somewhere. I get the following:

The structure looks right. Now how do I get that array into something usable? Is this where we run the foreach to loop through the code to generate the html?

Also, what is happening in that line that I though was wrong? I'm just trying to understand. 🙂

liquorvicar

Actually, there's still an error in the code:

$row[$rs['menu_id']] = array('text'=>$rs['menu_text'],
                  'url'=>$rs['menu_url'],
                  'pos'=>$rs['menu_pos'],
                  'sub_menu'=>array('text'=>$rs['sub_menu_text'],
                          'url'=>$rs['sub_menu_url'],
                          'pos'=>$rs['sub_menu_pos']));

should be

$row[$rs['menu_id']] = array('text'=>$rs['menu_text'],
                  'url'=>$rs['menu_url'],
                  'pos'=>$rs['menu_pos'],
                  'sub_menu'=>array());
$row[$rs['menu_id']]['sub_menu'][] = array('text'=>$rs['sub_menu_text'],
                          'url'=>$rs['sub_menu_url'],
                          'pos'=>$rs['sub_menu_pos']);

As for the next step, have a read of the manual: foreach() and see if you can figure it out. Come back with any questions.

shonuff

Thank you for your help, liquorvicar. I will definitely do that. I am trying to learn after all.

Can you explain to me in simpleton terms what is happening in this line?

$row[$rs['menu_id']]['sub_menu'][] = array('text'=>$rs['sub_menu_text'], ...

Are we just saying that this array contains a submenu? Assigning a name? I'm not following.

Thanks again for all your help.

liquorvicar

It's adding the current sub menu (i.e. the sub menu in the current recordset from the D😎 and adds it to the relevant main menu entry in the array.
I've cleaned the code up a bit and put some comments in:

// Run the query
$result = mysql_query($query, $conn);
// Initialise our data array
$row = array();
// Fetch our DB results and put them into our data array
while ($rs = mysql_fetch_assoc($result)) {
	// If the current main menu is not in the data array add it
	if (!isset($row[$rs['menu_id']])) {
		$row[$rs['menu_id']] = array('text'=>$rs['menu_text'],
			'url'=>$rs['menu_url'],
			'pos'=>$rs['menu_pos'],
			'sub_menu'=>array());
	}
	// Add the current sub menu to the relevant main menu array entry
	$row[$rs['menu_id']]['sub_menu'][] = array('text'=>$rs['sub_menu_text'],
		'url'=>$rs['sub_menu_url'],
		'pos'=>$rs['sub_menu_pos']);
}
echo "<pre>";
print_r($row);
echo "</pre>";

shonuff

Thanks for the explanation, it's clearer now.

I've managed to use a foreach to print out the values of the main menu items using this:

foreach ($row as $mainmenu) {
echo "\t\t<li><a href=\"" . $mainmenu['url'] . "\">" . $mainmenu['text'] . "</a></li>\n";
}

That gives me a nice unordered list of the main menu items:

<!--Begin Navigation-->
<div id="nav">
	<ul>
		<li><a href="/home/">Home</a></li>
		<li><a href="/schedule/">Schedule</a></li>
		<li><a href="/results/">Race Results</a></li>
		<li><a href="/points/">Points Standings</a></li>
		<li><a href="/gallery/">Photo Gallery</a></li>
		<li><a href="/visitors/">Visitors</a></li>
		<li><a href="/contact/">Contact Us</a></li>
		<li><a href="/forums/">Forums</a></li>
	</ul>
</div>
<!--End Navigation-->

But how do I reference the submenu items and get respective submenu items to nest under their parent? Do I nest the foreach statements together? How do I get a main menu item that has a submenu to drop the ending </li> and nest an unordered list there then re-append the closing </li>? I feel like I'm making this way more difficult than it really is.

liquorvicar

Yes, you can nest foreach loop.
Try splitting your UL over a number of echo statements and inserting the inner foreach in the right place.
If you get stuck, try

print_r($mainmenu);

to see what form that array takes and what you need to use for the inner loop.

shonuff

You can use if/else statements within a foreach as well, right? Is that how I need to find if a main menu item has submenu items? I managed to get the foreach to print out the main menu, but I am lost as to how to get the values for the subnavigation.

I know this is wrong, but I'm posting it to show that I am trying :-)

foreach ($row as $mainmenu) {
echo "<li><a href=\"" . $mainmenu['url'] . "\">" . $mainmenu['text'] . "</a>\n";
foreach ($row as $submenu) {
echo "<ul>\n<li><a href=\"" . $submenu['url'] . "\">" . $submenu['text'] . "</a></li>\n</ul>\n";
 }
echo "</li>\n";
}

I'm not accessing the right values for the submenu items, I know that much. I am so over my head here. I've learned a lot today, but I just can't grasp the concept of how to make this work the way it needs to. I know I'm getting close though.

liquorvicar

You're very close, try this:

foreach ($row as $mainmenu) {
echo "<li><a href=\"" . $mainmenu['url'] . "\">" . $mainmenu['text'] . "</a>\n";
foreach ($mainmenu['sub_menu'] as $submenu) {
echo "<ul>\n<li><a href=\"" . $submenu['url'] . "\">" . $submenu['text'] . "</a></li>\n</ul>\n";
}
echo "</li>\n";
}

You want the inner loop to loop through the 'sub_menu' array for each mainmenu. As the outer loop is already looping through each main menu as $mainmenu, you can access the array of sub menus as $mainmenu['sub_menu'] and loop through it.

Yes, you can use if/else blocks within foreach loops (you can put any code within a foreach loop) but I don't think you need to. $mainmenu['sub_menu'] will always be an array, if there are no sub menus it will be empty and the foreach loop won't execute at all.