Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Jan 2012
    Posts
    32
    Thanks
    0
    Thanked 1 Time in 1 Post

    mysql_fetch_assoc fails on second run

    For some reason I can not get the second menu to show its sub menus.

    The main categories show their names and the first category shows its sub menus.

    What am I doing wrong that causes the menus to not show up in the second list.

    Code:
    $sql = "SELECT * FROM `cats` ORDER BY `catName` ASC";	$getCats = mysql_query($sql);
    $sql = "SELECT * FROM `catSub` ORDER BY `subName` ASC";	$getSubCats = mysql_query($sql);
    
    while($cat = mysql_fetch_assoc($getCats)) {
    ?>
    <ul class="catMenuItem">
    	<li><a href="#" class="titleTriangle"><span><? echo($cat['catName']); ?></span></a></li>
    		<li class="catSubMenu">
    		<? while($sub = mysql_fetch_assoc($getSubCats)) {
    		echo("<br>:".$sub['subName'].":<br>");
    				if ($sub['catID'] == $cat['id']) {
    				echo($sub['subName']);
    				?><br><?
    				}
    		}
    		//unset($sub); ?>
    		<br>
    		</li>
    </ul>
    <?
    }

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Don't do this. The nested loop is always of full table record size, and you will have to iterate it to completion for every iteration of the external loop since you cannot determine if a match on catID exists prior to the iteration. Join the tables together, and for simplicity dump into an array and iterate that instead.
    PHP Code:
    <?php
    //...
    $sQry 'SELECT c.id, c.catName, s.subName
                FROM cats c
                LEFT JOIN subcats s ON (s.catID = c.id)
                ORDER BY c.catName ASC, s.subName ASC'
    ;
    if (
    $qry mysql_query($sQry))
    {
        
    $aResult = array();
        while (
    $row mysql_fetch_assoc($qry))
        {
            if (!isset(
    $aResult[$row['catName']]))
            {
                
    $aResult[$row['catName']] = array();
            }
            
    $aResult[$row['catName']][] = $row;
        }
        
    mysql_free_result($qry);
        foreach (
    $aResult AS $cat => $subs)
        {
    ?>
    <ul class="catMenuItem">
        <li><a href="#" class="titleTriangle"><span><?php echo $cat?></span></a></li>
            <li class="catSubMenu">
                    <?php
                    
    foreach ($subs AS $item)
                    {
                        
    printf('<br />:%s<br />%s<br />'$item['subName'], $item['subName']);
                    }
                    unset(
    $item);
    ?>
            <br>
            </li>
    </ul>
    <?php
        
    }
        unset(
    $cat$subs);
    }
    Something like this. This is a simple construction and is only good for 2 levels. If you want dynamic levels of unknown size, you'll need to use either recursion, some tricky stack popping techniques, or object oriented using a tree or graph style datastructure.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •