Link to home
Create AccountLog in
Avatar of fcruz5
fcruz5Flag for United States of America

asked on

Displaying Difference for Totals

I have for example the table data results below (the first 3 columns):

Division          Quota           Total          New Column
1                    1600             1610          10
2                    400               220            -180
3                    900               800            -100
4                    850               900            50
5                    1300             1200          -100

I want to add a fourth column (New Column) that will calculate the difference between the "Quota" column and the "Total" column. For example: the first row has 1600 for the Quota and 1610 for the Total. So it should display the difference which is 10.

The second row for example: has 400 for the Quota and 220 for the Total. So it falls into the negative and displays -180.

So basically, I need it to display the amount if it was over or under. How would something like this be done?

Here is the PHP code:

<?php

echo "<table cellpadding=\'2\' cellspacing=\'0\'>";

echo "<tr>";
echo "<td class=\"th\" >Division</td>";
echo "<td class=\"th\" >Quota</td>";
echo "<td class=\"th\" >Total</td>";

if($result && mysql_num_rows($result) > 0)
{    for ($i = 0; $i < mysql_num_rows($result); $i++) {
    $resultArray[$i] = mysql_fetch_array($result);
}

foreach ($resultArray as $val) {
   echo ('<tr>');//first row
   if (($rowcount%2) == 0) {
        $css_class = "\"row\"";
    } else {
        $css_class = "\"alt\"";
    }

      echo ('<td class=' . $css_class . ' align=default >'.$val[1].'</td>');
      echo ('<td class=' . $css_class . ' align=default >'.$val[2].'</td>');
      echo ('<td class=' . $css_class . ' align=default >'.$val[3].'</td>');
    echo ('</tr>');
}


} // end if results
echo ('</table>');

?>
Avatar of frin
frin
Flag of Slovenia image

Add this, what it does is return absolute value of subtraction of the two desired columns (-1 becomes 1):
      echo ('<td class=' . $css_class . ' align=default >'.abs($val[3]-$val[2]).'</td>');
Avatar of fcruz5

ASKER

Thanks, it shows the difference now. But, theres one more thing that I am trying to display.

Division          Quota           Total          New Column
1                    1600             1610          10
2                    400               220            -180
3                    900               800            -100
4                    850               900            50
5                    1300             1200          -100

For example, take a look at the 2nd row with 400 as the Quota and 220 for the Total. For the totals that are below the Quota like this one, it should display a (-) symbol in front of the number. The difference is 180. Can it display as -180 like the example above?

I am not sure I quite follow you. First you need absolute value, now not ;) Unless you want a fifth column.

In that case, it's just a matter of subtracting the right value from the other:

      echo ('<td class=' . $css_class . ' align=default >'.($val[3]-$val[2]).'</td>');

Two examples:

$val[3] = 220
$val[2] = 400
$val[3]-$val[2] = 220-400 = -180

$val[3] = 1610
$val[2] = 1600
$val[3]-$val[2] = 1610-1600 = 10

Avatar of fcruz5

ASKER

The first example below that you gave me displays the correct differences like this:

echo ('<td class=' . $css_class . ' align=default >'.abs($val[3]-$val[2]).'</td>');

Division          Quota           Total          New Column
1                    1600             1610          10
2                    400               220            180
3                    900               800            100
4                    850               900            50
5                    1300             1200          100

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

The only thing that is missing is the (-) sign in front of the values for the Totals that fall under the Quota. That way you can see if the Division was over or under the Quota.

Any number under the "Total" column that is below the number under the Quota column will have minus (-) sign in front of it like in rows 2, 3, and 5. See the below example.

The total 220 falls under the 400 Quota so the difference 180 displays as -180
The total 800 falls under the 900 Quota so the difference 100 displays as -100
The total 1200 falls under the 1300 Quota so the difference 100 displays as -100

Division          Quota           Total          New Column
1                    1600             1610          10
2                    400               220            -180
3                    900               800            -100
4                    850               900            50
5                    1300             1200          -100

ASKER CERTIFIED SOLUTION
Avatar of frin
frin
Flag of Slovenia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of fcruz5

ASKER

Thanks