HTML 
CSS 
PHP 
JavaScript 
SQL 
Bootstrap 
Solidity 
jQuery 
Git 
Chrome DevTools 
C++ 
Python 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.
- Easy to use with a learn-by-doing approach
- Offers quality content
- Gamified in-browser coding experience
- The price matches the quality
- Suitable for learners ranging from beginner to advanced
- Free certificates of completion
- Focused on data science skills
- Flexible learning timetable
- Simplistic design (no unnecessary information)
- High-quality courses (even the free ones)
- Variety of features
- Nanodegree programs
- Suitable for enterprises
- Paid Certificates of completion
- A wide range of learning programs
- University-level courses
- Easy to navigate
- Verified certificates
- Free learning track available
- University-level courses
- Suitable for enterprises
- Verified certificates of completion
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', '[email protected]');";
$sql .= "INSERT INTO users (username, password, email) VALUES ('Margaret', 'Johnson', '[email protected]');";
$sql .= "INSERT INTO users (username, password, email) VALUES ('Juliete', 'Doodley', '[email protected]');";
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', '[email protected]');";
$sql .= "INSERT INTO users (username, password, email) VALUES ('Margaret', 'Johnson', '[email protected]');"
$sql .= "INSERT INTO users (username, password, email) VALUES ('Juliete', 'Doodley', '[email protected]');";
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', '[email protected]')");
$con->exec("INSERT INTO users (name, surname, e_mail) VALUES ('Johnny', 'Eriksen', '[email protected]')");
$con->exec("INSERT INTO users (name, surname, e_mail) VALUES ('Mary', 'Julie', '[email protected]')");
// 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.
