[RESOLVED] Counting number of products in category

[MissJane]

Hi,

I have no idea where to start with this..I've googled like crazy, but I'm just over whelmed and confused.

I want to show how many Products are in each Category. For example: 'Accessories (12)'

I have 2 tables with the following attributes:

PRODUCTS
p_id
p_name
cat_id

CATEGORY
cat_id
cat_name

I think I have to GROUP BY and COUNT somehow...

Please help! I'm going crazy!:queasy:

[NogDog]

I think you can do it something like this:

SELECT COUNT(*) AS `total`, c.cat_name
FROM `category` AS c 
INNER JOIN `products` AS p ON p.cat_id = c.cat_id
GROUP BY c.cat_name

[MissJane]

Sorry, I'm very new at this, but how do I echo the results?

[NogDog]

The specifics would depend on which database interface you are using. Whichever it is, you would have to execute the query, the loop through the result rows and echo the results. If using the basic MySQL extension, the [man]mysql_fetch_assoc[/man] manual page gives a fairly thorough example of doing a select query and echoing the results.

[MissJane]

Thanks!! I got it working...but it's skipping the categories that do not have any products in them. How can I include these categories and just have 'Category Name (0)' for the categories with zero products in them?