MySql Batch Insert Using PHP

Sometimes we want to insert/update multiple records into MySql Database but due to performance issue we cannot call connect to MySql for every insert/update statement. In this article we will discuss how we can perform multiple insert/update on MySql Database.

MySqli Class in PHP:
MySqli is the newest addition to PHP, it has great feature when compared to traditional mysql Class in PHP. For More Information on MySqli you can check out PHP MySqli Extension

NOTE:
To use MySqli you need to “Configure PHP Installation With –with-mysqli parameter”

To test the batch insert operation i am going to create table “test” inside master database of mysql.

    CREATE TABLE master.test(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100));

Creating New MySqli Connection:

< ?
   $batchconnection = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
?>


 


Check for MySqli Connection:

< ?
      if ($batchconnection->connect_error) {
          echo "Error Occurred While Connection To DataBase";
      }
?>

Generating Sql Statements:
To achieve multiple insert/update all the insert/update statements should be concatenated inside a string. All these sql statement will be separated by semicolon(;).

Suppose i want to insert following Data into “test” table.
Hitesh
Agrawal
Hello
World

So our SqlStatement String Would be :

< ?
$sqlStatements = "insert into test(name) values('Hitesh');insert into test(name) values('Agrawal');insert into test(name) values('Hello');insert into test(name) values('World')";
?>

Regular Expression in MySQL

Batch Insert Sql Execution in PHP:

< ?
   $sqlResult = $batchconnection->multi_query($sqlStatements);
 
   if($sqlResult == true) {
       echo "Successfully Inserted Records";
   } else {
       echo "Some Error Occured While Inserting Records";
   }
?>

Final Code:

< ?
      $batchconnection = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
      if ($batchconnection->connect_error) {
          echo "Error Occurred While Connection To DataBase";
      }
      $sqlStatements = "insert into test(name) values('Hitesh');insert into test(name) values('Agrawal');insert into test(name) values('Hello');insert into test(name) values('World')";
 
 $sqlResult = $batchconnection->multi_query($sqlStatements);
 
   if($sqlResult == true) {
       echo "Successfully Inserted Records";
   } else {
       echo "Some Error Occured While Inserting Records";
   }
?>


Related Articles:

About Hitesh Agrawal

Wait... checking
Tagged , , . Bookmark the permalink.

7 Responses to MySql Batch Insert Using PHP

  1. Pingback: insert into sql

  2. Harish says:

    HI Hitesh.
    this is a very useful update on mysql part.
    thanks for putting it up here..

  3. Thanks, batch inserts with multi_query works for me.

  4. Tone says:

    Thanks, worked a treat.

  5. khagesh says:

    Thanks good work

  6. Svetlana says:

    Hi, guys! Thank you for a useful info. We have posted an article “PHP: Insert Text Into File at Position”, might be useful to your readers too: http://www.learncomputer.com/php-insert-text-into-file-at-position/ Thanks and good luck!

  7. Arvind says:

    Thanks

    I found it usefull.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>