[RESOLVED] Displaying all the results of query

[MissJane]

Hi,

I have a query that grabs info from 2 tables - Categories & Products. The query shows how many products are in each category - but only gives me the results of the Categories that have products in them. However, I want to display all Categories regardless if they have no products - so I want it to display either:

• Category Name (20)

• Category Name (0)

The following is my code:

$sql = "SELECT COUNT(*) AS `total`, c.cat_name FROM `category` AS c INNER JOIN `parts` AS p ON p.cat_id = c.cat_id GROUP BY c.cat_name;";

$result = mysql_query($sql);

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

while ($row = mysql_fetch_array($result)) {
printf('<p>%s (%s)', $row["cat_name"], $row["total"]);
}

Please help!

[Bjom]

LEFT JOIN

$sql = "SELECT COUNT(*) AS `total`, c.cat_name FROM `category` AS c LEFT JOIN `parts` AS p ON p.cat_id = c.cat_id GROUP BY c.cat_name;"; 

$result = mysql_query($sql); 

if (!$result) { 
    echo "Could not successfully run query ($sql) from DB: " . mysql_error(); 
    exit; 
} 

while ($row = mysql_fetch_array($result)) { 
printf('<p>%s (%s)', $row["cat_name"], $row["total"]); 
} 

[MissJane]

Excellent! Thanks so much...changing it to LEFT JOIN worked but it was saying that categories with 0 products in them had 1 product. So I changed what I was counting from (*) to (parts_id) and it works like a dream.

The following is my complete query:.

$sql = "SELECT COUNT(parts_id) AS `total`, c.cat_name FROM `category` AS c LEFT JOIN `parts` AS p ON p.cat_id = c.cat_id GROUP BY c.cat_name;";

Cheers!