P.Bes P.Bes - 5 months ago 13
PHP Question

Find Average with mysql_fetch_array and array_sum

I have this php file and I want to find the average of each column(sum the m1 column for example and divide with $total to take the average) but with the code below nothing happens whta is the proble?

<?php
$host="localhost";
$username="root";
$password="";
$databasename="moviecinema";

$connect=mysql_connect($host,$username,$password);
$db=mysql_select_db($databasename);

$select_rating=mysql_query("select m1,m2,m3,m4,m5,m6,m7,m8,m9,m10 from rating");
$total=mysql_num_rows($select_rating);

while($row=mysql_fetch_array($select_rating))
{
$m1=$row['m1'];
$m2=$row['m2'];
$m3=$row['m3'];
$m4=$row['m4'];
$m5=$row['m5'];
$m6=$row['m6'];
$m7=$row['m7'];
$m8=$row['m8'];
$m9=$row['m9'];
$m10=$row['m10'];

}
$total_m1_rating=(array_sum($m1)/$total);
$total_m2_rating=(array_sum($m2)/$total);
$total_m3_rating=(array_sum($m3)/$total);
$total_m4_rating=(array_sum($m4)/$total);
$total_m5_rating=(array_sum($m5)/$total);
$total_m6_rating=(array_sum($m6)/$total);
$total_m7_rating=(array_sum($m7)/$total);
$total_m8_rating=(array_sum($m8)/$total);
$total_m9_rating=(array_sum($m9)/$total);
$total_m10_rating=(array_sum($m10)/$total);

?>

<form method="post" action="">

<div id="div1" class="div">
<p>Charlie and the Chocolate Factory (<?php echo $total_m1_rating;?>)</p></div>


/the same for m2,m3,m4.../

Answer

You can use AVG() function like this..

$select_rating=SELECT Avg(m1) as avg_m1, Avg(m2) as avg_m2, Avg(m3) as avg_m3, Avg(m4) as avg_m4, Avg(m5) as avg_m5, Avg(m6) as avg_m6, Avg(m7) as avg_m7, Avg(m8) as avg_m8, Avg(m9) as avg_m9, Avg(m10) as avg_m10 FROM rating;

 while($row=mysql_fetch_array($select_rating))
            {
                $m1=$row['avg_m1'];
                $m2=$row['avg_m2'];
                $m3=$row['avg_m3'];
                $m4=$row['avg_m4'];
                $m5=$row['avg_m5'];
                $m6=$row['avg_m6'];
                $m7=$row['avg_m7'];
                $m8=$row['avg_m8'];
                $m9=$row['avg_m9'];
                $m10=$row['avg_m10'];

            }
<?php echo $m1; ?>

This will return you average of each column. For more you can try this link http://www.w3schools.com/sql/sql_func_avg.asp