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> Quote Link to comment 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]; } Quote Link to comment 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; Quote Link to comment 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 Quote Link to comment 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! Quote Link to comment 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.