更新运行余额

Question

I currently have this table in mysql. I would like to make a php script that will automatically update the balance like the one example B.

Example A
----+-------+-------------+---------+
| ID | Debit | Credit     |Balance  |
+----+-------+------------+---------+
|  1 |       | 35         |         |
|  2 | 65    | 0          |         |
|  3         | 35         |         |
|  4 | 65    | 0          |         |
|  5 | 65    | 0          |         |
|  6 | 65    | 0          |         |
-------------------------------------
Example B
----+-------+-------------+---------+
| ID | Debit | Credit     |Balance  |
+----+-------+------------+---------+
|  1 |       | 35         |  -35    |
|  2 | 65    | 0          |   30    |
|  3         | 35         |  -5     |
|  4 | 65    | 0          |   60    |
|  5 | 65    | 0          |  125    |
|  6 | 65    | 0          |  190    |
-------------------------------------

Here's what I tried:

 $sql = "
SELECT * 
  FROM tbl_journal 
 ORDER 
  BY date ASC
"; 

$conn = dbconnect(); 
$result = $conn->query($sql); 
while ($row = $result->fetch_assoc()) {
 $tbl_id = $row['tbl_id']; 
 $balance = $row['balance']; 
 $credit = $row['credit']; 
 $debit = $row['debit']; 
 echo "<pre>".$tbl_id."*".$row['balance']."</pre>"; 
 $balance = (($balance + $debit) - $credit); 
 $sql = "
UPDATE tbl_journal 
   SET balance = '$balance' 
 WHERE tbl_journal.tbl_id = $tbl_id"; 
$conn->query($sql); 
}

Thank you in Advance.

Answer 1

You can do this by initialising a variable, and then using that variable to keep a running total...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,debit INT NULL
,credit INT NULL
);

INSERT INTO my_table VALUES
(1,NULL,35),
(2,65,NULL),
(3,NULL,35),
(4,65,NULL),
(5,65,NULL),
(6,65,NULL);


SELECT id
     , debit
     , credit
     , @balance := @balance+(COALESCE(debit,credit*-1)) balance 
  FROM my_table
     , (SELECT @balance :=0) vars
 ORDER 
    BY id;
+----+-------+--------+---------+
| id | debit | credit | balance |
+----+-------+--------+---------+
|  1 |  NULL |     35 |     -35 |
|  2 |    65 |   NULL |      30 |
|  3 |  NULL |     35 |      -5 |
|  4 |    65 |   NULL |      60 |
|  5 |    65 |   NULL |     125 |
|  6 |    65 |   NULL |     190 |
+----+-------+--------+---------+

Actually, the way I've written this is slightly incorrect and can theoretically lead to an error, but in practice I've not yet managed to trigger said error, and I find this way easier to read.