php - 如何使用 php 在 for 循环中包含插入查询？

Question

用户将输入不止一张支付卡 1.00、现金 2.00、卡 10,00、现金 20.00 等......在这些所有值与当前日期一一插入到 payment_details 表之后。所以在此之后我需要将数据插入到另一个表称为moneybox 表。总现金和总卡计数将按当前日期存储到钱箱组中。总是两行，即；卡和现金将在钱表中，将根据当前日期存储现金和卡的总和。

标签： phpmysqli

Answer 1

据我了解要求，这可能会有所帮助...

        <?php


        if (isset($_POST["getamount"])) {
            $getinvoiceid    = $_POST['getinvoiceid'];
            $getstorepaymode = $_POST['getstorepaymode'];
            $getamount       = $_POST['getamount'];

            $sql1    = "select date from moneybox order by ID desc limit 1";
            $result1 = mysqli_query($link, $sql1);
            $row1    = mysqli_fetch_array($result1);
            //echo json_encode($row1);

            $last_moneybox_created_date = $row1['date'];

            $sqlclosebalcash     = "select closing_balance from moneybox where date='$last_moneybox_created_date' and type='cash'";
            $resultclosebal_cash = mysqli_query($link, $sqlclosebalcash);
            $rowclosebal_cash    = mysqli_fetch_array($resultclosebal_cash);
            //echo json_encode($row1);
            //$last_moneybox_closingbalanacecash = $rowclosebal_cash['closing_balance'];

            $sqlclosebalcard     = "select closing_balance from moneybox where date='$last_moneybox_created_date' and type='bank'";
            $resultclosebal_card = mysqli_query($link, $sqlclosebalcard);
            $rowclosebal_card    = mysqli_fetch_array($resultclosebal_card);

            //$last_moneybox_closingbalanacecard = $rowclosebal_card['closing_balance'];

            $tz        = 'Asia/Dubai'; // your required location time zone.
            $timestamp = time();
            $dt        = new DateTime("now", new DateTimeZone($tz)); //first argument "must" be a string
            $dt->setTimestamp($timestamp); //adjust the object to correct timestamp
            $todayDate = $dt->format('Y-m-d');

            if ($rowclosebal_cash['closing_balance'] == '') {
                $last_moneybox_closingbalanacecash = "0.00";
            } else {
                $last_moneybox_closingbalanacecash = $rowclosebal_cash['closing_balance'];
            }
            if ($rowclosebal_card['closing_balance'] == '') {
                $last_moneybox_closingbalanacecard = "0.00";
            } else {
                $last_moneybox_closingbalanacecard = $rowclosebal_card['closing_balance'];
            }

            for ($count = 0; $count < count($getamount); $count++) {
                $payamt_clean          = $getamount[$count];
                $getstorepaymode_clean = $getstorepaymode[$count];
                date_default_timezone_set('Asia/Dubai');
                $created = date("y-m-d H:i:s");    

                $query = 'INSERT INTO payment_details (invoiceID,paymentMode,Amount,created)
                                VALUES ("' . $getinvoiceid . '" , "' . $getstorepaymode_clean . '", "' . $payamt_clean . '", "' . $created . '");
            ';

             mysqli_query($link, $query);   



            // check whether card or cash or both rows are already there or not
            $sql1    = "select * from moneybox WHERE date='$todayDate' AND type='cash' ";
            $resultcash = mysqli_query($link, $sql1);
            if(mysqli_num_rows($resultcash)) {
                $cashMode = 1;
            }
            else {
                $cashMode = 0;
            }

            $sql1    = "select * from moneybox WHERE date='$todayDate' AND type='bank' ";
            $resultcard = mysqli_query($link, $sql1);
            if(mysqli_num_rows($resultcard)) {
                $cardMode = 1;
            }
            else {
                $cardMode = 0;
            }                    


            $cal_closingbalancecash = $last_moneybox_closingbalanacecash - $payamt_clean;
            $cal_closingbalancecard = $last_moneybox_closingbalanacecard - $payamt_clean;


            switch($getstorepaymode_clean) {
                        case "CASH":
                            if($cashMode === 0) {
                                echo 'Different Date cash'; //insert happen based on the type
                                $last_moneybox_created_date = $todayDate;
                                $cashMode = 1;

                                $query = "INSERT INTO moneybox (type,inflow,date) 
                                 VALUES ('cash','$payamt_clean','$todayDate');";                      
                            }
                            else {
                                echo 'Same Date cash'; //update happen based on type and date
                                $query = "UPDATE moneybox SET 
                                        inflow = inflow + $payamt_clean, 
                                        closing_balance= opening_balance + inflow - outflow 
                                        WHERE type = 'cash' and date = '$todayDate';";                                           
                            }
                        break;

                        case "CARD":
                            if($cardMode === 0) {
                                echo 'Different Date card'; //insert happen based on the type
                                $last_moneybox_created_date = $todayDate;
                                $cardMode = 1;

                                $query = "INSERT INTO moneybox (type,inflow,date) 
                                 VALUES ('bank','$payamt_clean','$todayDate');";                      
                            }
                            else {
                                echo 'Same Date card'; //update happen based on type and date
                                $query = "UPDATE moneybox SET 
                                        inflow = inflow + $payamt_clean, 
                                        closing_balance= opening_balance + inflow - outflow 
                                        WHERE type = 'bank' and date = '$todayDate';";                                           
                            }                     
                        break;  

                    } // end switch case

                    if (mysqli_query($link, $query)) {
                        echo 'paydetails Inserted';      
                    }
                    else {
                        echo "Error: " . $query . "<br>" . mysqli_error($link);
                    }

            }

        }        

    ?>