How to include insert query inside for loop using php?

Question

User will enter like more than one payment card 1.00,cash 2.00,card 10,00,cash 20.00 etc...After these all values insert into payment_details table one by one along with current date.So after this i need to insert data to another table called moneybox table. Count of total cash and total card will store into money box group by current date.Always two rows ie; card and cash will be there in money table,going to total of cash and card will be store based on current date.

Answer 1

As far as I understand the requirements, this may help...

        <?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);
                    }

            }

        }        

    ?>