Get Average Review Rating from MySQL

[accelerator]

Tweet

Hi All

I have a Reviews table where people can rate products out of 10. I need to calculate the average review rating for a particular product. I think I can do this using the following MySQL query:

$sql4 = "SELECT avg(ReviewRating) FROM Reviews WHERE ReviewIsApproved=1 AND ProductId=" . $ProductId . ";";

is the query right?? If not, how should it be done?

Many thanks

[scriptmonkey]

That looks like it should do exactly what you ask from it, I presume that ReviewIsApproved is a yes/no field?

One thing, I would change the order of the query because, again presuming your ProductId field is indexed, it would be slightly quicker to search based on that first.

[ian]

That looks like it should do exactly what you ask from it, I presume that ReviewIsApproved is a yes/no field?

One thing, I would change the order of the query because, again presuming your ProductId field is indexed, it would be slightly quicker to search based on that first.

Is MYSQL really dumb enough to process a query in the order you specify?

[accelerator]

OK, thanks guys, I have this working now, for those interested there's a code snippet below. The first bit of code sees whether there are approved reviews for a given ProductId, and then if there are, the second bit of the code calculates an average via the SELECT AVG(ReviewRating) MySQL statement.

Rgds

//Find out if there are approved reviews for a product
//Find out how many rows are in the table 
$sql5 = "SELECT COUNT(*) FROM Reviews WHERE ProductId LIKE $ProductId AND ReviewIsApproved=1";
$result5 = mysql_query($sql5, $connection) or trigger_error("SQL", E_USER_ERROR);
$r5 = mysql_fetch_row($result5);
$numrows5 = $r5[0];



//If there are approved reviews, calculate the average review rating
If ($numrows5 > 0) {

//Calculate AverageReviewRating
$sql4 = "SELECT AVG(ReviewRating) FROM Reviews WHERE ProductId LIKE $ProductId AND ReviewIsApproved LIKE 1";
$result4 = mysql_query($sql4);



//obtain
while($row4 = mysql_fetch_array($result4)){
      //mysql_num_rows($result4) always equal to 1 when calculating an average
	  //echo mysql_num_rows($result4);
	  $AverageReviewRating = $row4['AVG(ReviewRating)'];
	  //format to 2 decimal places
	  $AverageReviewRating = number_format($AverageReviewRating, 2);
      echo " Average Rating: " . $AverageReviewRating . " / 10";
      }

//End if there are approved reviews 
}