Page 1 of 1

Average Function

Posted: Tue Mar 09, 2004 6:03 pm
by syntax406
Am am trying to find the average of several fields in a mysql db. I am using this code:

<?php
$result = mysql_query("SELECT * FROM database",$db);
$num_rows = mysql_num_rows($result);
$field1 = mysql_query("SELECT SUM(fieldname) FROM database",$db);
$var1 = $field1 / $num_rows;
echo "$var1";
?>

However, instead of returning the correct average (should be about 16), '0.085714285714286' is what gets returned. Any advice?[/php_man]

Posted: Tue Mar 09, 2004 6:40 pm
by Unipus
First of all, it's probably returning unexpected values because you're not operating on the value returned, you're operating on the mysql_object. But let's ignore all that, because it's unnecessary. You can just use:

Code: Select all

SELECT AVG(fieldname) FROM database GROUP BY something

Posted: Tue Mar 09, 2004 9:09 pm
by syntax406
I'm sorry, I don't mean to be an idiot or anything, but could you please be a bit more specific?

Posted: Tue Mar 09, 2004 9:10 pm
by Illusionist
whts to be mroe specific about? he gave you the answer:

"SELECT AVG(fieldname) FROM database"

use AVG rather than SUM

Posted: Tue Mar 09, 2004 9:47 pm
by syntax406
Ok, so I changed SUM to AVG

Code: Select all

<?php 
$field1 = mysql_query("SELECT AVG(fieldname) FROM survey_scores"); 
echo "$field1"; 
?>
Now it returns "Resource id #2".
How can I set it to display the numerical average?

Posted: Tue Mar 09, 2004 10:04 pm
by Illusionist
i suggest going back to the basics and/or reading over some MYSQL tutorials. Thats what Unipus was talking about earlier. You can't do that.

try this:

Code: Select all

<?php
$result = mysql_query("SELECT AVG(fieldname) FROM survey_scores");
echo mysql_result($result,0);
?>

Posted: Tue Mar 09, 2004 10:25 pm
by syntax406
Worked, Thank you very much!!! :D

Posted: Wed Mar 10, 2004 2:28 pm
by Unipus
Great. But just to clarify, mysql_query doesn't return a value, it returns a MySQL resource object (explains the output you saw). You need to further mine that resource to get the result you're looking for, which is what Illusionist's addendum does... breaks down the resource into an array and returns the first value of the array.

Posted: Wed Mar 10, 2004 2:52 pm
by syntax406
Once again, thank you immensely for your help!