How to insert multiple data via PHP MySQL

Often see inserting data into the database on the site, this article explains multiple data inserted into the database.

Inserting multiple data into MySQL using MYSQLI and PDO

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

The following instance 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 ', ' 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 ();? >

Note that each SQL statement must be separated by semicolons.

Example (mysqli-process-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 ', ' 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);? >

Instance (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 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) {    //If execution fails rollback    $conn->rollback ();    Echo $sql. "<br>". $e->getmessage ();} $conn = null;? >

Using Preprocessing statements

The mysqli extension provides a second way of inserting statements.

We can preprocess statements and bind parameters.

MySQL extensions can send statements without data or query to the MySQL database. You can associate or "bind" variables to columns.

Instance (mysqli using Preprocessing statements)

<?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 (?,?,?)";     Initializes the statement object for Mysqli_stmt_prepare () $stmt = Mysqli_stmt_init ($conn); Preprocessing statement if (Mysqli_stmt_prepare ($stmt, $sql)) {//bind parameter Mysqli_stmt_bind_param ($stmt, ' SSS ', $firstname,         $lastname, $email);        Set the 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 handle the problem. We can make it easier to read and manage by creating code blocks.

Note the binding of the parameter. Let's look at the code in Mysqli_stmt_bind_param ():

Mysqli_stmt_bind_param ($stmt, ' SSS ', $firstname, $lastname, $email);

The function binds the parameter query and passes the parameters to the database. The second parameter is "SSS". The following list shows the type of the parameter. The S character tells the MySQL parameter to be a string.

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 guarantee the security of the data. The type of judgment can reduce the risk of SQL injection vulnerabilities.

This article explained how to insert multiple data through PHP, more learning materials to focus on the PHP Chinese network can be viewed.