[SOLVED] add mysql data

[ngreenwood6]

I have a question that will help me solve alot of my problems. I have a table in my database that stores numbers (ratings for pictures). I want to be able to get the average of these numbers. For example if there is 5 ratings in the database and the numbers are all 5 the average would be 5. I know that I can divide the numbers by mysql_num_rows but I do not know how to add the numbers in the database individually. I can display them on a page but am lost when it comes to adding them. Any help is appreciated.

 

Thanks

[Mchl]

There are functions for this:

 

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

[ngreenwood6]

Could you elaborate a bit more. I am trying to do it in php not through the mysql which is how it shows you. If anyone has a better method please let me know.

 

Thanks

[Mchl]

Doing it in mysql would be faster.

 

Let's see how php implementation could look like:

 

$sum = 0;
$count = 0;
while ($row = mysql_fetch_array($result)) { 
$count++;
$sum += $row['rating'];
}
$average = $sum/$count;

[ngreenwood6]

Well how would I do it in mysql.

[ngreenwood6]

When I do my mysql query it looks like this:

 

"SELECT AVG(rating) from 'rate_picture'"

 

but it returns nothing.

 

When i take out the ' around rate_picture it says resource id #5. Any help.

[Mchl]

Do you get any mysql errors?

[ngreenwood6]

Both ways i do not get any errors. Is there any way that I can just add all the numbers in the table and display that number. The reason I ask this is because if I can get that number I can just divide it by the number of rows to get the average.

[Mchl]

There's a php solution a few postes above... didn't it work?

 

Try putting

echo mysql_error();

after mysql_query line. It should display any mysql errors that are caused by this query.

[ngreenwood6]

I got no error. I do not understand what you are doing with the php solution. Could you explain it or any other solutions would be helpful.

[Mchl]

Well, perhaps I should add one line here

 

$result = mysql_query("SELECT rating FROM table WHERE pictureID = $pictureID");  //this is a query to get ratings for selected picture. the ine you use is probably different
$sum = 0;  // start with sum of ratings being 0
$count = 0;  //and count of ratings summed is also a 0
while ($row = mysql_fetch_array($result)) {   //go through all rows you got from mysql
$count++;   // increase $count by 1
$sum += $row['rating'];  //add rating in the current row to $sum
}
$average = $sum/$count;   //calculate average

 

[ngreenwood6]

Cool that worked perfectly. Thanks for all of your help. I dont like using any code that i do not understand what it does. I am a noob trying to learn so thanks for your patience.

[Mchl]

No problem. Glad I coould help.

I'm still wondering what went wrong with SQL method.

 

You should try it again, when you have some free time.

[ngreenwood6]

Well post it out for me how you would do the whole query if you want to help me try to figure it out.

 

I had this

 

<?php
$host = "host";
$user = "user";
$pass = "pass";
$db = "db";
$table = "table";

$connect = mysql_connect($host, $user, $pass);

mysql_select_db($db);

$select = "SELECT AVG(rating) from $table"; //rating is my field

$result = mysql_query($select);

echo $result;

?>

 

Any suggestions

[Mchl]

$select = "SELECT AVG(rating) AS average from $table"; //note: 'AS average' is mySQL alias, it will be the key in $row array
$result = mysql_query($select);

$row = mysql_fetch_array($result);  //$result is a resource, from witch you have to fetch rows (even if there's only one row)

echo $row['average'];

 

Edit:

Download mySQL GUI Tools. There's a nice tool there: mySQL Query Browser, that'll let you test queries without having to write php code.

[ngreenwood6]

Great that worked out. I knew I was missing something.

[Mchl]

Great

 

In case you haven't noticed: I added some info to the previous post about mySQL tools.

 

Have fun with coding