CODEWITHSUNDEEP
phpmysql
Labrathor
Labrathor

Reputation: 15

How to find Average from mysql table in php?

I need to calculate average for r1-r25 columns in ratings table.There will be null values in the columns represented by value 99.How to find the average for 25 columns in efficient manner? The sample code I used for a single column is as follows,

Sample PHP code

$s=0;
$n=0; 
$res = mysql_query("SELECT * FROM ratings");  

while($arr=mysql_fetch_array($res)){    
    $rat=$arr['r1'];
    if($rat != 99)
    {
        $s=$rat+$s;
        $n=$n+1;
        $avg=$s/$n; 
    }       
}

Upvotes: 1

Views: 317

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use the database for this work:

SELECT AVG(case when r1 <> 99 then r1 end) as r1_avg,
       AVG(case when r2 <> 99 then r2 end) as r2_avg,
       . . .
       AVG(case when r25 <> 99 then r25 end) as r25_avg     
FROM ratings;

This will be faster because you don't have to bring all the data over to the application for sequential processing.

It does require more typing (or use a spreadsheet to generate the code). This is because you are storing data in columns, when the data should really be in separate rows -- one row per whatever and the rating (and perhaps a rating number). With that data structure, the query would be both shorter and better performing.

Also, SQL has a great mechanism for storing NULL values, and you should use it. It is called NULL, not "99".

Upvotes: 1

varunsinghal
varunsinghal

Reputation: 329

$res = mysql_query("SELECT * FROM ratings WHERE `r1` NOT IN ( 99 )");

Now you don't need if check in your while loop.

Upvotes: 0

Related Questions