PHP MySQL Insert Multiple Rows
Inserting Multiple Records into MySQL Using MySQLi and PDO
The mysqli_multi_query() function can be used to execute multiple SQL statements.
The following example adds three new records to the "MyGuests" table:
Example (MySQLi - Object-oriented)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed:" . $conn->connect_error);
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Julie', 'Dooley', '[email protected]')";
if ($conn->multi_query($sql) === TRUE) {
echo "New records inserted successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
 |
Please note that each SQL statement must be separated by a semicolon. |
Example (MySQLi - Procedural)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
die("Connection failed:" . mysqli_connect_error());
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('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)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->beginTransaction();
$conn->exec("INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]')");
$conn->exec("INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', '[email protected]')");
$conn->exec("INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Julie', 'Dooley', '[email protected]')");
$conn->commit();
echo "New records inserted successfully";
}
catch(PDOException $e)
{
$conn->rollback();
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
Using Prepared Statements
The mysqli extension provides a second way to insert statements.
We can prepare statements and bind parameters.
The mysql extension can send statements or queries to the MySQL database without data. You can associate or "bind" variables to columns.
Example (MySQLi Using Prepared Statements)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed:" . $conn->connect_error);
} else {
$sql = "INSERT INTO MyGuests(firstname, lastname, email) VALUES(?, ?, ?)";
$stmt = mysqli_stmt_init($conn);
if (mysqli_stmt_prepare($stmt, $sql)) {
mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);
$firstname = 'John';
$lastname = 'Doe';
$email = '[email protected]';
mysqli_stmt_execute($stmt);
$firstname = 'Mary';
$lastname = 'Moe';
$email = '[email protected]';
mysqli_stmt_execute($stmt);
$firstname = 'Julie';
$lastname = 'Dooley';
$email = '[email protected]';
mysqli_stmt_execute($stmt);
}
}
?>
We can see that the above example uses modularization to handle the problem. We can achieve easier reading and management by creating code blocks.
Pay attention to parameter binding. Let's look at the code in mysqli_stmt_bind_param():
mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);
This function binds parameter queries and passes the parameters to the database. The second parameter is "sss". The following list shows the types of parameters. The 's' character tells MySQL that the parameter is a string.
Can be one of the following four parameter types:
- i - integer
- d - double precision floating point
- s - string
- b - binary blob storage object
Each parameter must have a specified type to ensure data security. By checking the type, the risk of SQL injection vulnerabilities can be reduced.
Other extensions