checkbook balance scripting

[phppup]

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.

[Barand]

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?

[phppup]

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]

[requinix]

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.

[Barand]

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

 

[phppup]

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.

[Barand]

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

[phppup]

Makes sense.

[phppup]

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;

 

[Barand]

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

[Barand]

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

 

[phppup]

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

 

[Barand]

Turn on your error checking.

[ginerjm]

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

[phppup]

UPDATE on the way...

Edited February 5, 2020 by phppup
mistake

[cyberRobot]

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

 

[phppup]

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?

[ginerjm]

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 February 5, 2020 by ginerjm

[phppup]

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

[ginerjm]

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

[phppup]

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.

[Barand]

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.

[phppup]

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

[Barand]

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?

[mconte]

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 February 5, 2020 by mconte