PHP MySQL insert multiple data
Use MySQLi and PDO MySQL to insert multiple data
mysqli_multi_query () function is used to execute multiple SQL statements.
The following examples are to "MyGuests" table added three new records:
Examples (MySQLi - Object Oriented)
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDB";
// Create Link
$ Conn = new mysqli ($ servername, $ username, $ password, $ dbname);
// Check Links
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 "The new record is inserted successfully";
} Else {
. Echo "Error:" $ sql "<br>" $ conn-> error;..
}
$ Conn-> close ();
?>
Note that each SQL statement must be separated by a semicolon. |
Examples (MySQLi - process-oriented)
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDB";
// Create Link
$ Conn = mysqli_connect ($ servername, $ username, $ password, $ dbname);
// Check Links
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 "The new record is inserted successfully";
} Else {
. Echo "Error:" $ sql "<br>" mysqli_error ($ conn);..
}
mysqli_close ($ conn);
?>
Examples of (PDO)
$ 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]') ");
// Commit the transaction
$ Conn-> commit ();
echo "The new record is inserted successfully";
}
catch (PDOException $ e)
{
// If the rollback fails
$ Conn-> rollback ();
.. Echo $ sql "<br>" $ e-> getMessage ();
}
$ Conn = null;
?>
Use prepared statements
mysqli extension provides a second way to insert statements.
We prepared statements and bound parameters.
mysql extension can transmit data without a statement or query to the mysql database. You can associate or "bind" variables to the columns.
Examples (MySQLi using prepared statements)
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDB";
// Create connection
$ Conn = new mysqli ($ servername, $ username, $ password, $ dbname);
// Test connection
if ($ conn-> connect_error) {
die ( "Connection failed:" $ conn-> connect_error.);
} Else {
$ Sql = "INSERT INTO MyGuests VALUES (?,,??)";
// To mysqli_stmt_prepare () initialization statement objects
$ Stmt = mysqli_stmt_init ($ conn);
// Prepared Statement
if (mysqli_stmt_prepare ($ stmt, $ sql)) {
// Bind parameters
mysqli_stmt_bind_param ($ stmt, 'sss', $ firstname, $ lastname, $ email);
// Set parameters and perform
$ 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 in the above example using a modular to deal with the problem. We can create a block of code for easier reading and management.
Note bound parameters. Let's look at mysqli_stmt_bind_param () code:
mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);
The Binding arguments to a query and pass parameters to the database. The second parameter is "sss". The following list shows the type of the parameter. s character tells mysql argument is a string.
It may be the following four parameters:
- i - integer
- d - double-precision floating-point number
- s - the string
- b - a boolean value
Each parameter type must be specified to ensure data security. By type of judgment can reduce the risk of SQL injection vulnerability.