I'm trying to create a rating system, where the user can rate pictures/videos/audio etc. I have two tables at the moment
Table: products
Cols: product_id[PK] | name | category | type | link
This is the products table and contains information about the products. If you're confused by products, think of the "product" as the image/video/audio, I named it like this simply for allowing me to understand it easier. The second table is the ratings
Table: product_ratings
Cols: rating_id[PK] | rating | product_id | timestamp
This table stores information about the rating the user has given.
I want a page where it will display the highest rating (on average) for all "products". So far, I've looked through SA, and found the follow piece of code:
SELECT
p.product_id, p.name,
AVG(pr.rating) AS rating_average
FROM products p
INNER JOIN product_ratings pr
ON pr.product_id = p.product_id
WHERE p.product_id = 1
This just returns the average rating for a specific product_id, How would I go about getting ALL the product_ids and their average rating, and how would I find the highest one via PHP?
I've tried:
WHERE p.product_id < 1 AND p.product_id < 30
But this just returns product_id of 2, with it's name and average_rating, which I don't understand.
Guidance/links to material are welcome
SELECT
p.product_id,
p.name,
AVG(pr.rating) AS rating_average
FROM products p
INNER JOIN product_ratings pr
ON pr.product_id = p.product_id
GROUP BY p.product_id
ORDER BY rating_average DESC
LIMIT 1