Admittedly, this is not a direct coding of your solution, but should give some ideas.
Simplest (not neccessarily most efficient) way to do this is to use nested querys and outputs something along these Pseudo lines:
$query to select categories;
while not end of category rowset
$subquery where subquery belongs to category
whilte not end of sub category row set
print current subcategory
increment subcategory row counter
Increment category row counter
I have code along these lines:
PHP Code:
function dynamicWriteNav($category) {
//SELECT CATEGORIES AND SUB CATEGORIES
$query = ("SELECT DISTINCT c.categoryid as catid,categoryname, sc.categoryid as subcat_catid FROM Category c LEFT JOIN SubCategory sc on c.categoryid = sc.categoryid ORDER BY c.categoryid");
$result = mysql_query($query);
$data = mysql_fetch_assoc($result);
//LOOP THROUGH CATEGORIES
while ($data[catid] != 0) {
if($data[catid] == $category) {
print("<li class=\"nav\"><a href=\"index.php?punchline=" . $data[catid] . "\" class=\"navselected\">" . $data[categoryname] . "</a></li>\n");
//IF IT'S GOT SUB CATEGORIES, DISPLAY THEM
if($data[subcat_catid] = $data[catid]) {
$subquery = ("SELECT subcategoryid, subcategoryname FROM SubCategory WHERE categoryid = " . $data[catid] . " ORDER BY subcategoryname;");
$subresult = mysql_query($subquery);
$subdata = mysql_fetch_assoc($subresult);
print("<ul class=\"littlenav\">\n");
while ($subdata[subcategoryid] != 0) {
print("<li class=\"littlenav\"><a href=\"index.php?punchline=" . $data[catid] . "&topic=" . $subdata[subcategoryid] . "\" class=\"littlenav\">" . $subdata[subcategoryname] . "</a></li>\n");
$subdata = mysql_fetch_assoc($subresult);
}
print("</ul>\n");
}
}
else
//ELSE DISPLAY THE NORMAL NAV ITEM
{
print("<li class=\"nav\"><a href=\"index.php?punchline=" . $data[catid] . "\" class=\"nav\">" . $data[categoryname] . "</a></li>\n");
}
$data = mysql_fetch_assoc($result);
}
}
There is a little more complexity than you may need here as i test for selected nav item and display a different class based on this.
I use it to display the navigation in http://www.1liners.co.uk/test/index.php?punchline=1 and it seems to work fine.
In my example, the cats only display sub cats if they are selected, hence this line :
PHP Code:
if($data[catid] == $category) {
You could make it simpler by removing some of this,
Not the most efficient though, not optimised yet, just testing functionality
Hope it helps