使用PHP SQL从账单中扣除金额

Question

Hi Guys I have been coding and can't have a good solution to this problem... Sample: From BILLING table there is 2 bills that is cost for January 1,500 and February 2,000 From PAYMENT table the client paid 2,000 and this amount should deducting to the Jan BILL and Feb Bill since 2,000 is greater than the amount of January so should be automatically deducting the 500 to the next BILL February or so on. Table

OUT PUT

AMOUNT PAID - 2,000

| MONTH     |   BILL    |    AMT PAID   |   BALANCE   |
-------------------------------------------------------
| January   |   1,500   |    2,000      |     -500    |
| February  |   2,000   |      500      |    1,500    |

QUERY

SQL BILLING
| MONTH | BILL  |
| JAN   | 1500  |
| FEB   | 2000  | 
$query = SELECT MONTH, BILL FROM billing
$amt_paid = 2000;
foreach($query as $row){
   $actual_paid = $amt_paid;
   $actual_bal -= $actual_paid;
   echo '<tr>';
   echo '<td>'.$row->MONTH.'</td>';
   echo '<td>'.$row->BILL.'</td>';
   echo '<td>'.$actual_paid.'</td>';
   echo '<td>'.$actual_bal.'</td>';
}

WRONG RESULT
| MONTH     |   BILL    |    AMT PAID   |   BALANCE   |
-------------------------------------------------------
| January   |   1,500   |    2,000      |     -500    |
| February  |   2,000   |    2,000      |        0    |

I want to show a exact amount and balance. please help, I hope you guess what I mean to say :D

Answer 1

-- You should have monthIndex or datetime in your table

select t0.MONTHNAME, t0.bill, t0.pay, (select sum(bill - pay) from billing t1 where t1.MONTHINDEX <= t0.MONTHINDEX) bal
from billing t0