PHP & MySQL - Insert Multiple Records

The MySQL INSERT INTO statement is used to insert a new record in a table. There are two ways of using INSERT INTO statement which are mentioned below.

The below syntax specifies column names and respective values to be inserted.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

The below syntax specifies values only. Therefore, it is essential to specify values in the same order as the columns in the table.

INSERT INTO table_name 
VALUES (value1, value2, value3, ...);

Along with this, to connect to the MySQL server, mysqli_connect() function can be used. After establishing the connection, mysqli_multi_query() function can be used to perform multiple queries to insert multiple records in the given table.

Insert Data To MySQL Database - Object-oriented style

The example below demonstrates how to insert multiple new records in a table named "Employee" in object-oriented style.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = new mysqli($servername, $username, $password, $dbname);

//checking connection
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: ". $mysqli->connect_error;
  exit();
}

//query for inserting multiple records
$sql = "INSERT INTO Employee (EmpID, Name, City, Age, Salary)
VALUES (7, 'Suresh', 'Mumbai', 29, 2900);";
$sql .= "INSERT INTO Employee (EmpID, Name, City, Age, Salary)
VALUES (8, 'Zayne', 'Oslo', 31, 3400);";
$sql .= "INSERT INTO Employee (EmpID, Name, City, Age, Salary)
VALUES (9, 'John', 'London', 28, 2800);";

//executing the query
if (!$mysqli->query($sql)) {
  echo "Error inserting records: ". $mysqli->error;
} else { 
  echo "New records inserted successfully.";
}

//closing the connection
$mysqli->close();
?>

Insert Data To MySQL Database - Procedural style

To obtain the same result using procedural style, the following script can be used.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = mysqli_connect($servername, $username, $password, $dbname);

//checking connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: ". mysqli_connect_error();
  exit();
}

//query for inserting multiple records
$sql = "INSERT INTO Employee (EmpID, Name, City, Age, Salary)
VALUES (7, 'Suresh', 'Mumbai', 29, 2900);";
$sql .= "INSERT INTO Employee (EmpID, Name, City, Age, Salary)
VALUES (8, 'Zayne', 'Oslo', 31, 3400);";
$sql .= "INSERT INTO Employee (EmpID, Name, City, Age, Salary)
VALUES (9, 'John', 'London', 28, 2800);";

//executing the query
if (!mysqli_query($mysqli, $sql)) {
  echo "Error inserting records: ". mysqli_error($mysqli);
} else { 
  echo "New records inserted successfully.";
}

//closing the connection
mysqli_close($mysqli);
?>

Complete PHP MySQLi Reference

For a complete reference of all properties, methods and functions of PHP MySQLi extension, see PHP MySQLi Reference.