Related knowledge of PHP MySQL preprocessing statements

PHP MySQL preprocessing statement is very important in PHP, this article will learn more about the PHP MySQL preprocessing statements.

preprocessing statements and binding parameters

Preprocessing statements are used to execute multiple identical SQL statements and perform more efficiently.

The preprocessing statement works as follows:

Preprocessing: Creates a SQL statement template and sends it to the database. The reserved value uses the parameter "?" tag. For example:

INSERT into Myguests (firstname, LastName, email) VALUES (?,?,?)

Database parsing, compiling, executing query optimizations on SQL statement templates, and storing results without output.

Execution: Finally, the value of the applied binding is passed to the parameter ("?" tag), and the database executes the statement. The application can execute the statement multiple times if the value of the parameter is different.

Preprocessing statements have two main advantages over direct execution of SQL statements:

Preprocessing statements greatly reduce the parsing time, and only one query is made (although the statements are executed multiple times).

The binding parameters reduce the server bandwidth, and you only need to send the parameters of the query, not the entire statement.

Preprocessing statements are useful for SQL injection, since parameter values are sent with different protocols to ensure the legitimacy of the data.

MYSQLI preprocessing statements

The following example uses a preprocessing statement in mysqli and binds the corresponding parameters:

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);} preprocessing and binding $stmt = $conn->prepare ("INSERT into Myguests (firstname, LastName, email) VALUES (?,?,?)"); $stmt->bind_param ("SSS", $firstname, $lastname, $email); Set the parameters and execute $firstname = "John"; $lastname = "Doe"; $email = "john@example.com"; $stmt->execute (); $firstname = "Mary"; $lastname = "Moe"; $email = "mary@example.com"; $stmt->execute (); $firstname = "Julie"; $lastname = "Dooley"; $email = "julie@example.com"; $stmt->execute (); echo "New record inserted successfully"; $stmt->close (); $conn->close ();? >

Parse each line of code for the following instance:

"INSERT into Myguests (firstname, LastName, email) VALUES (?,?,?)"

In the SQL statement, we used the question mark (?), where we can replace the question mark with an integer, a string, a double-precision float, and a Boolean value.

Next, let's take a look at the Bind_param () function:

$stmt->bind_param ("SSS", $firstname, $lastname, $email);

The function binds the arguments to the SQL and tells the values of the database parameters. The SSS parameter column handles the data type of the remaining parameters. The S character tells the database that the parameter is a string.

The following four types of parameters are available:

I-integer (integral type)

D-double (double-precision floating-point type)

S-string (String)

B-blob (Binary Large object: Binary Large Objects)

Each parameter requires a specified type.

By telling the data type of a database parameter, you can reduce the risk of SQL injection.

Note: If you want to insert additional data (user input), validation of the data is very important.

Pre-processing statements in PDO

The following example we used a preprocessing statement in PDO and bound parameters:

Instance (PDO uses preprocessing statements)

<?php$servername = "localhost"; $username = "username"; $password = "password"; $dbname = "Mydbpdo";    try {$conn = new PDO ("mysql:host= $servername;d bname= $dbname", $username, $password);     Set PDO error mode to exception $conn->setattribute (Pdo::attr_errmode, pdo::errmode_exception); preprocessing SQL and binding parameters $stmt = $conn->prepare ("INSERT into Myguests (firstname, LastName, email) VALUES (: FirstName,:    LastName,: Email) ");    $stmt->bindparam (': FirstName ', $firstname);    $stmt->bindparam (': LastName ', $lastname);     $stmt->bindparam (': email ', $email);    Insert Row $firstname = "John";    $lastname = "Doe";    $email = "john@example.com";     $stmt->execute ();    Insert another line $firstname = "Mary";    $lastname = "Moe";    $email = "mary@example.com";     $stmt->execute ();    Insert another line $firstname = "Julie";    $lastname = "Dooley";    $email = "julie@example.com";     $stmt->execute (); echo "New record inserted successfully";} catch (Pdoexception $e) {echo "Error:". $e->getmessage ();} $conn = null;? >

This article detailed the PHP MySQL preprocessing statement related knowledge, the more study material clear concerns the PHP Chinese net to be able to watch.