How to include insert query inside for loop using php?

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.

payment table looks like

card 1.00 2018-10-18
cash 2.00 2018-10-18
card 10.00 2018-10-18
cash 20.00 2018-10-18

insert record into “moneybox” table like (only two rows)

cash  11.00 2018-10-18 // all cash sum value 11 of 2018-10-18
card  22.00 2018-10-18 // all card sum value 22 of 2018-10-18
cash  200.00 2018-10-19
card  370.00 2018-10-19

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.

Here is my php code for inserting and updating

    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'];
    }
	
	
    $cashMode = 0; 
    $cardMode = 0;
	
    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 . '");
    ';
	
	if($last_moneybox_created_date != $todayDate)   {
                $cashMode = 0;
                $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_multi_query($link, $query)) {
            echo 'paydetails Inserted';            
        } else {
            echo "Error: " . $query . "<br>" . mysqli_error($link);
        }
    
 
}

Actually i want

  1. when user enter card:10:00 (if $last_moneybox_created_date != $todayDate insertion will happen)insert record card:10:00.

2.When user enter card:10:00 cash:20:00 (if $last_moneybox_created_date != $todayDate insertion will happen)insert record first row card:10:00 and second row cash 20:00.

3.When user enter card 10:00 cash:20:00 card 11:00 cash 30:00 (if $last_moneybox_created_date != $todayDate insertion will happen)insert record first row card:21:00 and second row cash 50:00.

  1. When user enter card 10:00 card 10:00 (if $last_moneybox_created_date != $todayDate insertion will happen)insert record first row card:20:00

  2. When user enter cash 50:00 cash 50:00 (if $last_moneybox_created_date != $todayDate insertion will happen)insert record first row cash:100:00

then will see when updation will happen

6.When user enter cash 1000:00 (if $last_moneybox_created_date == $todayDate
update will happen)update record where current date belongs to cash

7.When user enter card 700:00 (if $last_moneybox_created_date == $todayDate update will happen)update record where current date belongs to card

Can you show the full SQL text generated by your problem scenario? echo $query;

Tell us about the real problem you are trying to solve instead of your attempt at solving it.

I have updated my code above.plz check it.Now what happens in the code,When user enter card:10 cash:20:00 card:4 cash 9 then insertion is happening in moneybox looks like card 14:00 cash 29:00 with current date.This working fine.But problem is when next time(same in today date) user enter card:2:00 again its inserting as new record to moneybox instead of updating card 16:00(last card value 14 + new value 2 = 16will show).Insert will happen only when $last_moneybox_created_date doesnot match with today’s date

This is a bad database design, resulting in a huge amount of unnecessary code/queries, which will result in data synchronization problems between the source data and the derived data.

All you need to do is insert the source data into the payment_details table. To get a summery/report of that data, just query against that data when needed. To get a summery for any date or range of dates, just use SELECT created, paymentMode, SUM(Amount) as total … with a WHERE clause that matches the date or range of dates you want and a GROUP BY created, paymentMode in the query.

You should also use a prepared query when supplying external/unknown data values to the query and switch to use the much simpler php PDO extension. Also, a prepared query will save about 5% of the execution time when repeatedly executing the same query, because you will prepare it once, then just execute it for each set of values.

1 Like
Sponsor our Newsletter | Privacy Policy | Terms of Service