Left Join only returning one row
Posted
by
Adam
on Stack Overflow
See other posts from Stack Overflow
or by Adam
Published on 2012-10-16T22:16:19Z
Indexed on
2012/10/16
23:00 UTC
Read the original article
Hit count: 140
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.
© Stack Overflow or respective owner