In this unit, we see how to insert multiple records in the database table. Insert is the process of inserting data into the table. Insert statement adds new records into a table.
Read Also: PHP MySQL Insert Data
Index
MySQL Multiple Insert
- By using PHP MySQL insertion statements, we can insert multiple records at the same time.
- If we want to efficiently send multiple records with a single query instead of multiple queries. This will help the website to run faster.
- We have to make sure we use the correct MySQL insert syntax.
- Multiple SQL statement uses mysqli_multi_query() function to execute.
Syntax
INSERT INTO table_name (column_list) VALUES (value_list_1), (value_list_2), ... (value_list_n);
- First, we have to specify the name of the table.
- Second, we have to specify a comma-separated column list inside parentheses.
- Third, we have to specify a comma-separated list of row data in the VALUES clause.
If a string data types to be inserted, double or single quotes are to be used.
When adding a new row, the datatype of the value and the column should match.
There are three ways of inserting multiple rows.
1. MySQL Procedural
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "user";
// Create connection
$connection = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
$query = "INSERT INTO user (firstname, lastname, email) VALUES ('Neha', 'shah', '[email protected]');";
$query.= "INSERT INTO user (firstname, lastname, email) VALUES ('Mary', 'shah', '[email protected]');";
$query.= "INSERT INTO user (firstname, lastname, email) VALUES ('rita', 'Ahuja', '[email protected]')";
if (mysqli_multi_query($connection, $sql)) {
echo "New records created successfully";
} else {
echo "Error: " . $query . "<br>" . mysqli_error($connection);
}
mysqli_close($connection);
?>2. Object-Oriented
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "user";
// Create connection
$connection = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($connection->connect_error) {
die("Connection failed: " . $connection->connect_error);
}
$query = "INSERT INTO user (firstname, lastname, email) VALUES ('Neha', 'Shah', '[email protected]');";
$query .= "INSERT INTO user (firstname, lastname, email) VALUES ('Mary', 'Shah', '[email protected]');";
$query .= "INSERT INTO user (firstname, lastname, email) VALUES ('Rita', 'Ahuja', '[email protected]')";
if ($connection->multi_query($query) === TRUE) {
echo "New records created successfully";
} else {
echo "Error: " . $query . "<br>" . $connection->error;
}
$connection->close();
?>3. MySQL PDO
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "user";
try {
$connection = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// begin the transaction
$connection->beginTransaction();
// our SQL statements
$connection->exec("INSERT INTO user (firstname, lastname, email) VALUES ('Neha', 'shah', '[email protected]')");
$connection->exec("INSERT INTO user (firstname, lastname, email) VALUES ('Mary', 'shah', '[email protected]')");
$connection->exec("INSERT INTO user (firstname, lastname, email) VALUES ('Rita', 'Ahuja', '[email protected]')");
// commit the transaction
$connection->commit();
echo "New records created successfully";
} catch(PDOException $e) {
// roll back the transaction if something failed
$connection->rollback();
echo "Error: " . $e->getMessage();
}
$connection = null;
?>Read More: PHP MySQL Select Data
Conclusion
In this article, we learned how to insert multiple rows using the mysqli_multi_query() function. When adding a new row, the datatype of the value and the column should match.
I hope you find this article helpful 🙂
