[Resolved] Geting totals

[ldtiw]

Hey there,

i have three tables. One with cats, one with products and another with product reviews.

I need to get the total number of reviews for each product once a category is selected and the next page has loaded.

Here is the code i have tried using and cant get it to work. it says that all 3 products have a review when in testing i added only one for one product:

SELECT product.prod_id, product.r_cat_id, product.item, product.price, product.madeby, product.link, COUNT(product_review.r_cat_id) as total
FROM product LEFT JOIN product_review ON product.r_cat_id = product_review.r_cat_id
WHERE product.r_cat_id = '$r_cat_id'
GROUP BY product.prod_id

Im still failry new to php and this snippet is what ive adapted from help someone else gave me here a while ago for another project.

Any help would be great.

Thanks,
Danny

[uffis]

First, a note concerning using numeric values in SQL. Numeric values are used without single quotes. MySQL and some other DBMSes may accept quotes, but that isn't standard and portable SQL, IIRC.

Shouldn't you be joining the tables product and product_review on the product id column instead of category id?

Also, you might want to count the occurrences of the product id in the product_review table and not the category id. I think you don't even need to select the category as you already know it because you have chosen the products belonging to it.

Which DBMS are you using? Usually the group by clause must have the columns chosen in the select clause except the ones that are the result of an aggregate function (count, sum, max etc.).

Try something like this first, and then add columns you need for the final query (I used table aliases as it makes the code usually much shorter and clearer, IMHO):

SELECT P.prod_id, COUNT(PR.r_prod_id) AS total
FROM product P 
  LEFT JOIN product_review PR ON P.prod_id = PR.r_prod_id
WHERE P.r_cat_id = '$r_cat_id'
GROUP BY P.prod_id