Problem generating nested unordered lists

[woe2you]

I'm trying to write a dynamically-generated product menu where the categories and subcategories only appear if they contain subcategories and products, marked up as nested unordered lists. My problem is that the following code:

$query = "SELECT distinct categories.category_id, categories.category_name, subcategories.subcategory_id, subcategories.subcategory_name, products.subcategory_id products.stock
FROM categories
LEFT JOIN subcategories ON categories.category_id = subcategories.category_id
LEFT JOIN products ON subcategories.subcategory_id = products.subcategory_id
WHERE products.subcategory_id IS NOT NULL AND products.stock > 0
ORDER BY categories.category_id";
$result = mysql_query($query);

echo "<ul class=\"menu\">";
while($row = mysql_fetch_assoc($result))
	{
	echo "<li><a href=\"$php_self?category=$row[category_id]\">$row[category_name]</a>";

	echo "<ul class=\"menu\">";
	echo "<li><a href=\"$php_self?category=$row[category_id]&subcategory=$row[subcategory_id]\">$row[subcategory_name]</a></li>";
	echo "</ul>";


echo "</li>";
}

echo "</ul>";

is producing the following markup:

<ul class="menu">
<li><a href="?category=1">Hard Drives</a>
<ul class="menu">
<li><a href="?category=1&subcategory=2">IDE</a></li>
</ul>
</li>
<li><a href="?category=3">Motherboards</a>
<ul class="menu">
<li><a href="?category=3&subcategory=3">Socket A</a></li>
</ul>
</li>
<li><a href="?category=3">Motherboards</a>
<ul class="menu">
<li><a href="?category=3&subcategory=4">Slot 1</a></li>
</ul>
</li>
</ul>

where the "Socket A" and "Slot 1" are supposed to be within the same <ul> nested within the "Motherboards" <li>. Call me dense, but I can't seem to figure out where I'm going wrong.

[eoghain]

It's because that is what you have programmed.

You are looping over every row that is returned from your query and outputting both the category and the subcategory.

Here is what you want to do:

$categoryID = "";
$endlist = "";
echo "<ul class=\"menu\">"; 
while($row = mysql_fetch_assoc($result)) 
    { 
    if ($categoryID != $row[category_id]) {
        $categoryID = $row[category_id]
        echo $endlist; 
        echo "<li><a href=\"$php_self?category=$row[category_id]\">$row[category_name]</a>"; 
        $endlist = "<\li>";
    }

    echo "<ul class=\"menu\">"; 
    echo "<li><a href=\"$php_self?category=$row[category_id]&subcategory=$row[subcategory_id]\">$row[subcategory_name]</a></li>"; 
    echo "</ul>"; 
} 

echo "</ul>";

This will only print out each unique Category and thus all sub categories will appear correctly.

[woe2you]

Cheers, that's great. I think I see where I was being thick now.