PHP - Calculate running balance

Question

I am trying code in php with mysql and I want to calculate running balance based on the type of transaction. My database table is as per below

id  |  amt    |  type    |    date
----+---------+----------+-----------
 1  |   70000 |    Cr    | 01-01-2022
 2  |    8000 |    Dr    | 01-01-2022
 3  |   60000 |    Cr    | 02-01-2022
 4  |   50000 |    Dr    | 02-01-2022
 5  |   90000 |    Cr    | 03-01-2022
 6  |   28000 |    Dr    | 03-01-2022

And I want the result as per below, if type = Cr then ADD and if type = Dr then SUBTRACT and auto calcuate running BALANCE where initial value to be ZERO

id  |    Dr    |    Cr   |     date    |  balance
----+----------+---------+-------------+----------
 1  |       0  |  70000  |  01-01-2022 |   70000
 2  |    8000  |      0  |  01-01-2022 |   62000
 3  |       0  |  60000  |  02-01-2022 |  122000
 4  |   50000  |      0  |  02-01-2022 |   72000
 5  |       0  |  90000  |  03-01-2022 |  162000
 6  |   28000  |      0  |  03-01-2022 |  134000

The following is the PHP script I'm currently using

<table>
<tr><th>Date</th><th>IN</th><th>OUT</th><th>Balance</th></tr>
<?php
$conn=mysqli_connect("details hidden");

$sql="SELECT * FROM table_name WHERE type IN ('Dr', 'Cr') ORDER BY date DESC";
$query=mysqli_query($conn, $sql);
while ($row=mysqli_fetch_array($query)) {

echo '<tr>';
echo '<td>'.$newdate = date('d-m-y', strtotime($row['date'])).'</td>';

if ($row['type']==Dr){
echo '<td>'.$row['amt'].'</td>';
echo '<td></td>';}

elseif ($row['type']==Cr){
echo '<td></td>';
echo '<td>'.$row['amt'].'</td>';}

else {
echo '<td></td>;
echo '<td></td>;}
echo '</tr>';
}
?>
</table>

Any help would be helpful.

Answer 1

You can use the sum window function to get the result you want:

select id, 
case when `type` = 'Dr' then `amt` else 0 end as Dr,
case when `type` = 'Cr' then `amt` else 0 end as Cr,
date,
sum(case when `type` = 'Dr' then -`amt` when `type` = 'Cr' then `amt` end) over(order by date rows unbounded preceding) as balance
from table_name;

Fiddle