Php spry menu problem

[stualk]

Hi folks,

 

I've got a spry/drop-down menu on my website but it's not quite working how I want it to. In the db table are a range of products that fall into certain categories and then sub-categories. Here's a small selection of items in the table as an example:

 

SECTIONSUB SECTION

BedsSingle Beds

BedsDouble Beds

BedsKing Size Beds

 

The way the spry/drop-down menu is meant to work is that it selects a unique/distinct value from the 'section' column and this becomes a drop-down link. Then, it displays all the sub sections specific to that section beneath in the drop-down, like this:

 

Beds

- Single Beds

- Double Beds

- King Size Beds

 

At the minute it's doing this, one beside another:

 

Beds

- Single Beds

 

Beds

- Double Beds

 

etc

 

I'm almost there but how can I group them all into one 'beds' drop-down? A foreach loop perhaps? Here's my code:

 

<ul id='MenuBar2' class='MenuBarHorizontal'>

<?php

$NumRows=mysql_query("select DISTINCT section,sub_section from products where visible = 'Yes'");
$NumRows=mysql_num_rows($NumRows);
$Result = mysql_query("select DISTINCT section,sub_section from products where visible = 'Yes'") or die("Error 1");
$i=1;
    while($row = mysql_fetch_array($Result)){

   echo "<li><b><a class='MenuBarItemSubmenu' href='products.php'>$row[section]</a></b><ul>
   			<li><a href='products_second.php?section=$row[sub_section]'><b><i>$row[sub_section]</i></b></a>";

echo "</li></ul>";
}

?>
</li>
</ul>

 

[leehanken]

Load into an array then use a nested loop.

while($row = mysql_fetch_array($Result))
{	
$sections[$row[section]][] = $row[subsection];
}

foreach ($sections as $section => $subsections)
{
echo "<ul><li>$section</li><ul>";
foreach ($subsections as $subsection)
{
	echo "<li>$subsection</li>";
}
echo "</ul></ul>";
}

 

[stualk]

Great advice Lee. Much appreciated. I had to adjust it slightly to work how I wanted it to but the theory behind it was spot on so many thanks. Here's the final code that I arrived at:

 

<ul id='MenuBar2' class='MenuBarHorizontal'>
<?php

$NumRows=mysql_query("select DISTINCT section,sub_section from products where visible = 'Yes'");
$NumRows=mysql_num_rows($NumRows);
$Result = mysql_query("select DISTINCT section,sub_section from products where visible = 'Yes'") or die("Error 1");
$i=1;
while($row = mysql_fetch_array($Result))
{	
$sections[$row[section]][] = $row[sub_section];
}

echo "";

foreach ($sections as $section => $subsections)
{
echo "<li><a class='MenuBarItemSubmenu' href='#'>$section</a><ul>";
foreach ($subsections as $sub_section)
{
	echo "<li><a class='MenuBarItemSubmenu' href='products_second.php?section=$sub_section'>$sub_section</a></li>";
}
echo "</ul>";
}
?></li></ul>

 

[leehanken]

Glad to be of help.