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.

Creating New MySqli Connection:


Check for MySqli Connection:

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 :

Regular Expression in MySQL

Batch Insert Sql Execution in PHP:

Final Code:

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