Jump to content
phppup

checkbook balance scripting

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.

Share this post


Link to post
Share on other sites

That code snippet without any context is as much use as a chocolate teapot.

What is the table structure?

How are attempting to get the balance?

Share this post


Link to post
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]

Share this post


Link to post
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.

Share this post


Link to post
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   |
+-------+--------+---------+

 

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

All that would be required is to get the SUM(amount) for those transactions up to that one (as @requinix has already mentioned.)

Share this post


Link to post
Share on other sites

Shouldn't this give me a result?

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

 

Share this post


Link to post
Share on other sites

No, you haven't executed any query, and you haven't even defined a string variable correctly.

Share this post


Link to post
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   |
+-------+---------------------+--------+---------+

 

Share this post


Link to post
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'] ;
     }
}

 

Share this post


Link to post
Share on other sites

Quick thought - Do you not store the check number with each row?  Or is that the cleverly named 'id' column?

Share this post


Link to post
Share on other sites

UPDATE on the way...

Edited by phppup
mistake

Share this post


Link to post
Share on other sites
5 minutes ago, phppup said:

I added to the top of my script, but got no additional return information

That turns off error reporting. Try adding the following

error_reporting(E_ALL);
ini_set('display_errors', 1);

 

Share this post


Link to post
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?

Share this post


Link to post
Share on other sites

select max(id)?   You did that in your very first post, no?

OR how about this:

	SELECT meet_date FROM `meeting_mins`
	WHERE 1
	order by meet_date desc
	limit 2
	

This selects the 2 highest dates in my table.

Edited by ginerjm

Share this post


Link to post
Share on other sites

My first post indicates that I am having TROUBLE with MAX(id)

Share this post


Link to post
Share on other sites

Well the line of code I gave you does the trick.

Share this post


Link to post
Share on other sites

OK, used ginerjm's code and got results, so at least I have eliminated some basic concerns.

Still curious why MAX(id) is not working for me when I try to SELECT results.

Share this post


Link to post
Share on other sites
3 minutes ago, phppup said:

Still curious why MAX(id) is not working for me when I try to SELECT results.

If you show us some code we might be able to tell you.

Share this post


Link to post
Share on other sites

I've tried the following alterations

$sql = "SELECT MAX(id) FROM persons" ;

$sql = "SELECT * FROM persons WHERE 'id=MAX(id)'";

$sql = "SELECT * FROM persons WHERE 'MAX(id)>0'";

and variations of them

Share this post


Link to post
Share on other sites

All those lines do is assign values to $sql string variable.

Do you expect that will magically execute the SQL query and return the result?

Share this post


Link to post
Share on other sites

which version of mysql are you using? your version might not support MAX()

try:

$sql = "select id from persons order by id desc limit 1;"

or:

mysqli_insert_id($link);

Edited by mconte

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.