...

View Full Version : mysql_fetch_assoc fails on second run



FreelanceCoder
04-22-2012, 11:32 AM
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.



$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>
<?
}

Fou-Lu
04-22-2012, 07:39 PM
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
//...
$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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum