使用循环更新db表

Question

`SI_ID is` the Auto Increment Field
`SI_Reg_No` i the student registration number 
`SI_Ins_NO` is the installment number 
`SI_Due_Date` is the due date of that installment 
`SI_Paid_Amount` is the paid amount of each installment

when my payment form button click i want to update the SI_Paid_Amount field.When i click on the payment field bellow table data is already loaded without SI_Paid_Amount.

$amount = 5000.00 // this is what i send to the db for update `SI_Paid_Amount`

if SI_paid_Amount should be update like bellow

From first SI_Ins_No if SI_Ins_amount is bigger than $amount it should update the Si_Paid_Amount field and it should stop the loop. like bellow

If the SI_Ins_Amount less than sending amount it should be update the 2nd SI_Ins_No related SI_Paid_Amount column.And also when sending the data to SI_Paid_Amount we want to check amount previous SI_Paid_Amounts according to SI_Ins_No.So my question is how i do this with php ???

Answer 1

I have spoken to this guy on chat and found out what he's after. Basically it's an table for students to pay off their installments, but they can pay in bulk or whatever.

Example: So I owe 3 instalments of 15000, 10000 and 5000. I pay £17000 and it deducts that 15000 from my 3 instalments based on the SI_Ins_No in ASC order. So it would add 5000 to amount paid in SI_ID #1 then 2000 to SI_ID #2

I've written a little solution to help

// $rows = "SELECT *, (`SI_Ins_Amount` - `SI_Paid_Amount`) as owed FROM `student_installments` WHERE (`SI_Ins_Amount` + `SI_Paid_Amount`) != 0 AND `SI_Reg_No` = 'COL/A-000041' ORDER BY `SI_Ins_NO` ASC";

$rows = array();
$rows[0]['SI_ID'] = 1;
$rows[0]['SI_Reg_no'] = 'COL/A-000041';
$rows[0]['SI_Ins_Amount'] = '15000';
$rows[0]['SI_Paid_Amount'] = '0';
$rows[0]['owed'] = '15000';
$rows[1]['SI_Ins_NO'] = '1';

$rows[1]['SI_ID'] = 2;
$rows[1]['SI_Reg_no'] = 'COL/A-000041';
$rows[1]['SI_Ins_Amount'] = '10000';
$rows[1]['SI_Paid_Amount'] = '0';
$rows[1]['owed'] = '10000';
$rows[1]['SI_Ins_NO'] = '2';

$rows[2]['SI_ID'] = 2;
$rows[2]['SI_Reg_no'] = 'COL/A-000041';
$rows[2]['SI_Ins_Amount'] = '5000';
$rows[2]['SI_Paid_Amount'] = '0';
$rows[2]['owed'] = '5000';
$rows[1]['SI_Ins_NO'] = '3';

$amount = 27000;
foreach($rows as $r) {
    // There's money left
    if($r['owed'] - $amount < 0) {
        $paying = $r['owed'];
        $amount -= $r['owed'];
    } else {
        // No money left
        $paying = ($r['SI_Paid_Amount' + $amount);
        $amount -= $paying;
    }

    // If there's money left
    if($paying > 0) { 
       echo "UPDATE `student_installments` SET `SI_Paid_Amount` = '".$paying."' WHERE `SI_ID` = '".$r['SI_ID']."' 
";
   }
}

Please feel free to improve on this as it's just a quick crude method.

You can test this here

You'll need to add in your own stuff like checking the amount is positive, making sure they don't pay too much etc...