Re: SELECT AVG(rating)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, 2010-07-02 at 14:32 -0600, Ben Miller wrote:

> Hi - I have a MySQL table full of product reviews and I'm trying to select
> info for only the top 5 rated products.  The only way I can figure out how
> to do it so far is something like:
> 
> $query1 = mysql_query("SELECT * FROM products");
> for($i=1;$i<=mysql_num_rows($query1);$i++) {
> 	$row1 = mysql_fetch_array($query1,MYSQL_ASSOC);
> 	$query2 = mysql_query("SELECT AVG(rating) as rating FROM reviews
> WHERE product_id='" . $row1['product_id'] . "'");
> 	...
> 	$product[$i]['name'] = $row1['product_name'];
> 	$product[$i]['rating'] = $row2['rating'];
> }
> 
> And then use array functions to sort and display only the first 5.
> 
> Is there any easier way to get this done with a single query - something
> like "SELECT AVG(rating) WHERE product_id=DISTINCT(product_id)"? <<= I tried
> that - it didn't work.  Would greatly appreciate any advice.  Thanks,
> 
> Ben 
> 
> 
> 


How about something like this (untested)

SELECT products.product_id, AVG(reviews.rating) AS rating
FROM products
LEFT JOIN reviews ON (reviews.product_id = products.product_id)
GROUP BY products.product_id
ORDER BY rating
LIMIT 1,5

I'm unsure about that order and limit there, so you might have to wrap
that inside of a temporary table query and take your 5 out of that. A
join is the right way to go with this though I reckon.

Thanks,
Ash
http://www.ashleysheridan.co.uk



[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux