Average Rating script

Question

I have asked this once before but i didnt get a very clear answer.

I need to know how to make a rating script for a site. I have a form that submits a rating out of ten to mysql. How would you get the average rating to be displayed from the mysql column using php?

One person suggested having two tables; one for all the ratings, and one for the average rating of each page. Is there a simpler method than this?

Answer 1

If you collect ratings for a single feature lets say FeatureX and place in a column lets say FCOLX then you can:

1. Use the built in Mysql AVG() function
2. Fetch all values and calculate the average in the php script.

Calculating an average usually goes like this:

$myRatings = $dbHandler->fetch(); //generic fetch
$totalRatings = count($myRatings);
$sum = 0;
foreach($myRatings as $index=>$rating){
   $sum+=$rating['FCOLX']; //where FCOLX is the column where you have the user rating [1-10];
}

$average = 0;

if($totalRatings>0){ 
  $average = $sum/$totalRatings;
}

The resulting average will be for the scale of 1-10 that you use.

Answer 2

You will want to store the rating for each time a page is ratd. because if you just store the average rating in one column, such as

ratings (page_id, average_rating)

It is not easy to determine:

1. How many users rated
2. Distribution of the ratings (how many voted 10, for example)

And you cannot allow users to change their rating, because everything has been flatten into one row. You can do without the second table if you use the average function in MYSQL

$result = mysql_query("SELECT AVG(rating) FROM ratings GROUP BY page_id WHERE page_id = '$pageid'");
$rating_for_page = mysql_fetch_row($result);
$rating = $rating_for_page[0];

Notice that I am assuming each rating is stored as a seperate row

Answer 3

A simple average is not very good in itself for a rating. For instance, if a hundred people voted something for an average of 8.5, then just one person voting 10 on something else would give it a better ranking.

I've asked a similar question some time ago that might interest you.