asked on

Sum an array - producing totals for a reporting system

Hi,

I'm building a reporting system in PHP and Mysql. I would like to know how to sum arrays.

The following is part of my script:

============================

$rep9=mysql_query("SELECT zerouno, COUNT(cod_persone) AS 'count for zerouno'
FROM persone
where YEAR(timestamp_1) >= 1999
and sospeso = 'N'
and do_not_mail = 'N'
and cancellato = 'N'
and privacy = 'N'
GROUP BY zerouno; ") or die($rep9);

echo "<table class=\"dot_all\" align=\"center\" border=\"0\" cellpadding=\"4\" cellspacing=\"1\" width=\"60%\">
 <tr>
 <td class=\"verdana9g\" align=\"center\">Zerouno - Yes/No</td>
 <td class=\"verdana9g\" align=\"center\">Tot.</td>
 </tr>";

while($array9=mysql_fetch_array($rep9))
{

$zerouno=$array9['zerouno'];
$count_zerouno=$array9[COUNT(cod_persone)];

 echo "<tr>";
 echo "<td class=\"verdana9g\" align=\"center\">";

 if(1==$zerouno)
 {
 echo "SI";
 }
 else {
 echo "NO";
 }


 echo "</td>";
 echo "<td class=\"verdana9g\" align=\"center\">$count_zerouno</td>";
 echo "</tr>";
 }

echo "</table> ";

=============================

I would like to add the totals at the end of my html table. How can I sum $count_zerouno?

I thought array_sum($count_zerouno) would do the trick:

 <tr>
 <td class=\"verdana9g\" align=\"center\">Total</td>
 <td class=\"verdana9g\" align=\"center\">array_sum($count_zerouno)</td>
 </tr>";

Please let me know.

Cheers,
-L.

ASKER CERTIFIED SOLUTION

jstretch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jstretch

Or in mysql you can SELECT SUM(zerouno)

Laurent Belin

ASKER

I need the sum of this $array9[COUNT(cod_persone)]; This what I tried and seems to work. Is the syntax correct?

This $array_sum = 0; basically initializes the variable, right?

$rep9=mysql_query("SELECT zerouno, COUNT(cod_persone) AS 'count for zerouno'
FROM persone
where YEAR(timestamp_1) >= 1999
and sospeso = 'N'
and do_not_mail = 'N'
and cancellato = 'N'
and privacy = 'N'
GROUP BY zerouno ") or die($rep9);

echo "<table class=\"dot_all\" align=\"center\" border=\"0\" cellpadding=\"4\" cellspacing=\"1\" width=\"60%\">
 <tr>
 <td class=\"verdana9g\" align=\"center\">Zerouno - Si/No</td>
 <td class=\"verdana9g\" align=\"center\">Tot.</td>
 </tr>";

$array_sum = 0;

while($array9=mysql_fetch_array($rep9))
{

$zerouno=$array9['zerouno'];

$count_zerouno=$array9[COUNT(cod_persone)];
$array_sum += $array9[COUNT(cod_persone)];

 echo "<tr>";
 echo "<td class=\"verdana9g\" align=\"center\">";

 if(1==$zerouno)
 {
 echo "SI";
 }
 else {
 echo "NO";
 }


 echo "</td>";
 echo "<td class=\"verdana9g\" align=\"center\">$count_zerouno</td>";
 echo "</tr>";
 }

 echo "<tr><td class=\"verdana9g\" align=\"center\">Total</td>
 <td class=\"verdana9g\" align=\"center\">$array_sum</td></tr>";

echo "</table> ";

jstretch

COUNT(x) will return the total number of rows for the result.
SUM(x) will add all the values of x

Consider the line:
$array_sum += $array9[COUNT(cod_persone)];

Lets say there are a total of 10 rows returned from your SELECT ... statement. So whats happening here is your always adding $array9[10] to $array_sum.

I think you want to do is change 2 lines:

$rep9=mysql_query("SELECT zerouno, cod_persone .....
.....
$array_sum += $array9['cod_persone'];

try that and see what happens.

Laurent Belin

ASKER

Ok, I see what you mean.

But, I need the COUNT and thne I need to do a SUM of the COUNT. Your first solution was correct.

I just wanted to know why you set $array_sum = 0; before the While loop. Is it because you need to initialize it?

Thanks,
-L.

jstretch

Yes you have to initialize it outside of the loop so you can use it after the loop.

Laurent Belin

ASKER

If I wanted to reuse it for another SUM of another COUNT...I would just have to initialize it again before a loop, right?

I have tried it works.

Thanks for your help.

-L.

jstretch

If you need to sum another field in the same loop I would just use a different variable so they dont get mixed up.

$array_sum2 = 0; //something like this

Or be more specific with your variable names:

$persone_sum = 0;
$zerouno_sum = 0;
etc....