PHP/MySQL menu issues

[henryhenry]

I'm trying to make php do my menu from a MySQL DB. The menu has three levels. eg...-product

-product 1

-product 1.1 -product 1.2

-product 2

-product 2.1 -product 2.2

-anotherproduct

-another 1

- another 1.1

etcThe problem is I'm no php expert. I have got the queries good so that I can get php to show just product 1.1, 1.2 or just product 1, product 2...The problem comes with getting the product 2.1, 2.2 to sit under product 2. I suspect there is a simple thing that can be done but I'm not sure...Here is my (no doubt shameful) attempt so far:

$query1000 = mysql_query("SELECT * FROM browse WHERE level='1'");$query2100 = mysql_query("SELECT * FROM browse WHERE level='2' AND class='1'");$query3110 = mysql_query("SELECT * FROM browse WHERE level='3' AND class='1' AND part='1'");$query3120 = mysql_query("SELECT * FROM browse WHERE level='3' AND class='1' AND part='2'");$query3130 = mysql_query("SELECT * FROM browse WHERE level='3' AND class='1' AND part='3'");$query2200 = mysql_query("SELECT * FROM browse WHERE level='2' AND class='2'");$query2300 = mysql_query("SELECT * FROM browse WHERE level='2' AND class='3'");		while($row=mysql_fetch_array($query1000))//Product		  	  {		  	  	echo "<ul>";		  	  	echo "<li><a href='" . $row['href'] . "' title='" . $row["title"] . "'>" . $row['label'] . "</a>";		  	  	while($row=mysql_fetch_array($query2100))//Product 1,2,3		  	  	  {		  	  	  	echo "<ul>";		  	  	  	echo "<li>"		  	  	  	echo "<a href='" . $row['href'] . "' title='" . $row["title"] . "'>" . $row['label'] . "</a>";		  	  	  	while($row=mysql_fetch_array($query3110))//Product 1.1		  	  	  	  {		  	  	  	  	echo "<ul>"; 			  	  	  	echo "<li><a href='" . $row['href'] . "' title='" . $row["title"] . "'>" . $row['label'] . "</a></li>"; 			  	  	  	echo "</ul>"; 			  	  	   } 			  	  	echo "</li>"; 			  	  	while($row=mysql_fetch_array($query3120))//Product 1.2		  	  	  	  {		  	  	  	  	echo "<ul>"; 			  	  	  	echo "<li><a href='" . $row['href'] . "' title='" . $row["title"] . "'>" . $row['label'] . "</a></li>"; 			  	  	  	echo "</ul>"; 			  	  	   }		  	  		echo "</li>";		  	  		echo "</ul>";		  	  	  }		  	  	echo "</li>";		  	  	echo "</ul>";		  	  }

So in that piece of code, the output is something like:-product

-product 1

-product 1.1 -product 1.2 -product 2.1 -product 2.2

-product 2 -product 3

But I want-product

-product 1

-product 1.1 -product 1.2

-product 2

-product 2.1 -product 2.2

Is it possible?

[justsomeguy]

It looks like you would need another column to associate a submenu item with its parent. Most setups like these have a database like this:

table itemsid	title   1	 item 12	 item 23	 item 34	 item 1.15	 item 1.26	 item 2.17	 item 2.1.1table childrenparent   child1		41		52		66		7

You can see that item 1 is the parent of items 4 and 5, item 2 is the parent of item 6, and item 6 is the parent of item 7. Then you just write the code to check if the current item has any children, and display the children before moving on to the next item.

[Mr_CHISOL]

well, I think that's complex way of doing it.The simplest way is to just have column with the parent-ID, in this you only has one table to keep track on.This is just an example of how the table may look:

id   title		   href			 parent1	Main		  main.php   02	News		 news.php  03	Old News  main.php  2

And this is how the code that displays the menu could look like

$result = mysql_query("SELECT * FROM browse WHERE parent=0"); // Note the use of parent=0, this is the same as level=1// Use a function to display the menu$menuHTML = GetMenu( $result, 0 );  // 0 is the id of the parent// The functionfunction GetMenu( $items, $parent, $indent = "  " ){	  $html = $indent . "<ul>\n";	  while ($row = mysql_fetch_array($items)) {			$html .= $indent . "	<li>\n";			$html .= $indent . '		<a href="' . $row['href'] . '" title="' . $row['title'] . '">' . $row['label'] . "</a>\n";			// Check for childrens			if ($result = mysql_query( "SELECT * FROM browse WHERE parent=".$row['id'])) {					  // Call this function recursively to get the childs					 $html .= GetMenu( $result, $row['id'], $indent.'		' );			}			$html .= $indent . "	  </li>\n";	  }	  $html .= $indent . "</ul>\n";	  return $html;}

That should work fine...An importen thing when programming is flexibility, you don't want rewrite alot of code as soon as you make a change (i.e, more levels to the menu)The recursive function can write out as many levels that you would like without any complex code or changes..,.Good Luck and Don't panic

[henryhenry]

Thanks very much to both suggestions! I've sort of worked out more closely what I'm actually trying to do from your posts and getting there gradually!! Here is a link for anyone else that has this issue http://www.sitepoint.com/article/hierarchical-data-database