Insert Bulk Data in the DB in Background in PHP

I have a form which sends bulk messages to customers, on clicking submit button, it saves the messages in the DB before sending, but the inserting process takes about 2 mins to insert 3000 records, How can I reduce the insertion time or how can I process the data in the background to avoid the user waiting for the process to complete. I have tried several options on stack overflow with no success. I am on a shared hosting.
here is my code

<?php
if(isset($_POST['submit'])){
 $date = date("D, F d, Y h:i:sa");
 $phones = $_POST['recipient_phones']; //get phone numbers from textarea
 $phones = trim($phones,",\r\n\t\r\n/\s+/\0\x0B]/"); //do some regex
 $phones = multiexplode(array(","," ","\n","r\n",".","|",":"),$phones);//reformat and convert to array
 $sender = $_POST['sender_id']; //Get Sender ID input field
 $message = $_POST['message']; //Get Message input field
 $time = $_POST['sc_time']; //Get time input field



 ob_end_clean();
 ignore_user_abort();
 ob_start();
 header("Connection: close");
 // echo json_encode($out);
 header("Content-Length: " . ob_get_length());
 ob_end_flush();
 flush();



foreach($phones as $phone){

      $data = array("sender" => "$sender","phone" => "$phone", "message" => "$message", "user_id" => "$user_id","time_submitted" => "$date");
   
         $qry = Insert('crbsms_queue',$data);
  
   
   $_SESSION['msg']="10";

  echo "<script>location.href='$url?success=yes';</script>";
   exit
}



     # Insert Data 
    function Insert($table, $data){
    global $mysqli;
    //print_r($data);

    $fields = array_keys( $data );  
    $values = array_map( array($mysqli, 'real_escape_string'), array_values( $data ) );
    
   //echo "INSERT INTO $table(".implode(",",$fields).") VALUES ('".implode("','", $values )."');";
   //exit;  
    mysqli_query($mysqli, "INSERT INTO $table(".implode(",",$fields).") VALUES ('".implode("','", $values )."');") or die( mysqli_error($mysqli) );

}

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

Inserting 3000 rows is not a lot and it should not take too much time if you do it properly. You must remember that you should always use prepared statements. You can execute the same statement multiple times with different data. When you wrap the whole thing in a transaction it should be executed really fast.

// Start transaction
$mysqli->begin_transaction();

// prepared statement prepared once and executed multiple times
$insertStatement = $mysqli->prepare('INSERT INTO crbsms_queue(sender, phone, message, user_id, time_submitted) VALUES(?,?,?,?,?)');
$insertStatement->bind_param('sssss', $sender, $phone, $message, $user_id, $date);
foreach ($phones as $phone) {
    $insertStatement->execute();
}

// Save and end transaction
$mysqli->commit();

If this doesn’t improve the performance then it means you have a problem somewhere else. You need to profile and debug your application to find where the issue comes from.

Side note: Remember to enable mysqli error reporting, otherwise your transaction might not behave properly.

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply