PHP MySQL Insert Multiple Records

PHP MySQL Insert Multiple Records

Sometimes, you need using PHP MySQL Insert Multiple Records into a table in a single query. PHP supports this using mysqli or PDO.

Using `mysqli` (Procedural)

<?php
$conn = mysqli_connect("localhost", "root", "", "test_db");

if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// Insert multiple records
$sql = "INSERT INTO users (name, email, age) VALUES
('Vipul', 'vipul@example.com', 25),
('Amit', 'amit@example.com', 28),
('Ravi', 'ravi@example.com', 30)";

if (mysqli_query($conn, $sql)) {
echo "New records inserted successfully. Last inserted ID is: " . mysqli_insert_id($conn);
} else {
echo "Error: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

<?php

$conn = mysqli_connect("localhost", "root", "", "test_db");

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

// Insert multiple records

$sql = "INSERT INTO users (name, email, age) VALUES

('Vipul', 'vipul@example.com', 25),

('Amit', 'amit@example.com', 28),

('Ravi', 'ravi@example.com', 30)";

if (mysqli_query($conn, $sql)) {

echo "New records inserted successfully. Last inserted ID is: " . mysqli_insert_id($conn);

} else {

echo "Error: " . mysqli_error($conn);

}

mysqli_close($conn);

Single query inserting multiple rows is faster than multiple separate queries.

Using `mysqli` Object-Oriented

<?php
$conn = new mysqli("localhost", "root", "", "test_db");

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO users (name, email, age) VALUES
('Vipul', 'vipul@example.com', 25),
('Amit', 'amit@example.com', 28),
('Ravi', 'ravi@example.com', 30)";

if ($conn->query($sql) === TRUE) {
echo "New records inserted successfully. Last inserted ID is: " . $conn->insert_id;
} else {
echo "Error: " . $conn->error;
}

$conn->close();
?>

<?php

$conn = new mysqli("localhost", "root", "", "test_db");

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

$sql = "INSERT INTO users (name, email, age) VALUES

('Vipul', 'vipul@example.com', 25),

('Amit', 'amit@example.com', 28),

('Ravi', 'ravi@example.com', 30)";

if ($conn->query($sql) === TRUE) {

echo "New records inserted successfully. Last inserted ID is: " . $conn->insert_id;

} else {

echo "Error: " . $conn->error;

}

$conn->close();

Using PDO with Prepared Statements

If you want secure insertion for multiple rows, use a loop with PDO prepared statements:

<?php
try {
$conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $conn->prepare("INSERT INTO users (name, email, age) VALUES (:name, :email, :age)");

$users = [
["Vipul", "vipul@example.com", 25],
["Amit", "amit@example.com", 28],
["Ravi", "ravi@example.com", 30]
];

foreach ($users as $user) {
$stmt->bindParam(':name', $user[0]);
$stmt->bindParam(':email', $user[1]);
$stmt->bindParam(':age', $user[2]);
$stmt->execute();
}

echo "All records inserted successfully.";

} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}

$conn = null;
?>

<?php

try {

$conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $conn->prepare("INSERT INTO users (name, email, age) VALUES (:name, :email, :age)");

$users = [

["Vipul", "vipul@example.com", 25],

["Amit", "amit@example.com", 28],

["Ravi", "ravi@example.com", 30]

];

foreach ($users as $user) {

$stmt->bindParam(':name', $user[0]);

$stmt->bindParam(':email', $user[1]);

$stmt->bindParam(':age', $user[2]);

$stmt->execute();

}

echo "All records inserted successfully.";

} catch(PDOException $e) {

echo "Error: " . $e->getMessage();

}

$conn = null;

Using prepared statements prevents SQL injection and allows dynamic multiple inserts.

Key Points

Multiple rows in a single query are faster for large datasets.
Use prepared statements for user input to prevent SQL injection.
mysqli_insert_id() or $conn->insert_id returns the first inserted auto-increment ID.
PDO allows looping through an array of records safely and securely.

PHP MySQL Insert Multiple Records