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')"; ?> |
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:
- MySql Prepared Statement in PHP
- OOPS in PHP5 – construct() Method
- OOPS in PHP 5 Tutorial – Abstract Class
- Understanding MySQL Joins
- MySql – Cross Join Queries
- OOPS in PHP 5 – Polymorphism
- OOPS in PHP 5 Tutorial – Using Interface
- MySql – Working With Stored Procedure
- BackUp and Restore Mysql Database
- PHP 5 Tutorial – get() Magic Method
HI Hitesh.
this is a very useful update on mysql part.
thanks for putting it up here..
Thanks, batch inserts with multi_query works for me.
Thanks, worked a treat.
Thanks good work
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!