mikenl Posted April 22, 2007 Share Posted April 22, 2007 Hey, I cannot figure out how to make this loop work less db intensive, does anyone know howto do this? function shop_menu(){ dbsystem(); $query = " SELECT category.cat, category.un_id, category.title, category.text FROM category GROUP BY category.un_id "; $query = mysql_query($query); $menu = array(); while ($row = mysql_fetch_row($query)) { $menu[] = $row; } $counter = 0; $x = count($menu); for($i = 0; $i < $x; $i++) { if(!empty($menu[$counter])) { ?> <table width="170" border="0" cellpadding="0" cellspacing="0"> <tr> <td height="20" class="menue"><a href="../sortiment/?cat=<? echo $menu[$counter][1] ?>" class="textgreenbold"><? echo $menu[$counter][0] ?></a></td> </tr> </table> <table width="170" border="0" cellspacing="0" cellpadding="0"> <? dbsystem(); $query="SELECT sub,un_id FROM subcategory WHERE cat = ".$menu[$counter][1].""; $result = mysql_query($query); while($row = mysql_fetch_array($result)){ $cat = $row[0]; $id = $row[1]; ?> <tr> <td width="10"> </td> <td width="160" class="menue"><a href="../sortiment/?sub=<? echo $id ?>" class="men"><? echo $cat ?></a></td> </tr> <? } ?> </table> <? } $counter++; } } ?> Link to comment https://forums.phpfreaks.com/topic/48139-phpmysql-loop/ Share on other sites More sharing options...
Psycho Posted April 22, 2007 Share Posted April 22, 2007 First off, there are some problems with the looping constructs in the code above. First it creates a $counter, then it usees a for loop with an $i counter. Why both? In any event you should have used neither. A foreach loop is a better construct for arrays. And, to go even further there is no need to dump the db results into an array and then loop through the array. You would only need to do that if you need to reuse the results from the db query several times. Also, you should always use <?php not <?. Last critique - you should never used nested queries - that is what is making the script db intensive. You can get all the info you need with one query. Anyway, this will do the same thing more efficiently. I'm sure there are some typos as I don't have your data to test against. <?php function shop_menu(){ dbsystem(); // One query grabing all the pertinent data $query = " SELECT category.cat, category.un_id as cat_id, category.title, category.text, subcategory.sub, subcategory.un_id as subcat_id FROM category, subcategory WHERE category.un_id = subcategory.cat ORDER BY category.un_id"; $query = mysql_query($query); $current_catid = ''; while ($row = mysql_fetch_assoc($query)) { if ($current_catid!=$row['un_id']) { // Close previous subcat table if not 1st time through if ($current_catid != '') { echo '</table>'; } //First entry for this category - show category header $current_catid=$row['un_id']; echo ' <table width="170" border="0" cellpadding="0" cellspacing="0"> <tr>\n"; <td height="20" class="menue"><a href="../sortiment/?cat='.$row['cat_id'].' class="textgreenbold">'.$row['cat'].'</a></td> </tr> </table> <table width="170" border="0" cellspacing="0" cellpadding="0">'; } // End if //Display the subcat data echo ' <tr> <td width="10"> </td> <td width="160" class="menue"><a href="../sortiment/?sub='.$row['subcat_id'].'" class="men">'.$row['sub'].'</a></td> </tr>'; } //End while loop echo '</table>'; } ?> Link to comment https://forums.phpfreaks.com/topic/48139-phpmysql-loop/#findComment-235274 Share on other sites More sharing options...
mikenl Posted April 22, 2007 Author Share Posted April 22, 2007 First of all, thx for assisting I did the nested looping because there are main categories and subcategories and the subcategories must be listed as a submenu under each main category, like: Main1 sub1 sub2 sub3 Main 2 sub 1 sub 2 This code lists all subcategories as one list, without the main categories. Link to comment https://forums.phpfreaks.com/topic/48139-phpmysql-loop/#findComment-235322 Share on other sites More sharing options...
Psycho Posted April 23, 2007 Share Posted April 23, 2007 Again, you do not need nested queries. The code I posted was designed to do exactly what you asked for. As I said, I can't test it since I don't have your database. Did you even try it? Link to comment https://forums.phpfreaks.com/topic/48139-phpmysql-loop/#findComment-235607 Share on other sites More sharing options...
mikenl Posted April 23, 2007 Author Share Posted April 23, 2007 Yes I tried and it resulted in a list with all subcategories, no categories. I tried changing the code but I didn't manage to make it work Link to comment https://forums.phpfreaks.com/topic/48139-phpmysql-loop/#findComment-235773 Share on other sites More sharing options...
mikenl Posted April 23, 2007 Author Share Posted April 23, 2007 UPDATE: Played with it again and then noticed $row['un_id']. When I replaced this with $row['cat_id'] all worked! THX! Link to comment https://forums.phpfreaks.com/topic/48139-phpmysql-loop/#findComment-235784 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.