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.

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)

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)

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)

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)

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.

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.