PHP MySQL inserts multiple data

In general, INSERT statements can only add one statement to the MySQL database, and this article describes how to insert multiple data into a data table in bulk using a function.

Insert multiple data into MySQL using MySQLi and PDO

mysqli_multi_query () function can be used to execute multiple SQL statements.

The following example adds three new records to the MyGuests table:

Instances (MySQLi - Object Oriented)

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

Create a link
$conn =
new mysqli($servername, $username, $password, $dbname);

Check the link

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 created successfully";
} else {
echo
"Error: " . $ sql . "
" . $conn->error;
}

$conn->close();
?>


PHP MySQL inserts multiple data Note that each SQL statement must be separated by a half sign.


Instances (MySQLi - Process Oriented)

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

Create a link
$conn = mysqli_connect($servername, $username, $password,
$dbname);

Check the link

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
created successfully";
} else {
echo "Error: "
. $sql . "
" . mysqli_error($conn);
}

mysqli_close($conn);
?>


Example (PDO)

<?php
$servername = "localhost";
$username = "username";

$password = "password";
$dbname =
"myDBPDO";

try {
$conn = new PDO("mysql:host=$servername; d bname=$dbname",
$username, $password);

set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);

Start the transaction

$conn->beginTransaction();
SQL statement

$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]')");


commit the transaction
$conn->commit();

echo "New records created successfully";
}
catch(PDOException $e)
{

roll back the transaction if something failed

$conn->rollback();

echo $sql . "
" . $e->getMessage();
}


$conn = null;
?>


Use preprocessed statements

The mysqli extension provides a second way to insert statements.

We can preprocess statements and binding 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 uses preprocessed statements)

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

Create connection
$conn =
new mysqli($servername, $username, $password, $dbname);

Check connection

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} else {
$sql = "INSERT INTO
MyGuests (firstname, lastname, email) VALUES(?, ?, ?)";

Initialize mysqli_stmt_prepare () for
The statement object
$stmt =
mysqli_stmt_init($conn);

Preprocessed statements

if (mysqli_stmt_prepare($stmt, $sql)) {

The binding parameter

mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);


Set parameters and execute them

$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 modularity to handle the problem. We can make it easier to read and manage by creating blocks of code.

Note the binding of the parameters. Let's look mysqli_stmt_bind_param code in the mysqli_stmt_bind_param(): 

mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);

The function binds parameter queries and passes them to the database. T he second parameter is "sss". T he following list shows the types of parameters. The s character tells the mysql argument to be a string.

There can be four parameters:

  • i - integer

  • d - double

  • s - string

  • b - BLOB

Each parameter must specify a type to keep the data secure. The type of judgment reduces the risk of SQL injection vulnerabilities.