Insert Bulk Data in the DB in Background in PHP

Question

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

}

**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 08 '20 at 18:15
Bear in mind that the code you have posted in the question has multiple issues including syntax errors — Dharman, Aug 08 '20 at 18:24
Here is the correction, is this right: $stmt = $connect->prepare("INSERT INTO tbl (sender,phone,message,user_id,time_submitted) VALUES(:sender, :phone, :message, :user_id, :time_submitted)"); $stmt->bindValue(':sender', $sender); $stmt->bindValue(':phone', $phone); $stmt->bindValue(':message', $message2); $stmt->bindValue(':user_id', $user_id); $stmt->bindValue(':time_submitted', $date); $stmt->execute(); — Edward Muss, Aug 08 '20 at 19:11
Yes, it is using PDO library, which is better than mysqli. My answer shows how to do it using mysqli — Dharman, Aug 08 '20 at 19:50
Holy oh no. That `trim($phones,",\r\n\t\r\n/\s+/\0\x0B]/")` is a red hot mess. That is definitely not how to pack a character mask. Please read the `trim()` documentation to better understand that that second argument is not doing what you think it is doing. — mickmackusa, Jan 27 '21 at 11:56

score 2 · Accepted Answer · answered Aug 08 '20 at 18:22

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.

Insert Bulk Data in the DB in Background in PHP

1 Answers1

Linked

Related