Running balance / checkbook

[phppup]

 

How can I calculate a running balance using PHP?

I have a table that will record both sales and credits and would like to display a cumulative balance. It seems redundant to have to revisit every individual transaction and recalculate the current balance each time it is required.

How can I set up an additional column named "balance" and use it as a reference point against the newest entry for the specific customer?

What is the best method of achieving a checkbook-style method?

[Barand]

Give us a couple of clues.

 

• How is your table structured?
• How do want the output to look?

[phppup]

The table (which has not been coded bc I realized this issue) would simply likely refer back to an identifying retirement 'id' with rows 'sale'(a positive number), 'credit' (a negative), and 'current_balance' (for the running total).

Obviously, I could always SUM both columns and do basic math tho calculate a total.

My problem is how to get to the last balance (in the prior row corresponding to the particular 'id' and running a continuously updated cycle.

In Excel it would be E2+C3-D3 to equal E3.

My problem is getting to E2 (the last/previous sale record for this customer).

[Barand]

Your table simply needs to be something like this

mysql> SELECT * FROM transaction;
+----------------+------------+--------+
| transaction_id | transdate  | amount |
+----------------+------------+--------+
|              1 | 2018-03-21 |  50.00 |
|              2 | 2018-03-31 |  40.00 |
|              3 | 2018-04-02 | -30.00 |
|              4 | 2018-04-03 | -20.00 |
|              5 | 2018-04-04 | 100.00 |
|              6 | 2018-04-05 | -10.00 |
+----------------+------------+--------+

This next bit could be done simply in PHP, but as you posted in the MySQL forum I assume you want an SQL solution

SELECT transaction_id
     , transdate
     , CASE 
        WHEN amount >= 0 THEN amount ELSE ''
       END as Sales 
     , CASE 
        WHEN amount < 0 THEN -amount ELSE ''
       END as Credits 
     , @bal := @bal + amount as balance
FROM transaction
     JOIN ( SELECT @bal:=0) openbal;

+----------------+------------+--------+---------+---------+
| transaction_id | transdate  | Sales  | Credits | balance |
+----------------+------------+--------+---------+---------+
|              1 | 2018-03-21 | 50.00  |         |      50 |
|              2 | 2018-03-31 | 40.00  |         |      90 |
|              3 | 2018-04-02 |        |  30.00  |      60 |
|              4 | 2018-04-03 |        |  20.00  |      40 |
|              5 | 2018-04-04 | 100.00 |         |     140 |
|              6 | 2018-04-05 |        |  10.00  |     130 |
+----------------+------------+--------+---------+---------+

[phppup]

Ummm, no. I posted originally in PHP and the post got moved to this forum. I would prefer a PHP solution (but will try this for educational purposes).

Still would like the PHP method.

Thanks for the help.

[Barand]

Ummm, no. You posted it in both forums at the same time. One got locked as cross-posting is not allowed.

 

To do it it in php, just replicated the calculations in the query.

[Barand]

Something like this

<?php
include 'db_inc.php';
$db = pdoConnect('test');      // connects to "test" database

$statementdate = '2018-04-01';

//
// get the opening balance for this month's statement
//
$stmt = $db->prepare("SELECT 
                        SUM(amount) as balbf
                      FROM transaction
                      WHERE transdate < ? ");
$stmt->execute( [$statementdate] );
$balbf = $stmt->fetchColumn();
//
// get transaction data
//
$stmt = $db->prepare("SELECT date_format(transdate, '%b %D, %Y') as date
                           , amount
                      FROM transaction
                      WHERE transdate >= ?
                      ORDER BY transdate");
$stmt->execute( [$statementdate] );
$tdata = '';
$bal = $balbf;
foreach ($stmt as $r) {
    $bal += $r['amount'];                      // accumulate balance total
    $tdata .= "<tr><td>{$r['date']}</td>" ;
    if ($r['amount'] >= 0) {
        $amt = number_format($r['amount'], 2);
        $tdata .= "<td class='ra'>$amt</td><td></td><td class='ra'>".number_format($bal, 2)."</td></tr>\n";
    } else {
        $amt = number_format(-$r['amount'], 2);
        $tdata .= "<td></td><td class='ra'>$amt</td><td class='ra'>".number_format($bal, 2)."</td></tr>\n";
    }
}
?>
<html>
<head>
<meta name="generator" content="PhpED 18.0 (Build 18044, 64bit)">
<title>Sample Statement</title>
<style type="text/css">
    table {
        width: 600px;
        border-collapse: collapse;
    }
    td, th {
        padding: 5px;
    }
    .ra {
        text-align: right;
    }
</style>
</head>
<body>
<table border='1'>
    <tr><th>Date</th><th>Sales</th><th>Credits</th><th>Balance</th></tr>
    <tr><th colspan='3' class='ra'>Opening balance</th><th class='ra'><?=$balbf?></th></tr>
    <?=$tdata?>
</table>
</body>
</html>