andrew_biggart Posted April 16, 2010 Share Posted April 16, 2010 Basically what I am trying to do is display all Categories that my shop has which are listed in the Category table. I can Get this section working fine. But then when I try and Count the number of items each category has I run into problems. Im trying to count the number of items each category has by the Item_id. At the moment I am getting a syntax error. Can anyone Help please? <table class="shop_left"> <tr> <td class="shop_left_ho">Categories</td> </tr> <?php ini_set("display_errors", "1"); error_reporting(E_ALL); include("config.php"); // Retrieve data from database $sql="SELECT * FROM biggartfp9_shop_categories ORDER BY Cat_id ASC " ; $result=mysql_query($sql); // Start looping rows in mysql database. while($rows=mysql_fetch_array($result)){ $Category = $rows['Category']; // Retrieve data from database $sql2 = "SELECT count(Item_id) as itemcount FROM biggartfp9_shop_itemst WHERE Category='$Category' "; $result2 = mysql_query($sql2); $rows2 = mysql_fetch_assoc($result2); if($rows2['itemcount'] != 0) { $Catnum = $rows2['itemcount']; } else { $Catnum == 0]; } ?> <tr> <td class="shop_left_subh"> <a href="shop_categories.php?cat_id=<? echo $rows['Category']; ?>" class="shop_filter"><?php echo $rows['Category']; ?> <?php echo $Catnum; ?> </a> </td> </tr> <? // close while loop } // close connection mysql_close(); ?> </table> Link to comment https://forums.phpfreaks.com/topic/198765-counting-the-number-of-items-in-a-category-in-a-while-loop/ Share on other sites More sharing options...
andrew_biggart Posted April 16, 2010 Author Share Posted April 16, 2010 The error is in this part of the code, but I cant seem to figure out why ?? $Category = $rows['Category']; // Retrieve data from database $sql2 = "SELECT count(Item_id) as itemcount FROM biggartfp9_shop_itemst WHERE Category='$Category' "; $result2 = mysql_query($sql2); $rows2 = mysql_fetch_assoc($result2); if($rows2['itemcount'] != 0) { $Catnum = $rows2['itemcount']; } else { $Catnum == 0]; } Link to comment https://forums.phpfreaks.com/topic/198765-counting-the-number-of-items-in-a-category-in-a-while-loop/#findComment-1043173 Share on other sites More sharing options...
Ken2k7 Posted April 16, 2010 Share Posted April 16, 2010 You have a ] in your else clause. I think this SQL will do the same. Just less coding on your part. Not tested, but I think it should work. SELECT COUNT(*) cnt FROM biggartfp9-shop_categories c INNER JOIN biggartfp9-shop_itemst i ON (i.Category = c.Category) GROUP BY i.Category ORDER BY c.Category; Link to comment https://forums.phpfreaks.com/topic/198765-counting-the-number-of-items-in-a-category-in-a-while-loop/#findComment-1043179 Share on other sites More sharing options...
andrew_biggart Posted April 16, 2010 Author Share Posted April 16, 2010 Right that's got rid of the syntax error, just needed a fresh pair of eyes as I couldn't spot it. Now it is showing beside every category that it has one item in it even if that category contains no items ? Ken I do not understand what you want me to replace your new code with? Thankyou Link to comment https://forums.phpfreaks.com/topic/198765-counting-the-number-of-items-in-a-category-in-a-while-loop/#findComment-1043190 Share on other sites More sharing options...
andrew_biggart Posted April 16, 2010 Author Share Posted April 16, 2010 Ok its working now thanks for your help! Link to comment https://forums.phpfreaks.com/topic/198765-counting-the-number-of-items-in-a-category-in-a-while-loop/#findComment-1043193 Share on other sites More sharing options...
Ken2k7 Posted April 16, 2010 Share Posted April 16, 2010 I was just throwing out a SQL that could reduce your 2 SQLs into 1. I haven't tested it, but see if it works. If so, you won't have to do all that coding. Link to comment https://forums.phpfreaks.com/topic/198765-counting-the-number-of-items-in-a-category-in-a-while-loop/#findComment-1043195 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.