Left Join only returning one row
- by Adam
I am trying to join two tables. I would like all the columns from the product_category table (there are a total of 6 now) and count the number of products, CatCount, that are in each category from the products_has_product_category table. My query result is 1 row with the first category and a total count of 68, when I am looking for 6 rows with each individual category's count.
<?php
$result = mysql_query("
SELECT a.*, COUNT(b.category_id) AS CatCount
FROM `product_category` a
LEFT JOIN `products_has_product_category` b
ON a.product_category_id = b.category_id
");
while($row = mysql_fetch_array($result)) {
echo '
<li class="ui-shadow" data-count-theme="d">
<a href="' . $row['product_category_ref_page'] . '.php" data-icon="arrow-r" data-iconpos="right">' . $row['product_category_name'] . '</a><span class="ui-li-count">' . $row['CatCount'] . '</span></li>';
}
?>
I have been working on this for a couple of hours and would really appreciate any help on what I am doing wrong.