Multilevel dynamic Drop down menu

Hi to all,

I'm trying to create a dynamic dropdown menu that populates by using Mysql and generates a dynamic link.  I haven?t been able to get the children of each parent menu. Here it is my code and Database...

Database

tb_menus (Main Menu)

menu_id, menu_name, status

tb_pages (Child Menu)

page_id, menu_id, page_title, page_name, page_content, page_status

<ul>
$db = new testCore();
$db->getAll('tb_menus');
	$numHeader = mysql_num_rows($db->Command);
	if($numHeader>0) 
	{
		while($rowHeader = mysql_fetch_object($db->Command)) 
		{ ?>
			<li><a href="#"><?php echo $rowHeader->menu_name; ?></a>
                <?php
			$dataSubMenu = array (
				'menu_id' => $rowHeader->menu_id
			);
			$db->selectWhere('tb_pages', $dataSubMenu);
			$numSubMenu=mysql_num_rows($db->Command);
			if($numSubMenu>0)
			{
			?>
                <ul>
                	<?php
				while($rowSubMenu=mysql_fetch_object($db->Command))
				{
				?>
                    	<li><a href="#"><?php echo $rowSubMenu->page_name; ?></a></li>
                    <?php
				}
				?>
                </ul>
                <?php
			}
			?>
                </li>	
		<?php } 
	}
	?>
</ul>

Please help me out...!

So what is the issue here?

Hi, thanks for the reply....

Am not getting the main menu as well as sub menus of main menu....

Have any rows been returned?

Yes, only one row.

In main menu, I have Home, About Us, Contact Us

In child menu, company profile, services as a sub menu for about us.

When I tried with my code, I got only Home menu only. Am not getting remaining main menu.

Please help me out.

Since you are using some sort of database wrapper, I can't see the query. So, the first thing you need to do is figure out what the actual query being run is and make sure everything is okay with that.

How deep can the submenus go? If they can only go 1 level deep, you should probably use a JOIN to select them instead of running queries in a loop. If it can be more than 1 level deep you'll probably want to store it in a hierarchical format. See these two articles for that:

http://www.sitepoint.com/hierarchical-data-database/

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

After looking at this for a little bit, I noticed that you're not using JOINs to select the data. Instead you're querying the DB once for every main category you have.

This is not only an overly complex way of doing it, leading to a lot of unnecessary code, but it's also highly inefficient. Querying a database is quite expensive, so you'll want to keep the number of queries as low as you can. You definitely don't want to have a query inside a loop, if you can prevent it.

I've cleaned up your code a bit, and modified it to use JOINs instead. Not tested, but this should work:

<?php

$db = new testCore ();

// Create query to select all of the data immediately.
$SQL = <<<OutSQL
SELECT m.menu_id, m.menu_name, p.page_name FROM tb_menus AS m
LEFT JOIN tb_pages AS P ON p.menu_id = m.menu_id
OutSQL;

// Assumed that testCore is extending mysqli.
$db->query ($SQL);

$numHeader = mysql_num_rows ($db->Command);

$lastMenu = '';
$menu = "<ul>\n";
if ($numHeader > 0) {
while ($rowHeader = mysql_fetch_object ($db->Command)) {
	// Check if we've changed main categories.
	if ($row->menu_name != $lastMenu) {
		// Close the previous sub menu, unless this is the first main category.
		if (!empty ($lastMenu)) {
			$menu .= "\t\t</ul>\n";
			$menu .= "\t</li>\n";	
		}

		$menu .= "\t<li><a href=\"#\">".$rowHeader->menu_name."</a>";
		$menu .= "\t\t<ul>\n";
	}

	$menu .= "\t\t\t<li><a href=\"#\">".$rowSubMenu->page_name."</a></li>\n";
}
}

$menu .= "</ul>\n";

Also note that I've removed the echos from your code, and used a variable instead. This is so that you can do all of the PHP before you send any content to the browser, something that will make life a lot easier for you in the long run.

Thanks for the reply....!

I got the solution.