title: How to calculate running balance on scheduled loan repayment based on customer payment using php and mysql
body: <p>I am trying to update a scheduled loan repayment MySql table with a customer loan payment. </p>
  
  <p>The code below logic is what I have tried but I don't seem to get it right. 
  I will Appreciate Any help.
  Thanks in advance.</p>
  
  <pre><code>$sql11 = "SELECT * FROM loan_paymentss WHERE borrower_loan_id = '$loan_id' ";
  
  $result11 = mysqli_query($link,$sql11);
  $count = mysqli_num_rows($result11);
  while ($row2 = mysqli_fetch_array($result11)) {
  
      $id = $row2['id'];
      $sql = "SELECT * FROM loan_paymentss WHERE borrower_loan_id = '$loan_id' LIMIT 1";
      $result = mysqli_query($link,$sql);
      while ($row1 = mysqli_fetch_array($result)) {
  
          $amt = $row1['amount'];
          $pmnt = $row1['pmnt_made'];
          $r = $amt-$amt_paid;
          for ($i=0; $i &lt;= $count ; $i++) {
  
              if ($r == 0) {
  
                  $pmnt = $amt_paid;
                  $sql1 = "UPDATE loan_paymentss SET pmnt_made = '".$pmnt."', status='PAID' WHERE id = '$id' ";
                  mysqli_query($link,$sql1);
  
              }elseif ($r &gt; 0 ) {
                  $pmnt = $amt_paid;
                  $sql1 = "UPDATE loan_paymentss SET pmnt_made = '".$pmnt."', status='UNPAID' WHERE id = '$id' ";
                  mysqli_query($link,$sql1);
  
              }else{
                  if ( stristr($r,'-') ) {
                      $sql2 = "UPDATE loan_paymentss SET pmnt_made = '$r'  WHERE id = (select max(id) from loan_paymentss where id &lt; '$x')";
                      mysqli_query($link,$sql2);
                      $x++;
                  }
              }
          }
      }
  }
  </code></pre>
  
  <p>I have a form that accepts user payment and user can pay before or after loan payment scheduled but will be penalised for late payment after loan maturity date.    </p>
  
  <pre><code>id|borrower_id|borrower_loan_id|payment_sched|  payment_number|amount| pmnt_made|status
  ---------------------------------------------------------------------------------------
  1 | 34        |l_5261          |10/10/2019   |  1             |400   |          |UNPAID
  2 | 34        |l_5261          |10/11/2019   |  2             |400   |          |UNPAID
  
  
  When a customer pays $500 
  Expected Output
  
  1 | 34        |l_5261          |10/10/2019   |  1             |400   |   400     |PAID
  2 | 34        |l_5261          |10/11/2019   |  2             |400   |   100     |UNPAID
  
  When a customer pays $300
  
  1 | 34        |l_5261          |10/10/2019   |  1             |400   |    300    |UNPAID
  2 | 34        |l_5261          |10/11/2019   |  2             |400   |    0.00   |UNPAID
  
  when a customer pays all installment @ a go
  1 | 34        |l_5261          |10/10/2019   |  1             |400   |    400    |PAID
  2 | 34        |l_5261          |10/11/2019   |  2             |400   |    400    |PAID
  </code></pre>
  
  <p>What am trying to achieve is whenever a customer pays a loan based </p>
  
  <p>on scheduled payment(monthly installment eg. monthly installment of
   $400 for two terms)which a customer can decide to pay before or after </p>
  
  <p>the scheduled date (eg. 10/10/2019 and 10/11/2019) so I want to update column pmnt_made(payment made withholds 0.00 initially) with the amount paid by customer and change status to "PAID" based on the ff conditions... </p>
  
  <p>Instances
  If first payment made (pmnt_made) is equal to amount(scheduled amount to be paid) update status to "PAID"
  OR
  If the first payment made (pmnt_made) is greater than  amount(scheduled amount to be paid) update status to "PAID" and subtract(amount - pmnt_made = -r) place value r in the next scheduled payment i.e 2nd scheduled pmnt_made, if r matches 
  the first condition, update status to "PAID",if not wait for next payment<br>
  OR
  if pmnt_made is less than amount(scheduled amount to be paid) don't update status but upon next payment(pmnt_made + new payment(pmnt_made) )if still less than the amount don't update status but if equal to (amount) go ahead and update status... </p>