Question 113538 in Mysql Question posted in
The official MySQL documentation can be found here.

php ajax mysql update using for loop taking more time to execute what's good practice?

here i need update more than 100s of data at time. this query works for me but its too slow at least it take 15 to 20 seconds to execute.
i have tried few things but failed
any help appreciated. iam still learner .

<?php
include "../connection.php";
if (isset($_POST['close_val'])) {

    $item_cid = $_POST["item_cid"];
    $item_id = $_POST["item_id"];
    $op_date = $_POST["op_date"];
    $op_value = $_POST["op_value"];
    $close_date = $_POST["close_date"];
    $close_val = $_POST["close_val"];
    $PurchaseRate = $_POST["PurchaseRate"];
    $GeneralRate = $_POST["GeneralRate"];
    $WholeSaleRate = $_POST["WholeSaleRate"];
    $WholeSaleQty = $_POST["WholeSaleQty"];    
    for ($count = 0; $count < count($item_id); $count++) {
        $item_cid_clean = mysqli_real_escape_string($conn, $item_cid[$count]);
        $item_id_clean = mysqli_real_escape_string($conn, $item_id[$count]);
        $op_date_clean = mysqli_real_escape_string($conn, $op_date[$count]);
        $op_value_clean = mysqli_real_escape_string($conn, $op_value[$count]);
        $close_date_clean = mysqli_real_escape_string($conn, $close_date[$count]);
        $close_val_clean = mysqli_real_escape_string($conn, $close_val[$count]);
        
        $PurchaseRate_clean = mysqli_real_escape_string($conn, $PurchaseRate[$count]);
        $GeneralRate_clean = mysqli_real_escape_string($conn, $GeneralRate[$count]);
        
        $WholeSaleRate__clean = mysqli_real_escape_string($conn, $WholeSaleRate[$count]);
        $WholeSaleQty_clean = mysqli_real_escape_string($conn, $WholeSaleQty[$count]);
                
        $updatequery = "UPDATE table1 SET 
        
                            `item_cid` = '" . $item_cid_clean . "',
                            `item_id` = '" . $item_id_clean . "',
                            `op_date` = '" . $op_date_clean . "',
                            `op_value` = '" . $op_value_clean . "',
                            `close_date` = '" . $close_date_clean . "',
                            `close_val` = '" . $close_val_clean . "',
                            `PurchaseRate` = '" . $PurchaseRate_clean . "',
                            `GeneralRate` = '" . $GeneralRate_clean . "',
                            `WholeSaleRate` = '" . $WholeSaleRate__clean . "',
                            `WholeSaleQty` = '" . $WholeSaleQty_clean . "'
                
                    WHERE close_date='" . $close_date_clean . "' 
                    and `item_id` = '" . $item_id_clean . "'  ";
            
        mysqli_query($conn, $updatequery);

    }
    
    $return_arr = array('item_cid' => $item_cid, 'item_id' => $item_id, 'op_date' => $op_date, 'bar' => $item_type);
    echo json_encode($return_arr);
}
?>

this ajax used to initialize Array to post to php

var item_cid = [];
var item_id = [];
var op_date = [];
var op_value = [];
var close_date = [];
var close_val = [];
var PurchaseRate = [];
var GeneralRate = [];
var WholeSaleRate = [];
var WholeSaleQty = [];        
    
    
// Initializing array with Checkbox checked values
$("input[name='item_cid[]']").each(function() {
    item_cid.push(this.value);
});
$("input[name='item_id[]']").each(function() {
    item_id.push(this.value);
});
$("input[name='op_date[]']").each(function() {
    op_date.push(this.value);
});
$("input[name='op_value[]']").each(function() {
    op_value.push(this.value);
});
$("input[name='close_date[]']").each(function() {
    close_date.push(this.value);
});
$("input[name='close_val[]']").each(function() {
    close_val.push(this.value);
});
$("input[name='PurchaseRate[]']").each(function() {
    PurchaseRate.push(this.value);
});
$("input[name='GeneralRate[]']").each(function() {
    GeneralRate.push(this.value);
});
$("input[name='WholeSaleRate[]']").each(function() {
    WholeSaleRate.push(this.value);
});
$("input[name='WholeSaleQty[]']").each(function() {
    WholeSaleQty.push(this.value);
});


$.ajax({
        url: myurl,
        type: 'post',
        data: {
            item_cid: item_cid,
            item_id: item_id,
            op_date: op_date,
            op_value: op_value,
            close_date: close_date,
            close_val: close_val,
            PurchaseRate: PurchaseRate,
            GeneralRate: GeneralRate,
            WholeSaleRate: WholeSaleRate,
            WholeSaleQty: WholeSaleQty
        },
        dataType: 'JSON',
        success: function(response) {
            //success 
        }

here php ajax html code every step checks out correct its working only php part of mysql query in loops needs fix.

Tags:

2

Answers


  1. 0

    If you prepare the query outside your loop you will compile the query only once. If you use mysqli_query() inside the loop you have to compile the query once per update, thats a overhead you can do without as it is a complete waste of time.

    So write the query as a prepared parameterised query as below so you compile it once and then use it to execute the update multiple times. This wont make a magical improvement, but it shoudl be the way you always do your database quertying as it also preotects your database from SQL Injection Attack

    
        $sql = "UPDATE table1 
                SET `item_cid` = ?, `item_id` = ?, `op_date` = ?
                    `op_value` = ?, `close_date` = ?, `close_val` = ?, 
                    `PurchaseRate` = ?, `GeneralRate` = ?, 
                    `WholeSaleRate` = ?, `WholeSaleQty` = ?
                WHERE close_date=? and `item_id` = ?";

        $stmt = $conn->prepare($sql);

        foreach( $_POST["item_id"] as $idx => $itemid ) {
            // you may have to check the data types used in here
            // however mysqli does not normally care about that as much as you might hope
            $stmt->bind_param('ssssssssssss',
                                $_POST["item_cid"][$idx],
                                $itemid,
                                $_POST["op_date"][$idx],
                                $_POST["op_value"][$idx],
                                $_POST["close_date"][$idx],
                                $_POST["close_val"][$idx],
                                $_POST["PurchaseRate"][$idx],
                                $_POST["GeneralRate"][$idx],
                                $_POST["WholeSaleRate"][$idx],
                                $_POST["WholeSaleQty"][$idx],
                                $_POST["close_date"][$idx],
                                $_POST["item_id"][$idx]
                    );
            $stmt->execute();
        }

        // not sure what you are trying to do here 
        // as these are arrays and you do this after the loop
        $return_arr = array('item_cid' => $item_cid, 'item_id' => $item_id, 'op_date' => $op_date, 'bar' => $item_type);
        echo json_encode($return_arr);
    }
    Login or Signup to reply.
  2. 0

    If you don’t already have this, it should help with speed:

    INDEX(item_id, close_date)

    For further discussion please provide

    • SHOW CREATE TABLE
    • A sample query after it is constructed. Two samples, if that generates lots of separate UPDATEs. Be sure to have different values for item_id and close date unless one of them does not change.

    (You can leave out most of the columns.)

    I may be able to show you how to do all the Updates in a single IODKU. (But currently the requirements are vague.)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search