Insert multiple data entries in PHP MySQL and multiple data entries in phpmysql

Insert multiple data entries in PHP MySQL and multiple data entries in phpmysql
Use MySQLi and PDO to insert multiple data records into MySQL

The 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 a 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 ('julil', 'doole', 'julie @ example.com ')"; if ($ conn-> multi_query ($ SQL) === TRUE) {echo "New records created successfully" ;}else {echo "Error :". $ SQL. "<br> ". $ conn-> error;} $ conn-> close ();?>

 

Instance (MySQLi-process-oriented)

<? Php $ servername = "localhost"; $ username = "username"; $ password = "password"; $ dbname = "myDB "; // create a 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 ('julil', 'doole', 'julie @ example.com ')"; if (mysqli_multi_query ($ con N, $ SQL) {echo "New records created 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; dbname = $ dbname ", $ username, $ password); // set the PDO error mode to exception $ conn-> setAttribute (PDO: ATTR_ERRMODE, PDO:: ERRMODE_EXCEPTION); // start the 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 ('julil', 'doolean ', 'julie @ example.com ') "); // commit the transaction $ conn-> commit (); echo" New records created successfully ";} catch (PDOException $ e) {// roll back the transaction I F something failed $ conn-> rollback (); echo $ SQL. "<br>". $ e-> getMessage () ;}$ conn = null;?>

 

Use preprocessing statements

Mysqli extension provides the second method for inserting statements.

We can pre-process statements and bind parameters.

Mysql extension can be used to query mysql databases without data sending statements. You can associate columns or "bind" variables.

Example (MySQLi uses preprocessing statements)

<? Php $ servername = "localhost"; $ username = "username"; $ password = "password"; $ dbname = "myDB "; // Create connection $ conn = new mysqli ($ servername, $ username, $ password, $ dbname); // Check connectionif ($ conn-> connect_error) {die ("Connection failed :". $ conn-> connect_error);} else {$ SQL = "INSERT INTO MyGuests VALUES (?, ?, ?) "; // Initialize the statement object for mysqli_stmt_prepare () $ stmt = mysqli_stmt_init ($ conn); // preprocessing statement if (mysqli_stmt_prepare ($ stmt, $ SQL )) {// bind the parameter mysqli_stmt_bind_param ($ stmt, 'ss', $ firstname, $ lastname, $ email); // set the parameter 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 = 'julil'; $ lastname = 'doole'; $ email = 'julie @ example.com '; mysqli_stmt_execute ($ stmt) ;}}?>

We can see that modularization is used in the above instances to solve the problem. We can create code blocks for easier reading and management.

Note the parameter binding. Let's take a look at the code in mysqli_stmt_bind_param:

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

This function is bound to a parameter query and passed to the database. The second parameter is "sss ". The following lists the parameter types. The s character tells mysql that the parameter is a string.

This argument may be one of four types:

• I-integer
• D-double
• S-string
• B-BLOB

Each parameter must specify a type to ensure data security. You can determine the types to reduce the risks caused by SQL injection vulnerabilities.

 

Original article address:/php/php_mysql_insert_multiple.html

Php mysql-related reading materials:

• Introduction to PHP MySQL
• PHP connection to MySQL
• Create a database in php
• Create a table in php
• Php mysq insert data
• Insert multiple data entries in PHP MySQL
• PHP MySQL pre-processing statement
• Php mysql reads data
• Php mysql where
• PHP MySQL Order
• PHP MySQL Update
• PHP MySQL Delete
• Php ODBC