Jump to content
phppup

Running balance / checkbook

Recommended Posts

 

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?

Share this post


Link to post
Share on other sites

Give us a couple of clues.

 

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

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites

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 |
+----------------+------------+--------+---------+---------+

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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>

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.