PHP MySQL Multiple Insert

In this unit, we will see how to insert multiple records in the database table. Insert statement adds new records into a table.

Read Also: PHP MySQL Insert Data

MySQL Multiple Insert

• By using PHP MySQL insertion statements, we can insert multiple records at the same time.
• This feature helps to increase the efficiency of a website by reducing the number of lines of a query. Multiple records can be inserted with a single query instead of multiple queries. This helps the website 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', 'neha@gmail.com');";
$query.= "INSERT INTO user (firstname, lastname, email) VALUES ('Mary', 'shah', 'mary@gmail.com');";
$query.= "INSERT INTO user (firstname, lastname, email) VALUES ('rita', 'Ahuja', 'rita@gmail.com')";

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', 'neha@gmail.com');";
$query .= "INSERT INTO user (firstname, lastname, email) VALUES ('Mary', 'Shah', 'mary@gmail.com');";
$query .= "INSERT INTO user (firstname, lastname, email) VALUES ('Rita', 'Ahuja', 'rita@gmail.com')";

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', 'neha@gmail.com')");
$connection->exec("INSERT INTO user (firstname, lastname, email) VALUES ('Mary', 'shah', 'mary@gmail.com')");
$connection->exec("INSERT INTO user (firstname, lastname, email) VALUES ('Rita', 'Ahuja', 'julie@gmail.com')");

// 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.

We hope you found this article helpful and in case, you want to learn about how to connect to MySQL using PHP and creating databases, do check out our previous blogs. Happy Learning 🙂