Counting the number of items in a category in a While loop

[andrew_biggart]

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>

 

[andrew_biggart]

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];
			   }

 

[Ken2k7]

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;

[andrew_biggart]

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

[andrew_biggart]

Ok its working now thanks for your help!

[Ken2k7]

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.