php - 使用 php 循环减少数据库的多个列和行中的值

Question

我有一个客户需要在现有解决方案中添加新功能。

他们将财务数据存储在 MySQL 数据库中，其中包含以下各列。这些都是未付款项，有些有罚款，有些则没有。

ID | money_id | principal | interest | penalty | paid_pri | paid_int | paid_pen

 1      1         50          60         5          0          0          0
 2      1         55          55         5          0          0          0
 3      1         60          50         0          0          0          0

在上述情况下，客户需要对 ID 1 和 2 支付两项罚款，如果客户支付$200，则需要先支付利息和罚金支付本金。支付所有未付利息和罚款后本金可以还清。

支付 200 美元后，新表应如下所示。

ID | money_id | principal | interest | penalty | paid_pri | paid_int | paid_pen

 1      1         50          60         5          25         60          5
 2      1         55          55         5          0          25          5
 3      1         60          50         0          0          50          0

这是因为200的分配是有返回的，60 + 5 + 2 5 + 5 + 50然后第一本金可享受 25 美元折扣。

编辑 MARC B 的观点:

how can i "Reduce values in several columns and rows of a database using a php loop"

我必须使用从 mysql 返回的对象。

Array
(
    [0] => Array
        (
            [id] => 1
            [interest] => 60
            [interestPaid] => 0
            [penalty] => 5.00
            [penaltyPaid] => 0
            [principal] => 50
            [principalPaid] => 0.00
        )

    [1] => Array
        (
            [id] => 2
            [interest] => 55
            [interestPaid] => 739.10
            [penalty] => 5
            [penaltyPaid] => 739.10
            [principal] => 55
            [principalPaid] => 0.00
        )
)

我必须使用它来循环数据库，它称为 $ payment_array。

到目前为止我的代码，记住他们有一个扩展 mysql ($this) 的类

$amount_to_pay = 200;

foreach ($payment_array as $ordered_repayment) {
            if ($amount_to_pay > 0) {
            $interest_balance = $ordered_repayment['interest'] - $ordered_repayment['interestPaid'];
            if ($interest_balance > 0) {
                $sql = "update schedules set paid_int = paid_int + '" . $interest_balance . "' where id = '" . $ordered_repayment['id'] . "'";
                $amount_to_pay -= $interest_balance;
                $this->query($sql);
            }
            $penalty_balance = $ordered_repayment['penalty'] - $ordered_repayment['penaltyPaid'];
            if ($penalty_balance > 0) {
                $sql = "update schedules set paid_pen = paid_pen + '" . $penalty_balance . "' where id = '" . $ordered_repayment['id'] . "'";
                $amount_to_pay -= $penalty_balance;
                $this->query($sql);
            }
        }
    }

    foreach ($payment_array as $ordered_repayment) {
        if ($amount_to_pay > 0) {
            $capital_balance = $ordered_repayment['principal'] - $ordered_repayment['principalPaid'];
            if ($capital_balance > 0) {
                $sql = "update schedules set paid_pri = paid_pri + '" . $capital_balance . "' where id = '" . $ordered_repayment['id'] . "'";
                $amount_to_pay -= $capital_balance;
                $this->query($sql);
            }
        }
    }

希望这更详细一点;)

Answer 1

我会稍微修改一些内容，以避免某些行需要进行 2 次更新:-

<?php

$amount_to_pay = 200;

foreach ($payment_array as $ordered_repayment) 
{
    switch (true)
    {
        case $amount_to_pay <= 0 :
            break;
        case $amount_to_pay > ($ordered_repayment['interest'] + $ordered_repayment['penalty']) :
            $sql = "UPDATE schedules 
                    SET paid_int = paid_int + " . $ordered_repayment['interest'] . ", 
                    paid_pen = paid_pen + " . $ordered_repayment['penalty'] . ", 
                    interest = 0, 
                    penalty = 0 
                    WHERE id = " . $ordered_repayment['id'] . "";
            $amount_to_pay -= ($ordered_repayment['interest'] + $ordered_repayment['penalty']);
            break
        case $amount_to_pay > $ordered_repayment['penalty']) :
            $sql = "UPDATE schedules 
                    SET paid_int = paid_int + " . ($amount_to_pay - $ordered_repayment['penalty']) . ", 
                    paid_pen = paid_pen + " . $ordered_repayment['penalty'] . ", 
                    interest = " . ($ordered_repayment['interest'] - ($amount_to_pay - $ordered_repayment['penalty'])) . ", 
                    penalty = 0 
                    WHERE id = " . $ordered_repayment['id'] . "";
            $amount_to_pay = 0;
            break
        default :
            $sql = "UPDATE schedules 
                    SET paid_pen = paid_pen + " . $amount_to_pay . ", 
                    penalty = " . ($ordered_repayment['penalty'] - $amount_to_pay) . " 
                    WHERE id = " . $ordered_repayment['id'] . "";
            $amount_to_pay = 0;
            break
    }
}

foreach ($payment_array as $ordered_repayment) 
{
    if ($amount_to_pay > 0) 
    {
        $capital_balance = $ordered_repayment['principal'] - $ordered_repayment['principalPaid'];
        if ($capital_balance > 0) 
        {
            $sql = "UPDATE schedules 
                    SET paid_pri = paid_pri + " . $capital_balance . " 
                    WHERE id ='" . $ordered_repayment['id'] . "";
            $amount_to_pay -= $capital_balance;
            $this->query($sql);
        }
    }
}