PHP mysql-inserting multiple data

PhpMySQL inserts more than one data 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 a link$conn=NewMysqli ($servername,$username,$password,$dbname);//Check Linksif($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 ();?>

	Note that each SQL statement must be separated by semicolons.

Example (mysqli-process-oriented)

<?PHP$servername= "localhost";$username= "username";$password= "Password";$dbname= "MyDB"; //Create a link$conn=Mysqli_connect($servername,$username,$password,$dbname);//Check Linksif(!$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);?>

Instance (PDO)

<?PHP$servername= "localhost";$username= "username";$password= "Password";$dbname= "Mydbpdo"; Try {    $conn=NewPDO ("mysql:host=$servername;d bname=$dbname",$username,$password); //Set the PDO error mode to exception    $conn->setattribute (Pdo::attr_errmode, PDO::errmode_exception); //Start a transaction    $conn-BeginTransaction (); //SQL Statements    $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 a transaction    $conn-commit (); Echo"New record inserted successfully";}Catch(pdoexception$e){    //Rollback If execution fails    $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 a connection$conn=NewMysqli ($servername,$username,$password,$dbname);//Detecting Connectionsif($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 Statements    if(Mysqli_stmt_prepare($stmt,$sql)) {        //Binding 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 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$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.

PHP mysql-inserting multiple data