Average Function

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
syntax406
Forum Newbie
Posts: 7
Joined: Mon Mar 01, 2004 3:57 pm

Average Function

Post 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]
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post 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
syntax406
Forum Newbie
Posts: 7
Joined: Mon Mar 01, 2004 3:57 pm

Post by syntax406 »

I'm sorry, I don't mean to be an idiot or anything, but could you please be a bit more specific?
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post by Illusionist »

whts to be mroe specific about? he gave you the answer:

"SELECT AVG(fieldname) FROM database"

use AVG rather than SUM
syntax406
Forum Newbie
Posts: 7
Joined: Mon Mar 01, 2004 3:57 pm

Post 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?
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post 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);
?>
syntax406
Forum Newbie
Posts: 7
Joined: Mon Mar 01, 2004 3:57 pm

Post by syntax406 »

Worked, Thank you very much!!! :D
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post 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.
syntax406
Forum Newbie
Posts: 7
Joined: Mon Mar 01, 2004 3:57 pm

Post by syntax406 »

Once again, thank you immensely for your help!
Post Reply