As you have learned to update your database tables by inserting and deleting rows, those processes seem easy. However, when you have a lot of data, it's handy to know how to make MySQL insert multiple rows to your data tables at once.
You can do that by using proper statements and following syntax requirements. Of course, first you have to establish a connection to your database and have at least one table created.
In this tutorial, we will explain how to insert multiple rows MySQL in the table you made before. It does not matter whether you use MySQLi or POD: they both allow to add more than one record.
Contents
MySQL Insert Multiple Rows: Main Tips
- By using PHP MySQL insertion statements, you can insert multiple records at the same time.
- This method might prove useful if you want to efficiently send multiple records with a single query instead of multiple queries. This will help your website run faster.
- You have to make sure you use correct MySQL insert syntax.
Three Ways of Inserting Multiple Rows
MySQL insert examples below show how to insert three records into the table called users while using different types of connections. Let's start with the way to insert multiple rows with the MySQLi object-oriented connection:
<?php
$host = 'host';
$user = 'user';
$pass = 'pass';
$db = 'db';
// Create connection
$conn = new mysqli($host, $user, $pass, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO users (username, password, email) VALUES ('Johny', 'Dawkins', 'johny@example.com');";
$sql .= "INSERT INTO users (username, password, email) VALUES ('Margaret', 'Johnson', 'marg@example.com');";
$sql .= "INSERT INTO users (username, password, email) VALUES ('Juliete', 'Doodley', 'juliete@example.com');";
if ($conn->multi_query($sql) === TRUE) {
echo "New records created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>Note: Every SQL statement has to be separated by a semicolon (;).
Now, let's take a look at how to MySQL insert multiple rows using MySQLi procedural. In this case, you will use a function called mysqli_multi_query():
<?php
$host = 'host';
$user = 'user';
$pass = 'pass';
$db = 'db';
// Create connection
$conn = mysqli_connect($host, $user, $pass, $db);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "INSERT INTO users (username, password, email) VALUES ('Johny', 'Dawkins', 'johny@example.com');";
$sql .= "INSERT INTO users (username, password, email) VALUES ('Margaret', 'Johnson', 'marg@example.com');"
$sql .= "INSERT INTO users (username, password, email) VALUES ('Juliete', 'Doodley', 'juliete@example.com');";
if (mysqli_multi_query($conn, $sql)) {
echo "New records created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>If you want to make MySQL insert multiple rows using PDO, slightly different MySQL insert syntax rules apply. You won't need to use mysqli_multi_query() for a PHP MySQL insert in this case. Take a look:
<?php
$server = 'server';
$user = 'user';
$pass = 'pass';
$db = 'db';
try {
$con = new PDO("mysql:host=$server;db=$db", $user, $pass);
// PDO error mode is set to exception
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// the transaction begins
$con->beginTransaction();
// our SQL statements
$con->exec("INSERT INTO users (name, surname, e_mail) VALUES ('Ben', 'Jefferson', 'ben@example.com')");
$con->exec("INSERT INTO users (name, surname, e_mail) VALUES ('Johnny', 'Eriksen', 'johnny@example.com')");
$con->exec("INSERT INTO users (name, surname, e_mail) VALUES ('Mary', 'Julie', 'julie@example.com')");
// commit the transaction
$con->commit();
echo "Created new records.";
} catch(PDOException $err) {
// roll the transaction back if something fails
$con->rollback();
echo "Error message: " . $err->getMessage();
}
$con = null;
?>MySQL Insert Multiple Rows: Summary
- MySQL statements allow you to insert multiple rows with one query. That will make your website faster.
- There are different approaches to this insertion process that depend on your type of connection to MySQL. Whichever you choose, it's crucial to use proper MySQL insert syntax.