Insert Multiple Records Into MySQL
It is also possible to insert multiple rows into a table with a single insert query at once.
To do this, include multiple lists of column values within the INSERT INTO statement, where column values for each row is enclosed within parentheses and separated by a comma.
In the following examples, we add three new records to the "MyGuests" table:
Example - MySQLi Object-oriented
html
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES
('John', 'Doe', '[email protected]'),
('Mary', 'Moe', '[email protected]'),
('Julie', 'Dooley', '[email protected]')";
if ($conn->multi_query($sql) === TRUE) {
echo "New records
inserted successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
Example - MySQLi Procedural
html
<?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());
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES
('John', 'Doe', '[email protected]'),
('Mary', 'Moe', '[email protected]'),
('Julie', 'Dooley', '[email protected]')";
if (mysqli_multi_query($conn, $sql)) {
echo "New records
inserted successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>
Example - PDO
html
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("Could not connect. " .
$e->getMessage());
}
try {
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES
('John', 'Doe', '[email protected]'),
('Mary', 'Moe', '[email protected]',
('Julie', 'Dooley', '[email protected]')";
$conn->exec($sql);
echo "New records
inserted successfully";
} catch(PDOException $e) {
echo "Error: " . $sql .
"<br>" . $e->getMessage();
}
$conn = null;
?>