PHP MySQL- Insert Multiple Records

Php Mysql Insert Multiple Records

In this tutorial, we will learn to insert multiple records in a MySQL database using the PHP program. This is a very simple tutorial which consists of multiple ways and methods to insert multiple records such as mysqli and PDO. Let’s dive into it!

Before We Start

There are two approaches for inserting multiple records in the MySQL table. You can either write a single query to insert multiple records and execute that query. Or, you can write multiple INSERT queries and run all those queries using the mysqli_multi_query function.

Apart from these two methods, there can be other methods too such as inserting multiple records using loops. However, we are going to explain the easiest and most effective ways in this tutorial.

Inserting Multiple Records Example

In this tutorial, we will be using the table of the following description.

Emp Table Description
Emp Table Description

Using MySQLi (Procedural)- Method 1

<?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "journaldev"; $conn = mysqli_connect($servername, $username, $password, $dbname); if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } $sql = "INSERT INTO emp(name,city) VALUES('John','London'),('Peter','Paris'),('Amy','New York'),('Hannah','LA')"; $result = mysqli_query($conn, $sql); if ($result) { echo "Records inserted using mysqli procedural method."; } else { echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn); } mysqli_close($conn); ?>
Code language: PHP (php)
Insert Using Mysqli Procedural Method
Insert Using Mysqli Procedural Method

In this method, we write only a single insert query in which we put multiple values and execute that query.

Using MySQLi (Procedural) – Method 2

<?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "journaldev"; $conn = mysqli_connect($servername, $username, $password, $dbname); if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } $sql = "INSERT INTO emp(name,city) VALUES('John','London');"; $sql .= "INSERT INTO emp(name,city ) VALUES('Mike','Paris');"; $sql .= "INSERT INTO emp(name,city ) VALUES('Mary','New York');"; $result = mysqli_multi_query($conn, $sql); if ($result) { echo "Records inserted using mysqli procedural method 2."; } else { echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn); } mysqli_close($conn); ?>
Code language: PHP (php)
Insert Using Mysqli Procedural Method 2
Insert Using Mysqli Procedural Method 2

In this method, we write multiple insert queries and store them in a single variable as a string. To execute such a statement which consists of multiple queries, we use the mysqli_multi_query() function as shown in the code above.

All the queries specified in the single statement are executed one by one. Also, You can store different types of queries such as INSERT, and UPDATE in a single statement and execute them using the mysqli_multi_query() function.

Using MySQLi (Object Oriented) – Method 1

<?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "journaldev"; $conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "INSERT INTO emp(name,city) VALUES('John','London'),('Peter','Paris'),('Amy','New York'),('Hannah','LA')"; $result = $conn->query($sql); if ($result == TRUE) { echo "Records inserted using mysqli object oriented method "; } else { echo "Error: " . $sql . "<br>" . $conn->error; } ?>
Code language: PHP (php)
Insert Using Mysqli Object Oriented Method
Insert Using Mysqli Object Oriented Method

Using MySQLi (Object Oriented) – Method 2

<?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "journaldev"; $conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "INSERT INTO emp(name,city) VALUES('John','London');"; $sql .= "INSERT INTO emp(name,city ) VALUES('Mike','Paris');"; $sql .= "INSERT INTO emp(name,city ) VALUES('Mary','New York');"; $result = $conn->multi_query($sql); if ($result == TRUE) { echo "Records inserted using mysqli object oriented method 2"; } else { echo "Error: " . $sql . "<br>" . $conn->error; } ?>
Code language: PHP (php)
Insert Using Mysqli Object Oriented Method 2
Insert Using Mysqli Object Oriented Method 2

Using PDO – Method

The PDO method for inserting multiple records is a little bit different from the previous methods. Here, we use the exec() function to execute the query statement. The statement might contain a single query or multiple queries.

<?php $serverName = "localhost"; $userName = "root"; $password = ""; $dbName = "journaldev"; try { $conn = new PDO("mysql:host=$serverName;dbname=$dbName", $userName, $password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $conn->beginTransaction(); //begin transaction $conn->exec("INSERT INTO emp(name,city) VALUES('John','London'),('John','London');"); $conn->exec("INSERT INTO emp(name,city) VALUES('Peter','Paris');"); $conn->exec("INSERT INTO emp(name,city) VALUES('Paul','Berlin');"); $conn->commit(); //commit the transaction echo "Records inserted using PDO method"; } catch (PDOException $e) { $conn->rollBack(); //rollback if something failed echo "Error: " . $e->getMessage(); } ?>
Code language: PHP (php)

As you can see, the first exec() function consists of multiple values in the INSERT statement, while the other exec() functions consist of only a single value in the INSERT statement.

Insert Using Mysqli PDO Method
Insert Using Mysqli PDO Method

Conclusion

In this tutorial, we learned to insert multiple records in a MySQL database using the PHP program. We have seen multiple methods such as using a single query and multiple queries along with different ways such as MySQLi and PDO. Apart from these methods, there can be other methods too that you can use to insert multiple records in the table.