5 star rating system messes up my numbers

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 got the mysql_query code from phpmyadmin and just left it as is. pid is my primary key so i can take it out..

iv just tried taking out the limit and it still has a problem. Here is a link to a preview page..

http://www.grafax.co.uk/OTHER/zavvex/shop/viewbycat.php?category=all

just vote on any of them a few times and it will start messing up.

I see a button to add to basket but I don't see a button to vote

click one of the 5 stars

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.

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.

okay, thanks a lot. reply when you have time :)

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.

Thanks for the help :)