WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

PHP Insert Multiple Records Into MySQL

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:

Instance (MySQLi-Object Oriented)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Create link $conn = new mysqli($servername, $username, $password, $dbname); // Check link if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com');"; $sql .= "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Mary', 'Moe', 'mary@example.com');"; $sql .= "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Julie', 'Dooley', 'julie@example.com')"; if ($conn->multi_query($sql) === TRUE) { echo "New record inserted successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; } $conn->close(); ?>


Please note that each SQL statement must be separated by a semicolon.

Examples (MySQLi-Process Oriented)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Create link $conn = mysqli_connect($servername, $username, $password, $dbname); // Check link if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com');"; $sql .= "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Mary', 'Moe', 'mary@example.com');"; $sql .= "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Julie', 'Dooley', 'julie@example.com')"; if (mysqli_multi_query($conn, $sql)) { echo "New record inserted successfully"; } else { echo "Error: " . $sql . "<br>" . mysqli_error($conn); } mysqli_close($conn); ?>


Examples (PDO)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDBPDO"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Start transaction $conn->beginTransaction(); // SQL Statement $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')"); $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Mary', 'Moe', 'mary@example.com')"); $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Julie', 'Dooley', 'julie@example.com')"); // Commit transaction $conn->commit(); echo "New record inserted successfully"; } catch(PDOException $e) { // Rollback if execution fails $conn->rollback(); echo $sql . "<br>" . $e->getMessage(); } $conn = null; ?>



Use Prepared statements

The mysqli extension provides a second way to insert statements.

We can prepare statements and Bind parameters.

The mysql extension can send statements or query to the mysql database without data. You can associate or "bind" variables to columns.

Examples (Prepared statements used by MySQLi)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Detect connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } else { $sql = "INSERT INTO MyGuests(firstname, lastname, email) VALUES(?, ?, ?)"; // Initialize the statement object for mysqli_stmt_prepare () $stmt = mysqli_stmt_init($conn); //Prepared statements if (mysqli_stmt_prepare($stmt, $sql)) { // Bind parameter mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email); // Set parameters and execute $firstname = 'John'; $lastname = 'Doe'; $email = 'john@example.com'; mysqli_stmt_execute($stmt); $firstname = 'Mary'; $lastname = 'Moe'; $email = 'mary@example.com'; mysqli_stmt_execute($stmt); $firstname = 'Julie'; $lastname = 'Dooley'; $email = 'julie@example.com'; mysqli_stmt_execute($stmt); } } ?>

We can see that the above example uses modularity to deal with the problem. We can achieve simpler reading and management by creating code blocks.

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

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

This function binds the parameter query and passes the parameter to the database. The second parameter is "sss". The following list shows the types of parameters. The s character tells mysql that the argument is a string.

It can be the following four parameters:

  • i-integer
  • d-double-precision floating-point number
  • s- String
  • b-Boolean

Each parameter must specify a type to ensure the security of the data. Judging by type can reduce the risk of SQL injection vulnerabilities.