pulling menu and submenu from database

[lional]

Hi I am trying to write a nave bar where the sub menu and sub sub menu is pulled from mysql tables.

It works but only pulls the first sub menu and does not roll to the second

 

Here is my code

$query4 = "SELECT * from categories WHERE active = '1'"; // display the products in each subcat
        $result4 = mysql_query($query4, $conn);
        while ($row4 = mysql_fetch_assoc($result4)){
        $cat_id_out = $row4["cat_id"];
        $category_out = $row4["category"];
        
       
               
    echo '<li><a href=""><span>' . $category_out . '</span></a><ul>';
    
    $query4 = "SELECT * from subcats WHERE cat_id = '$cat_id_out' AND active = '1'"; // display the products in each subcat
        $result4 = mysql_query($query4, $conn);
        while ($row4 = mysql_fetch_assoc($result4)){
        $subcat_id_out = $row4["subcat_id"];
        $subcategory_out = $row4["subcat"];
        
       
               
    echo '<li><a href="products.php?subcat=' . $subcat_id_out . '"><span>' . $subcategory_out . '</span></a></li>';
    
        }
    echo '</ul></li></ul></li>';
        }

Thanks in advance

[mac_gyver]

the problem is you are reusing your variables and overwriting them. but, you should never find yourself running queries inside of loops or of running/processing data from more than one query at one time. your $queryx, $resultx, $rowx variables are an indicator that you are using TOO much code to accomplish a task.

 

you need to run ONE query using a JOIN that gets the rows you want in the order that you want them. the following (untested) should work (i'm not sure i got all the ul/li tags the way you need them to be) -

$query = "SELECT c.category,s.subcat_id,s.subcat FROM categories c JOIN subcats s ON c.cat_id = s.cat_id AND c.active = 1 AND s.active = 1 ORDER BY c.category,s.subcat";
$result = mysql_query($query, $conn);
$last_heading = null; // remember the heading (category) to detect when it changes, start with a null value
while($row = mysql_fetch_assoc($result)){
    $category = $row['category']; // save a little typing
    if($last_heading!= $category){ // detect if the heading changed
        // heading changed, test if it is not the first heading
        if($last_heading != null){
            // not the first one, close out the previous section here....
            echo "</ul></li>";
        }
        $last_heading = $category; // remember the new heading
        // output the new heading here...
        echo "<li><a href=''><span>$category</span></a><ul>";
    }
    // output the data under each heading here...
    echo "<li><a href='products.php?subcat={$row['subcat_id']}'><span>{$row['subcat']}</span></a></li>";
}

// close out the final section here...
echo "</ul></li></ul>";

Edited August 14, 2013 by mac_gyver