Insert multiple pieces of data into MySQL using MySQLi and PDO
The mysqli_multi_query() function can be used to execute multiple SQL statements.
The following example adds three new records to the "MyGuests" table:
Example (MySQLi - Object Oriented)
<?php $servername = " localhost " ; $username = " username " ; $password = " password " ; $dbname = " myDB " ; // Create 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 record inserted successfully " ; } else { echo " Error: " . $sql . " <br> " . $conn -> error ; } $conn -> close ( ) ; ?> | Please note that each SQL statement must be separated by a semicolon. |
|---|
Example (MySQLi - Procedure Oriented)
<?php $servername = " localhost " ; $username = " username " ; $password = " password " ; $dbname = " myDB " ; // Create 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 record inserted successfully " ; } else { echo " Error: " . $sql . " <br> " . mysqli_error ( $conn ) ; } mysqli_close ( $conn ) ; ?> Example(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', '[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]') " ) ; // Submit transaction $conn -> commit ( ) ; echo " New record inserted successfully " ; } catch ( PDOException $e ) { // If execution fails, rollback $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 queries to the mysql database without data. You can nematically associate or "bind" variables.
Example (MySQLi uses prepared statements)
<?php $servername = " localhost " ; $username = " username " ; $password = " password " ; $dbname = " myDB " ; // Create a 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 statement object for mysqli_stmt_prepare() $stmt = mysqli_stmt_init ( $conn ) ; // Preprocessing statement if ( mysqli_stmt_prepare ( $stmt , $sql ) ) { // Bind parameters mysqli_stmt_bind_param ( $stmt , ' sss ' , $firstname , $lastname , $email ) ; // Set parameters and execute $firstname = ' John ' ; $lastname = ' Doe ' ; $email = ' [email protected] ' ; mysqli_stmt_execute ( $stmt ) ; $firstname = ' Mary ' ; $lastname = ' Moe ' ; $email = ' mary @example.com ' ; mysqli_stmt_execute ( $stmt ) ; $firstname = ' Julie ' ; $lastname = ' Dooley ' ; $email = ' [email protected] ' ; mysqli_stmt_execute ( $stmt ) ; } } ?> We can see that modularity is used to solve problems in the above examples. We can achieve easier reading and management by creating code blocks.
Pay attention to the binding of parameters. Let's 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 parameters to the database. The second parameter is "sss". The following list shows the parameter types. The s character tells mysql that the parameter is a string.
It can be the following four parameters:
i - integer
d - double precision floating point number
s - string
b - Boolean value
Each parameter must specify a type to ensure data security. Type judgment can reduce the risk of SQL injection vulnerabilities.
