Latest web development tutorials

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)

<? Php
$ 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 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 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)

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

// 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)

<? Php
$ 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.