Jump to content

checkbook balance scripting


phppup
 Share

Recommended Posts

I am trying to generate data similar to a running balance for a checkbook.

My approach (correct me if there is a better method) is to get the balance from the previous record ID and then add/subtract the new value and create a new total balance.

However, I am not havig succes with MAX(id) or last_insert_id  -- is one preferable over the other??

 if($result = mysqli_query($link, $sql)){
    if(mysqli_num_rows($result) > 0){
 $sql = "SELECT MAX(id) FROM persons";
 echo "<br /<br />"; echo $sql;

Please assist.

Link to comment
Share on other sites

Currently very simple to get a handle on the approach,

id, name, add, sum

I've INSERTED 3 or 4 rows with random data.

Now I want to access the LAST value for SUM.

Afterward, I expected to INSERT the ADD value to the LAST (sum-1) to create a running balance.  [There will be no subtractions, although I assume a negative number would thereby reduce the SUM]

Link to comment
Share on other sites

Sounds kinda complicated, doesn't it? Having to store the current balance for each row. And it's only correct at the moment in time when the row was entered. So if you want to make a change you have to go find the most recent transaction to see what the balance was after it finished.

Don't do it.

For the moment, the balance is the sum of all transactions. As in do a query that gets a SUM(add) of all the transaction. That's your balance.

Link to comment
Share on other sites

I'd advise against storing any derived data values such as the balance.

Calculate it when rerquired

SELECT * FROM pup;
+----+-------+--------+
| id | name  | amount |
+----+-------+--------+
|  1 | Curly |  10.00 |
|  2 | Larry |  20.00 |
|  3 | Mo    |  15.00 |
|  4 | Peter |  -5.00 |
|  5 | Paul  |  10.00 |
|  6 | Mary  |   5.00 |
+----+-------+--------+

SELECT name 
     , amount
     , FORMAT(@balance := @balance + amount, 2) as balance
FROM pup 
     JOIN (SELECT @balance:=0) as init;
     
+-------+--------+---------+
| name  | amount | balance |
+-------+--------+---------+
| Curly |  10.00 | 10.00   |
| Larry |  20.00 | 30.00   |
| Mo    |  15.00 | 45.00   |
| Peter |  -5.00 | 40.00   |
| Paul  |  10.00 | 50.00   |
| Mary  |   5.00 | 55.00   |
+-------+--------+---------+

 

Link to comment
Share on other sites

I understand.

However, what if I wanted to know the balance after Carol made her deposit?  Hypothetically, isn't it simpler to print the calculated data result for a given period than to run the computation for each transaction at that point?

I appreciate the help and shared knowledge.

Thanks.

Link to comment
Share on other sites

To do it properly the transaction require a timestamp

+----+-------+--------+---------------------+
| id | name  | amount | transaction_date    |
+----+-------+--------+---------------------+
|  1 | Curly |  10.00 | 2020-01-01 10:00:00 |   Jan
|  2 | Larry |  20.00 | 2020-01-15 12:30:00 |
|  3 | Mo    |  15.00 | 2020-02-01 09:00:00 |   Feb
|  4 | Peter |  -5.00 | 2020-02-01 10:30:00 |
|  5 | Paul  |  10.00 | 2020-02-02 11:30:00 |
|  6 | Mary  |   5.00 | 2020-02-02 12:15:00 |
+----+-------+--------+---------------------+

Suppose we want to list this month's transactions only but showing the correct running balance total. Our initialise subquery needs to get the opening balance as at the end of January (ie sum of amount column where the date is prior to start of Feb). We then list the records since the start of February

SELECT name
     , transaction_date 
     , amount
     , FORMAT(@balance := @balance + amount, 2) as balance
FROM pup 
     JOIN (SELECT 
               @balance := (SELECT SUM(amount) FROM pup
                            WHERE transaction_date < '2020-02-01 00:00:00')
          ) as init
WHERE transaction_date >= '2020-02-01 00:00:00';

+-------+---------------------+--------+---------+
| name  | transaction_date    | amount | balance |
+-------+---------------------+--------+---------+
| Mo    | 2020-02-01 09:00:00 |  15.00 | 45.00   |
| Peter | 2020-02-01 10:30:00 |  -5.00 | 40.00   |
| Paul  | 2020-02-02 11:30:00 |  10.00 | 50.00   |
| Mary  | 2020-02-02 12:15:00 |   5.00 | 55.00   |
+-------+---------------------+--------+---------+

 

Link to comment
Share on other sites

Still not getting the connection in PHP (although the SQL seems to work in the db)

$sql = "SELECT first_name , sum , FORMAT(@balance := @balance + sum, 2) as balance FROM persons JOIN (SELECT @balance:=0) as init WHERE id = 25";

result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {

        echo "TEST" . $row['id'] ;
     }
}

 

Link to comment
Share on other sites

YES, I realized my error and deleted my post.  Obviously turning error reporting OFF will provide NO assistance. Dumb mistake.

After a day away, I am reviewing my efforts.  I see that I need to loop through all the records to accumulate data.

At this point I guess my question (due to unexpected non-results) is how can I simply SELECT the MAX(id) row successfully?

Apparently, mysqli_insert_id works ONLY during an INSERT, but how can I obtain that row's data independently with SELECT?

Can I reference the second-to-last with that information minus 1?

Link to comment
Share on other sites

This thread is more than a year old.

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.

 Share

×
×
  • 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.