[RESOLVED] Count product in categories

[ade234uk]

I am trying to display the categories and the number of items within the category. When a product is active b.deleted=0 and if a user deletes a product, b.deleted=1

The reason I set b.deleted=1 when a user deletes the product, is so that I can create an archive later on.

The query that I am using below is displaying the category name and number of items in that category correctly. It is counting all products regardless of b.deleted being 0 or 1

However I need to work so that it only counts the number of products when b.deleted = 0.

The problem is if I put a WHERE b.deleted=0 in the query, it then only displays the category names that have products

What I need it to do is display all the categories even if that category has 0 products in it.

I hope this makes some sort of sense.

<?php
// DB CONNECTION INFO

$result = mysql_query("SELECT a.user_id, a.id, a.inventory_category_name, b.deleted, COUNT(b.category) as number_of_items
FROM user_inventory_categories a
LEFT JOIN product_inventory b ON a.id=b.category
WHERE a.user_id=". $_SESSION['user_id'] ." GROUP BY a.id");

while ( $row = mysql_fetch_array($result) )
{
echo $row['inventory_category_name'];
echo $row['number_of_items'];
}
?>

[bradgrafelman]

This page pretty much explains the magic behind it, but try doing something like:

SUM(IF(<condition you want here>, 1, 0))

instead of a COUNT().

[ade234uk]

Learn something new everyday. I will try this tommorow and let you know. Thanks for heads up.

[ade234uk]

bradgrafelman;10988980 wrote:

This page pretty much explains the magic behind it, but try doing something like:

SUM(IF(<condition you want here>, 1, 0))

instead of a COUNT().

Amazing I was stuck on this for about 5 hours yesterday. Used the following
SUM(IF(b.deleted='0',1,0)) as number_of_items and it works a treat. Thank you very much.