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 🙂