PHP-MYSQL working out average results

[Wardy]

Tweet

I guess this shouldn't be too hard but would appreciate any help.

Ia hev a database that contains a rating (ranging from 1-10) that users click on to review something.
Insteda of now displaying all the individual results from the votes I want to just show the average.
Anyone able to shopw me the PHP needed to do this please...


Cheers
Wardy

[Freebie]

i do this in a few places, and prob go a long way about it, but this is what i do.

I have a field called total, each time there's a vote i add the vote value to this field. So if the first 2 people vote 10, then this field holds 20.

I have a field called votes. With each vote for that particular product i increment the value by 1.

So at this stage i know for product a, i've got 10 votes with a total of 50.

Simple maths gives you the rest. ie total/votes = average.

[dmorison]

You can use SQL's built in math functions to do this.

SELECT AVG(rating) AS average FROM sometable;

PHP would look something like this (database stuff included for completeness, but you've probably got all that already):

$link = mysql_connect("localhost","username","password");
mysql_select_db("somedatabase",$link);
$sql = "SELECT AVG(rating) AS average FROM sometable";
$result = mysql_query($sql,$link);
$row = mysql_fetch_array($result);
$average = $row["average"]; 

Hope this helps!

[Wardy]

Thanks for the replies guys, the AVG function was exacly what I was after

1 small snag is that I want to round the results to 1 decimal place, I know it requires the "round" function but having trouble gettign ti working correctly. When I add the round function it's just showing the nearest whole number and not to the 1 decimal place.

$result = mysql_query("SELECT AVG(score) FROM game1");
echo round ($result,1);

Anyone any suggestions what glaring mistake I'm making now?!

Cheers
Wardy

[ChrisAF]

The following should work:
mysql_query("SELECT Round(AVG(score),1) FROM game1");

[dmorison]

Hiya,

Your code seems to be missing the line where you extract the actual average value from the query result. Exactly what you have posted would just round the value of the database resource handle....

Try this:

$sql = "SELECT AVG(rating) AS average FROM sometable";
$result = mysql_query($sql,$link);
$row = mysql_fetch_array($result);
$average = round($row["average"],1); 

[Rich]

You need to get the result before rounding it. i.e

$result = mysql_query("SELECT AVG(score) FROM game1");
$row=mysql_fetch_row($result);
echo round ($row[0],1);

but I get the feeling that might just be a typo.

The result is 4 then round will still return 4, it won't make it 4.0. number_format will probably do better for you.
number_format(4,1)="4.0"
number_format(3.55,1)="3.6"

If you are still getting whole numbers, check what the result is from mysql before doing any processing.

[ChrisAF]

Sorry for not being clear, I was refering to the MySQL ROUND function as opposed to the php round function. Or is there something wrong with the MySQL ROUND function?

[Wardy]

Cool, nice one guys. Working a treat now thanks

Cheers
Wardy