Add sum of each column and display each total in the bottom of the table using foreach loop

0
votes

I have this foreach loop that I used to query Microsoft Access database but I don't know how to sum specific column that have value or no value and display the sum in the last row of the table as "total".

Here is my code : 

$sql = "SELECT EmployeeName, BasisSalary, Bonus FROM tableEmployee";

if ($result = $connectdb->query($sql)) 
{
    $rows = '';             
    echo '<table>';   

    foreach($result->fetchAll(PDO::FETCH_ASSOC) as $row) 
    {                 
        $heading = '';          
        $rows .= '<tr>'; 

        foreach($row as $key => $value) 
        {
            $limitwords = substr($value, 0,50);
            $heading .= '<th>'.$key.'</th>';
            $rows .= '<td>' . $limitwords . '</td>';
        }

        $rows .= '</tr>';
    }

    echo '<tr>'.$heading.'</tr>';
    echo $rows;

    echo '</table>';
}

My code above will display output like this:

|EmployeeName|BasicSalary| Bonus |
|     A      |   10.00   | 10.00 |
|     B      |   20.00   | 10.00 |
|     C      |   30.00   | 10.00 |

So I want to display the total as the last row of the table like this:

|EmployeeName|BasicSalary| Bonus |
|     A      |   10.00   | 10.00 |
|     B      |   20.00   | 10.00 |
|     C      |   30.00   | 10.00 | 
|      Total |   60.00   | 30.00 |
1
phpms-accesspdo

1 Answers

1
votes

Keep two variables $totalBasicSalary and $totalBonus to add total for both fields basic salary & bonus.

Modified Code:

$sql = "SELECT EmployeeName, BasisSalary, Bonus FROM tableEmployee";

if ($result = $connectdb->query($sql)) {
    $totalBasicSalary = $totalBonus = 0;
    echo '<table> '
    . ' <tr>'
    . '<th>EmployeeName</th>'
    . '<th>BasisSalary</th>'
    . '<th>Bonus</th>'
    . '</tr>';

    foreach ($result->fetchAll(PDO::FETCH_ASSOC) as $row) {
        echo '<tr>'
        . '<td>' . substr($row["EmployeeName"], 0, 50) . '</td>'
        . '<td>' . $row["BasisSalary"] . '</td>'
        . '<td>' . $row["Bonus"] . '</td>'
        . '</tr>';

        $totalBasicSalary += $row["BasisSalary"];
        $totalBonus += $row["Bonus"];
    }

    echo '<tr>'
    . '<td>Total</td>'
    . '<td>' . $totalBasicSalary . '</td>'
    . '<td>' . $totalBonus . '</td>'
    . '</tr>'
    . '</table>';
}