PHP MySQL Insert 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:

Instance (mysqli-object-oriented) <?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "MyDB";

Create a 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 "New Records created 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 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 "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;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 the transaction
$conn->commit ();
echo "New Records created successfully";
}
catch (Pdoexception $e)
{
Roll back the transaction if something failed
$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.

Example (mysqli using preprocessing statements) <?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "MyDB";

Create Connection
$conn = new Mysqli ($servername, $username, $password, $dbname);
Check Connection
if ($conn->connect_error) {
Die ("Connection failed:". $conn->connect_error);
} else {
$sql = "INSERT into myguests 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, ' 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.

This argument is one of four types:

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

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 Insert Multiple data