[SOLVED] Some error in my math code.

[xstevex]

Hi,

 

Can anyone see any obvious errors in this code. It is for calculating votes or ratings by users on files. They can vote from 1-5. Their vote is added to the cumulative total and then averaged.

 

The two database fields are "rating" and "rate_num" and they are both int(11).

 

"Rating" is the average vote and "rate_num" is the number of people that have voted.

 

Here is the code:

if (isset($_POST['my_vote']))
  {  
  $result = mysql_query("SELECT rating, rate_num FROM files WHERE file_id = '$file_id'") or die(mysql_error());
  $ratings = mysql_fetch_assoc($result);
  
      $new_count = ($ratings['rate_num'] + 1);
      $media_rating = ($ratings['rating'] * $ratings['rate_num']);
      $new_rating = (($_POST['rating'] + $media_rating) / ($new_count));
      $new_rating = number_format($new_rating);
  
  $update = mysql_query("UPDATE files SET rating='$new_rating', rate_num='$new_count' WHERE file_id = '$file_id'");
}

 

The code looks good to me and it looks like it does what it should do.

1. Adds 1 vote to the "rate_num"

2. Multiplies the "rate_num" by the average "rating" to get the cumulative

3. Adds the new vote to the cumulative and then divides the new cumulative by the new "rate_num"

 

But it gets incorrect answers. So there could be something wrong with it. Maybe I should have a third database field that keeps track of the cumulative figure.

 

Steve.

 

 

 

[Barand]

remove

 

$new_rating = number_format($new_rating);

 

Don't store formatted numbers, just the raw value. Format when you output.

 

 

Edit: Life would be easier if you just store the cumulatives and calculate the average when needed.

<?php

$rating = intval($_POST['rating']);
mysql_query ("UPDATE files SET rating=rating+$rating, rate_num=rate_num+1 WHERE file_id = '$file_id' ");

[xstevex]

Thanks for you answer Barand,

 

I tried removing that line but was still getting incorrect answers. I must confess I was in the "C" maths class at school. I'm not sure how I would implement the second solution.

 

I'll try to put a third field into the database that has the cumulatives so then I will be able to check the database to get a better idea where it is going wrong.

 

Steve.

[Barand]

What column type is "rating" defined as in your table?

[xstevex]

It's int(11) currently.

 

I've played around with making it DOUBLE(7,6) but now it's back to int(11).

 

Steve.

[Barand]

To do it your way it need be able to hold decimals otherwise a rating of 1.5 is stored as 1, so change to, say, DECIMAL(10,3)

 

If you do it my way, INT is fine. Then to get the top 10 av ratings you

 

SELECT file_id, (rating/rate_num) as avrating FROM files

ORDER BY avrating DESC

LIMIT 10

[xstevex]

Ok, new code in progress...

[xstevex]

Here is the new code (It works!):

 

if (isset($_POST['rating']))
  {  
  $result = mysql_query("SELECT rating, rate_num, rate_cumm FROM files WHERE file_id = '$file_id'") or die(mysql_error());
  $ratings = mysql_fetch_assoc($result);
  
  $new_count = ($ratings['rate_num'] + 1);
  $new_cumm = ($_POST['rating'] + $ratings['rate_cumm']);
      $new_rating = ($new_cumm) / ($new_count);
  
  $update = mysql_query("UPDATE files SET rating='$new_rating', rate_num='$new_count', rate_cumm='$new_cumm' WHERE file_id = '$file_id'");
  

 

"rate_cumm" is the new cumulative field and it is type--int(11)

"rate_num" is unchanged--the number of people who have voted--int(11)

"rating" is unchanged--the current score/rating but it is now--FLOAT(1,0)

 

I would like to make "rating" FLOAT(2,1) to get a more accurate number but my other code that uses this rating number is:

 

elseif ($rating == '4') {
	$stars = "<img src="images/star_4.png" />";

 

And the same for the other rating numbers (0,1,2,3 and 5). As you can see it won't know what to do with a decimal such as 4.1

 

How would I make the "4" in the code a range like "4 to 4.9"?

 

Steve.

 

 

 

[Barand]

Make it FLOAT(4,3)

 

I'd be tempted to use round() function otherwise even 4.999 will result in 4 stars and probably nothing would ever get a five unless everyone voted 5

 

elseif (round($rating,0) == 4) {
	$stars = "<img src="images/star_4.png" />";

[Barand]

A couple of observations

 

you can replace all those

elseif (round($rating,0) == 4) {{
	$stars = "<img src="images/star_4.png" />";

 

with a single line

$stars = sprintf ('<img src="images/star_%1d.png" />', round($rating,0) );

 

Also, you are still fetching the existing record, calculating then updating whereas my code just does an update. So the load on the server is halved.

 

[xstevex]

Thanks Barand,

 

Your first code works beautifully.

 

I tested it by making one file rated 4.125 and it showed 4 stars.

 

I'm not sure if it's fetching. Here is the full code:

 

function insert_media_rating($a)
{
global $config;

$sql = mysql_query("SELECT * FROM files WHERE file_id=$a[id] LIMIT 1");
while ($row = @mysql_fetch_array($sql))
{
	$rating = $row['rating'];
}

if ($rating == '0') {
	$stars = "<img src='/images/stars_0.png' />";
}
elseif ($rating == '1') {
	$stars = "<img src='/images/stars_0.png' />";
}
elseif ($rating == '2') {
	$stars = "<img src='/images/stars_0.png' />";
}
elseif ($rating == '3') {
	$stars = "<img src='/images/stars_0.png' />";
}
elseif (round($rating,0) == 4) {
	$stars = "<img src='/images/stars_0.png' />";
}
else {
	$stars = "<img src='/images/stars_0.png' />";
}

return $stars;
}

 

I've just changed the 4 star line so far. I'll change the rest now. It's works great, I also checked it with a rating of 4.512 and it correctly rounded to 5 and showed 5 stars.

 

Steve.

[xstevex]

I'll test the second single line too.

[xstevex]

Something was going funny with the single line. But it doesn't matter the thing all works now.

 

Thanks a lot for you help with this Barand.

 

Steve.

[Barand]

the single update to replace

if (isset($_POST['rating']))

  { 

  $result = mysql_query("SELECT rating, rate_num, rate_cumm FROM files WHERE file_id = '$file_id'") or die(mysql_error());

  $ratings = mysql_fetch_assoc($result);

 

  $new_count = ($ratings['rate_num'] + 1);

  $new_cumm = ($_POST['rating'] + $ratings['rate_cumm']);

      $new_rating = ($new_cumm) / ($new_count);

 

  $update = mysql_query("UPDATE files SET rating='$new_rating', rate_num='$new_count', rate_cumm='$new_cumm' WHERE file_id = '$file_id'");

}

   

is

<?php
if (isset($_POST['rating']))
  {
       $rating = intval($_POST['rating']);

       mysql_query ("UPDATE files SET 
          rate_cumm = rate_cumm + $rating, 
          rate_num = rate_num + 1 ,
          rating = rate_cumm / rate_num
          WHERE file_id = '$file_id' ");
  }

[xstevex]

Yes, this does the job too. And since it uses half the resources I'll use it instead. If fact it does seem quicker.

 

Steve.