Sum Columns in PHP table

Question

I have an index.php with various SQL queries which helps me find the balances of the respective accounts. This is a TRIAL BALANCE so I need to SUM all the amount in the Debit Column and SUM all the amount in the Credit Column so that I can Tally both of them. Please help me with the SUM. All values are echoed to a PHP table.

index.php

<?php
    echo "<table border='1'>";
    echo "<tr><th width='150'>Account</th><th width='200'>Debit</th><th width='200'>Credit</th></tr>";
    echo "<tr><th align='left'>Cash & Bank</th></tr>";


    //List the Banks first with their respective balances.
    $bank_sql = "SELECT * FROM bank";
    $bank_query = mysqli_query($conn, $bank_sql);
    while ($bank_result = mysqli_fetch_array($bank_query)){
    $bank_name = $bank_result['name'];

    $sql= "SELECT SUM(amount) FROM account WHERE (mode='$bank_name' AND status='completed') AND (type='p' OR type='r')";
    $sql_query = mysqli_query($conn, $sql);
    while($sql_result = mysqli_fetch_array($sql_query)){
        $sql_value = $sql_result['SUM(amount)'];
    }

    $sql1 = "SELECT SUM(amount) FROM account WHERE (mode='$bank_name' AND status='completed') AND (type='s' OR type='pa')";
    $sql1_query = mysqli_query($conn, $sql1);
    while($sql1_result = mysqli_fetch_array($sql1_query)){
        $sql1_value = $sql1_result['SUM(amount)'];
    }

    echo "<tr><td>".$bank_name."</td>";
    if ($sql_value > $sql1_value){
        echo "<td align='right'>AED " . number_format(($sql_value - $sql1_value),2) . "</td><td>&nbsp</td>";
    }
        elseif ($sql1_value > $sql_value) {
        echo "<td>&nbsp</td><td align='right'>AED " . number_format(($sql1_value - $sql_value),2) . "</td>";
        }
        else {
            echo "<td>&nbsp</td><td>&nbsp</td>";
        }
    }
    echo "</tr></table>";
    }
    ?>

Why not write a query that lets the database calculate everything for you? look at using `group by with rollup` http://dev.mysql.com/doc/refman/5.6/en/group-by-modifiers.html and then use case to handle the different modes, types, parties and status.. so you get back aggregated results. the DB engine will be faster than you can do in PHP. You're php will get much simpler that way... — xQbert, Jan 13 '15 at 14:40
Im just a beginner in PHP. Thats what makes my PHP code huge. I managed to print out the results as expected. All i need is to Total the printed results. — Ahamed Zulfan, Jan 13 '15 at 14:43

score 0 · Answer 1 · answered Jan 13 '15 at 14:36

0

Try the below query
SELECT SUM(amount) FROM ht_account WHERE mode='$bank_name' AND status='completed' AND type IN('sale','purchase','reciept','payment')

answered Jan 13 '15 at 14:36

user3040610

750
4
15

I need to SUM the results of a column from the above query. – Ahamed Zulfan Jan 13 '15 at 14:39
mode='$bank_name' would need double quotes, and you're open to sql injection – Dallin Jan 13 '15 at 14:42
How am I Open to SQL injection? I use mysqli functions. – Ahamed Zulfan Jan 13 '15 at 14:45

score 0 · Answer 2 · answered Jan 13 '15 at 14:38

A quick way to do it would be.

$sql3="select column1,column2 from yourtable where ..condition";
$sumofcolumn1;
$sumofcolumn2;
    while($sql3_result = mysqli_fetch_array($sql3))
     {
       $sumofcolumn2+=$sql3_result['column2'];
       $sumofcolumn1+=$sql3_result['column1'];
     }
echo $sumofcolumn1.' '.$sumofcolumn2

Sum Columns in PHP table

2 Answers2