Hi,

Im making a 5 star rating system but half the time it has the complete wrong result. I have gone over and over the script and checked the sums with a calculator and it is still doing something wierd. Sometimes when I rate it above it's current rating it moves the overall rating down...

The page that links to the php file just has links to the php file with the appropriate PID (product ID) and user's rating, for example index.php?pid=1234&rating=5. The ratings are between 1-5.

anyway, the code I currently have is below:

(by the way, apologies for the crappy notes, im not a mathmatician so find it difficult to explain what i'm doing.)

//get product id and user's rating
$pid = $_GET['pid'];
$newrating = $_GET['rating'];

//connect to db
include('../../php/database/connect.php');

//get current values from that product in the db
$query  = "SELECT * FROM products WHERE pid = '".$pid."'";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
//get current number of rates
$numberofrates = $row['numberofrates'];
$numberofrates = (int)$numberofrates;
//get current rating
$rating = $row['rating'];
$rating = (int)$rating;
//multiply current rating by number of ratings then increment number of rates by 1 to include new rating, and add user's rating to total rates to recreate average
$rating = $rating * $numberofrates;
$newnewrating = $rating + $newrating;
$numberofrates = $numberofrates + 1;
$newnewrating = $newnewrating / $numberofrates;

mysql_query('UPDATE `web163-zavvex`.`products` SET `rating` = "'.$newnewrating.'" WHERE `products`.`pid` = "'.$pid.'" LIMIT 1');
mysql_query('UPDATE `web163-zavvex`.`products` SET `numberofrates` = "'.$numberofrates.'" WHERE `products`.`pid` = "'.$pid.'" LIMIT 1');

echo $newnewrating;
}

Thanks in advance for pointing out where I have gone wrong :)

Max

mysql_query('UPDATE `web163-zavvex`.`products` SET `rating` = "'.$newnewrating.'" WHERE `products`.`pid` = "'.$pid.'" LIMIT 1');
mysql_query('UPDATE `web163-zavvex`.`products` SET `numberofrates` = "'.$numberofrates.'" WHERE `products`.`pid` = "'.$pid.'" LIMIT 1');

Why do you have to have "limit 1" on your update queries, is "pid" not your primary key. If not, maybe you are having issues with duplicate rows? This could produce unpredictable results.

I see it now, wouldn't work if firefox, switched to IE and I can see where the images are suppose to be. Only the rollovers work. Attached is what I see. Could have something to do with the network I am behind as well, maybe.

Attachments Untitled-1.jpg 196.32 KB

okay all the images and rollover's are sorted out now. it was just a simple mistake with the php script. try rating an item as 5 star about 5 times and eventually you will see the new rating on the next page decrease instead of decrease

Your calculations are not correct if you are looking for an average, I have just gotten busy and will have to get back to you on the error shortly.

OK, so what you are doing in your code is taking the current rating, adding the new rating to it, averaging that by dividing that by all votes. This will not work because you are not preserving the votes. For instance, if someone votes 5, what you do is take the 5 and add the current rating to it, but since lets say this is the first vote, the current rating is 0. So your current rating is 5 and the number of votes is 1 so you take 5/1 and that makes the current rating 5. Now take the second vote and follow the same process. Someone else comes around and votes 3. What you do is take the current rating add three to it and divide it by the number of votes so 8/2 makes the current rating 4, BUT rather than preserving that vote as 3 and using it in the next calculation when the 3rd person votes you store the average in rank. Is this making sense yet.

You can store rank at the time the vote was made but you HAVE to store the vote and calculate (vote+newvote)/(numvotes+1) what you are doing is (currentrank+newvote)/(numvotes+1). You must add up all the votes and divide by the number of votes not add up all the rankings at the time the votes were made + the new vote and divide by the number of votes.

Does this help?

Yes I see what you mean. So I would need to store every vote in a database.

I was thinking something similar to this might work:

$pid = $_GET['pid'];
$newrating = $_GET['rating'];

include('../../php/database/connect.php');

$query  = "SELECT * FROM products WHERE pid = '".$pid."'";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
explode(',',$votes);
$votesnumber = count($votes);

but I do not know where to go from there. Also there is a problem with the character limit in a MySQL database and eventually I would run out of voting space?

Yes I see what you mean. So I would need to store every vote in a database.

I was thinking something similar to this might work:

$pid = $_GET['pid'];
$newrating = $_GET['rating'];

include('../../php/database/connect.php');

$query  = "SELECT * FROM products WHERE pid = '".$pid."'";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
explode(',',$votes);
$votesnumber = count($votes);

but I do not know where to go from there. Also there is a problem with the character limit in a MySQL database and eventually I would run out of voting space?

Do you really think the votes will get that high? Your talking about exceeding the allowed integer limit? If you use a mediumint, that's 8388607/5 and over 1.5 million votes. Mediumint only takes 3 bytes. That would be in comparison to a varchar with 2 characters in it.

oh! I didnt realise you could change the type to medium int to increase the limit. I thought it had a 255 character limit. Well thats fine then :) You learn something everyday.


Okay so ill save all the results, explode them all into an array, count the array length, add all the arrays together, add the new vote and increment total votes by one then divide the total votes by total length.

How do I add every section of an array together?

Do you think the following makes a goo starting point?:

$pid = $_GET['pid'];
$newrating = $_GET['rating'];

include('../../php/database/connect.php');

$query  = "SELECT * FROM products WHERE pid = '".$pid."'";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
explode(',',$votes);
$votesnumber = count($votes);

Max

Iv solved it:

save all votes in a longtext field in the database with each number seperated with a comma and use the script below to average the score to the nearest whole number:

#set rating average

//explode ratings into an array
$rating = explode(',',$rating);

//get the total number of ratings
$ratingscount = count($rating);

//add everything within the array
foreach($rating as $value)
{
$ratingstotal = $ratingstotal + $value;
}

//create final rating average
$ratingav = $ratingstotal / $ratingscount;

$ratingav = round($ratingav);

remember, your final rating value is now $ratingav, not $rating.

:) hope this helps people in the same situation as I was.

Iv solved it:

save all votes in a longtext field in the database with each number seperated with a comma and use the script below to average the score to the nearest whole number:

#set rating average

//explode ratings into an array
$rating = explode(',',$rating);

//get the total number of ratings
$ratingscount = count($rating);

//add everything within the array
foreach($rating as $value)
{
$ratingstotal = $ratingstotal + $value;
}

//create final rating average
$ratingav = $ratingstotal / $ratingscount;

$ratingav = round($ratingav);

remember, your final rating value is now $ratingav, not $rating.

:) hope this helps people in the same situation as I was.

That worked out pretty good. I was thinking about the text field in the database and I guess that would be a pretty good way to go for conserving all of the votes. Looks good.

This question has already been answered. Start a new discussion instead.