php - 使用php对记录集列中的负数/正数求和

Question

我有一个从 mysql 提取数据的函数，如下所示；

function displayTrialBalance(){

require_once('Connections/mpcs_dbConn.php');

mysql_select_db($database_dbConn, $dbConn);

// declare query variables
$query_diff_empty = "SELECT Account_Code, sum(Amount) AS difference FROM journal2 GROUP BY Account_Code ORDER BY difference asc, Account_Code asc";


// check if user wants empty


// query DB
$amounts = mysql_query($query_diff_empty, $dbConn) or die(mysql_error());
$row_amounts = mysql_fetch_array($amounts);
$totalRows_users = mysql_num_rows($amounts);

if ($row_amounts){

function debit($arr){
return ($arr < 0);
}

function credit($arr){
return ($arr >= 0);
}


echo '<table align="center" border="0" bordercolor="#666666" cellpadding="5" cellspacing="3">
        <tr class="tableTop">
        <td>Account Code</td>
        <td>Debit</td>
        <td>Credit</td>';

do {

$diff = array($row_amounts['difference']);

        echo '
        </tr>
        <tr class="tableButtom">
        <td>'.$row_amounts['Account_Code'].'</td>
        <td>';
        foreach((array_filter($diff, "debit")) as $new){
        echo abs($new).'.00';}
        echo '</td>
        <td>';
        foreach((array_filter($diff, "credit")) as $new2){
        echo $new2;
        }
        echo '</td>';


}while ($row_amounts = mysql_fetch_array($amounts));
echo '</tr>
</table>';
}   
}

当我调用 displayTrialBalance() 时，我得到:

 Account Code   Debit      Credit
 13101          350000.00   
 22601          3000.00 
 22201                     0.00
 41101                     3000.00
 21201                     40000.00

如何将负数(借方列)和正数(贷方列)相加以获得输出表末尾的总计？

还可以按升序对帐户代码列进行排序，以便我的预期输出应该是:

 Account Code   Debit      Credit
 13101          350000.00   
 22601          3000.00 
 21201                     40000.00
 22201                     0.00
 41101                     3000.00
 -----------------------------------
 Total          353000.00  43000.00

Answer 1

我最终想通了，我所需要做的就是循环数组并收集另一个变量中的增量。然后在我的表中回显该变量作为总计。

function displayTrialBalance(){

require_once('Connections/mpcs_dbConn.php');

mysql_select_db($database_dbConn, $dbConn);

// declare query variables
$query_diff_empty = "SELECT Account_Code, sum(Amount) AS difference FROM journal2 GROUP BY Account_Code ORDER BY difference asc, Account_Code asc";


// check if user wants empty


// query DB
$amounts = mysql_query($query_diff_empty, $dbConn) or die(mysql_error());
$row_amounts = mysql_fetch_array($amounts);
$totalRows_users = mysql_num_rows($amounts);

if ($row_amounts){

function debit($arr){
return ($arr < 0);
}

function credit($arr){
return ($arr >= 0);
}


echo '<table align="center" border="0" bordercolor="#666666" cellpadding="5" cellspacing="3">
        <tr class="tableTop">
        <td>Account Code</td>
        <td>Debit</td>
        <td>Credit</td>';

// get total debit
foreach((array_filter($diff, "debit")) as $x){
$totalDebit += $x;
}
// get total credit
foreach((array_filter($diff, "credit")) as $y){
$totalCredit += $y;
}

do {

$diff = array($row_amounts['difference']);

        echo '
        </tr>
        <tr class="tableButtom">
        <td>'.$row_amounts['Account_Code'].'</td>
        <td>';
        foreach((array_filter($diff, "debit")) as $new){
        echo abs($new).'.00';}
        echo '</td>
        <td>';
        foreach((array_filter($diff, "credit")) as $new2){
        echo $new2;
        }
        echo '</td>';


}while ($row_amounts = mysql_fetch_array($amounts));
echo '</tr>
        <tr class="tableButtom">
        <td>';
        echo 'Total';
        echo '</td>
        <td>';
        echo abs($totalDebit).'00';
        echo '</td>
        <td>';
        echo $totalCredit.'00';
        echo '</td>
        </tr>
</table>';
}   
}