SELECT AVG(rating)
in [General]
|
Prev: CFP for Surge Scalability Conference 2010
Next: What's wrong in this function? Does not work for me.
From: "Ben Miller" on 2 Jul 2010 16:32 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
From: Ashley Sheridan on 2 Jul 2010 16:43 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
From: "Ben Miller" on 2 Jul 2010 17:13
-----Original Message----- From: Ashley Sheridan [mailto:ash(a)ashleysheridan.co.uk] Sent: Friday, July 02, 2010 2:43 PM To: ben(a)tottd.com Cc: php-general(a)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 |