PHP MySQL Insert Multiple
In this article, we will explore different ways to insert multiple rows of data into a MySQL database using PHP, including MySQLi and PDO.
We will also discuss some best practices and how to implement them so that the data is correctly formatted and that the query will be executed efficiently.
By the time you finish reading this article, you will be able to insert multiple records into a MySQL database using PHP code.
The process of inserting multiple records at the same time into a table is often needed when working with databases. You can do this in MySQL using the INSERT statement with multiple value sets, which can be used to do this.
Insert Multiple Rows Using a loop
The first method to insert multiple rows of data into a MySQL database using PHP is by using a loop.
In this method, we create an array of data and loop through each element in the array to insert it into the database.
Here is an example code:
<?php
// database connection
$conn = mysqli_connect("localhost", "username", "password", "database");// array of data
$data = array(
array("John", "Doe", "
[email protected]"),
array("Jane", "Doe", "
[email protected]"),
array("Bob", "Smith", "
[email protected]")
);// loop through the array and insert each row into the database
foreach ($data as $row) {
$sql = "INSERT INTO users (first_name, last_name, email) VALUES ('$row[0]', '$row[1]', '$row[2]')";
mysqli_query($conn, $sql);
}// close database connection
mysqli_close($conn);
?>
In the above example, we first establish a database connection using the mysqli_connect() function. We then create an array of data, which contains three rows of data.
We loop through each row in the array and use the mysqli_query() function to insert the row into the users table of our database.
Insert Multiple Records Using MySQLi and PDO
With the mysqli_multi_query() function, multiple SQL statements can be executed at the same time.
Using the following examples, you will be able to add three new records to the “Users” table:
Below is an example using MySQLi’s Object-Oriented method:
<?php
$servername = "localhost";
$username = "my_user";
$password = "my_pass";
$dbname = "mrx_test_db";// Creating connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Checking connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO Users (firstname, lastname, email, age, gender)
VALUES ('Matt', 'LeBlanc', '
[email protected]', 55, 'Male');";$sql .= "INSERT INTO Users (firstname, lastname, email, age, gender)
VALUES ('Matthew', 'Perry', '
[email protected]', 53 , 'Male');";$sql .= "INSERT INTO Users (firstname, lastname, email, age, gender)
VALUES ('Jennifer', 'Anniston', '
[email protected]', 54, 'Female')";if ($conn->multi_query($sql) === TRUE) {
echo "New records added successfully";
} else {
echo "Error: " . $sql . " " . $conn->error;
}
$conn->close();
?>
A semicolon must be used to separate each SQL statement in order for it to be valid.
Below is an example using MySQLi’s Procedural method:
<?php
$servername = "localhost";
$username = "my_user";
$password = "my_pass";
$dbname = "mrx_test_db";
// Creating connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Checking connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "INSERT INTO Users (firstname, lastname, email, age, gender)
VALUES ('Matt', 'LeBlanc', '
[email protected]', 55, 'Male');";$sql .= "INSERT INTO Users (firstname, lastname, email, age, gender)
VALUES ('Matthew', 'Perry', '
[email protected]', 53 , 'Male');";$sql .= "INSERT INTO Users (firstname, lastname, email, age, gender)
VALUES ('Jennifer', 'Anniston', '
[email protected]', 54, 'Female')";
if (mysqli_multi_query($conn, $sql)) {
echo "New records added successfully";
} else {
echo "Error: " . $sql . " " . mysqli_error($conn);
}
mysqli_close($conn);
?>
There is a slight difference in the PDO way of doing things:
<?php
$servername = "localhost";
$username = "my_user";
$password = "my_pass";
$dbname = "mrx_test_db";try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// sSpecify the exception error mode in the PDO
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// begin the transaction
$conn->beginTransaction();
// our SQL statements
$conn->exec("INSERT INTO Users (firstname, lastname, email, age, gender)
VALUES ('Matt', 'LeBlanc', '
[email protected]', 55, 'Male')");
$conn->exec("INSERT INTO Users (firstname, lastname, email, age, gender)
VALUES ('Matthew', 'Perry', '
[email protected]', 53 , 'Male')");
$conn->exec("INSERT INTO Users (firstname, lastname, email, age, gender)
VALUES ('Jennifer', 'Anniston', '
[email protected]', 54, 'Female')");
// commit the transaction
$conn->commit(); echo "New records added successfully";
}
catch (PDOException $e) {
// The transaction can be rolled back if something goes wrong
$conn->rollback();
echo "Error: " . $e->getMessage();
}
$conn = null;
?>
Example Explanation
First, the connection details are specified in variables. Then, a PDO object is created and the exception error mode is set to “ERRMODE_EXCEPTION“.
A transaction is then started using beginTransaction(). This is used to ensure that all three insert statements are executed as a single unit of work. If there is an error with any of the statements, the transaction can be rolled back so that none of the records are inserted.
The three insert statements are then executed using exec().
Finally, the transaction is committed using commit(). If an error occurs during the transaction, the catch block is executed, which rolls back the transaction and displays an error message.
The connection is then closed using $conn = null;.
Using Prepared Statements
Another method to insert multiple rows of data into a MySQL database using PHP is by using prepared statements.
Prepared statements are a way to execute a statement multiple times with different parameter values.
Below is an example:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Prepare the SQL statement
$sql = "INSERT INTO users (name, email, password) VALUES (?, ?, ?)";
$stmt = mysqli_prepare($conn, $sql);
// Bind parameters and execute the statement multiple times
$name1 = "John";
$email1 = "[email protected]";
$password1 = "password123";
mysqli_stmt_bind_param($stmt, "sss", $name1, $email1, $password1);
mysqli_stmt_execute($stmt);
$name2 = "Jane";
$email2 = "[email protected]";
$password2 = "password456";
mysqli_stmt_bind_param($stmt, "sss", $name2, $email2, $password2);
mysqli_stmt_execute($stmt);
$name3 = "Bob";
$email3 = "[email protected]";
$password3 = "password789";
mysqli_stmt_bind_param($stmt, "sss", $name3, $email3, $password3);
mysqli_stmt_execute($stmt);
// Close the statement and connection
mysqli_stmt_close($stmt);
mysqli_close($conn);
?>
In this example, we’re inserting three records into the users table. We’re preparing the SQL statement, binding parameters, and executing the statement multiple times using different parameter values for each record.
You can repeat the bind and execute steps as many times as necessary to insert multiple records.
