PHP MySQL Insert Multiple Records
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
<?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', 'john@example.com'),
('Mary', 'Moe', 'mary@example.com'),
('Julie', 'Dooley', 'julie@example.com')";
if ($conn->multi_query($sql) === TRUE) {
echo "New records
inserted successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
Example - MySQLi Procedural
<?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', 'john@example.com'),
('Mary', 'Moe', 'mary@example.com'),
('Julie', 'Dooley', 'julie@example.com')";
if (mysqli_multi_query($conn, $sql)) {
echo "New records
inserted successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>
Example - PDO
<?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', 'john@example.com'),
('Mary', 'Moe', 'mary@example.com',
('Julie', 'Dooley', 'julie@example.com')";
$conn->exec($sql);
echo "New records
inserted successfully";
} catch(PDOException $e) {
echo "Error: " . $sql .
"<br>" . $e->getMessage();
}
$conn = null;
?>
