[SOLVED] Finding the average from adding multiple entries from db

[djfox]

What I want to do is have the code add up all the entries in the database and find the average from a field.

 

Ie, the field name "views" in the table "submissions" and the entries in that field in that table are 234, 365, 264, 646, 354. Add up all of those and get the average number, which in this case is 373 (with the number rounded up to a whole number). I can do the rounding of the number up but I`m not sure how to tell the code to add up all of those numbers from a certain field in the database table.

[ngreenwood6]

I would look into the sum function of mysql. A basic example is:

 

SELECT SUM(views) FROM table

 

a good tutorial is here http://www.tizag.com/mysqlTutorial/mysqlsum.php

[djfox]

Thanks very much. I`ll give this a go.

[djfox]

Ok, I gave it go according to what http://www.tizag.com/mysqlTutorial/mysqlavg.php showed. So I entered this:

 

<?php
$query = "SELECT gallNum, AVG(views) FROM image WHERE gallNum='$g' GROUP BY gallNum"; 
$result = mysql_query($query) or die(mysql_error());

echo "The average views is ".$row['AVG(views)'];

?>

 

I get a blank as a result.

[ngreenwood6]

Sorry about that I do not know why I told you to do the sum function when you are trying to get an average I misread the post. Try this:

 

<?php
$query = "SELECT gallNum, AVG(views) AS avg_views FROM image WHERE gallNum='$g' GROUP BY gallNum"; 
$result = mysql_query($query) or die(mysql_error());

echo "The average views is ".$row['avg_views'];

?>

[Philip]

You still need to call mysql_fetch_assoc (or array)

[ngreenwood6]

Wow, good call kingphilip. I am seriously going to bed in like 5 mins because I am missing some obvious stuff. Good thing you are following my posts lol. I do like doing it with the AS section though because it is easier to read.

[Philip]

Agreed, I would use the AS, it makes things a lot easier

[djfox]

Ok, so I now have:

<?php
$query = "SELECT AVG(views) AS avg_views FROM image WHERE gallNum='$g'"; 
$result = mysql_query($query) or die(mysql_error());
mysql_fetch_assoc($result);

echo "The average views is ".$result['avg_views'];
?>

 

But the result is still blank.

 

[ngreenwood6]

Try this:

 

<?php
$query = "SELECT AVG(views) AS avg_views FROM image WHERE gallNum='$g'"; 
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_assoc($result))
{
echo "The average views is ".$row['avg_views'];
}
?>

 

You should look more into mysql's data input and retrieval.

[djfox]

Eureka! We struck it! Thanks guys.