Re: [PHP] SELECT AVG(rating)

2010-07-02 Thread Ashley Sheridan 
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

RE: [PHP] SELECT AVG(rating)

2010-07-02 Thread Ben Miller 


-Original Message-
From: Ashley Sheridan [mailto:a...@ashleysheridan.co.uk] 
Sent: Friday, July 02, 2010 2:43 PM
To: b...@tottd.com
Cc: php-general@lists.php.net
Subject: Re: [PHP] SELECT AVG(rating)

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




Adding a DESC after ORDER BY rating did it perfectly.  I had tried a few
JOIN queries, but was building them incorrectly.  Thank you so much for your
help.

Ben



-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php