Solved

Sum an array - producing totals for a reporting system

Posted on 2005-04-28
296 Views
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\"><b>Zerouno - Yes/No</b></td>
<td class=\"verdana9g\" align=\"center\"><b>Tot.</b></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><br><br>";

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

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\"><b>Total</b></td>
<td class=\"verdana9g\" align=\"center\"><b>array_sum(\$count_zerouno)</b></td>
</tr>";

Cheers,
-L.

0
Question by:lollodev

LVL 6

Accepted Solution

I would just keep a sum variable.

\$array_sum = 0;

while(....fetch_array...) {

\$array_sum += \$array9['zerouno'];
}

//echo your last row for sums
0

LVL 6

Expert Comment

Or in mysql you can SELECT SUM(zerouno)
0

Author Comment

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\"><b>Zerouno - Si/No</b></td>
<td class=\"verdana9g\" align=\"center\"><b>Tot.</b></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\"><b>Total</b></td>
<td class=\"verdana9g\" align=\"center\"><b>\$array_sum</b></td></tr>";

echo "</table><br><br>";
0

LVL 6

Expert Comment

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.

0

Author Comment

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.
0

LVL 6

Expert Comment

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

Author Comment

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.

-L.
0

LVL 6

Expert Comment

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....
0

Featured Post

The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.