PHP_PDO preprocessing statements

This article is mainly about PHP_PDO preprocessing statements, the following words do not say, we look at the detailed content.

First, preprocessing statements can bring two major benefits:

1. The query only needs to parse (or preprocess) once, but it can be executed multiple times with the same or different parameters. When the query is ready, the database will parse, compile, and optimize
The plan that executes the query. For complex queries, this process takes a long time, and if you need to repeat the same query multiple times with different parameters, the process will be large
Greatly reduce the speed of the application. By using preprocessing statements, you can avoid repeating the parse/compile/optimize cycle. In short, pre-processing statements consume less resources because
and run faster.

2. The parameters provided to the preprocessing statements do not need to be enclosed in quotation marks, and the driver will handle them automatically. If your application uses only preprocessing statements, you can ensure that you do not
SQL injection occurs. (However, there is still a risk of SQL injection if other parts of the query are built from an escaped input).

Second, the pretreatment example:

<?php//? There are 3 kinds of binding methods for the preprocessing statements of//1. Connect to database try{$pdo = new PDO ("Mysql:host=localhost;dbname=jikexueyuan", "Root", "");} catch (Pdoexception $e) {die ("Database connection Failed". $e->getmessage ())}//2. preprocessed SQL statement $sql = "INSERT into Stu (id,name,sex,age) VALUES (?,?,?,?) "; $stmt = $pdo->prepare ($sql); 3. Bind the parameter of the No.//(First binding method)/* $stmt->bindvalue (1,null), $stmt->bindvalue (2, ' test55 '); $stmt->bindvalue (3, ' W ') ); $stmt->bindvalue (4,22); *///Second binding method/* $stmt->bindparam (1, $id); $stmt->bindparam (2, $name); $stmt->bindparam (3, $sex); $stmt Bindparam (4, $age); $id =null; $name = "Test66"; $sex = "M"; $age = 33; *///Third binding mode//$stmt->execute (Array (null, ' test77 ', ' 22 ', 55)); 4. Execute $stmt->execute (array (null, ' test77 ', ' 22 ', 55)); echo $stmt->rowcount (); 

<?php//alias number of pre-processing statements there are 3 ways to bind//1. Connect to database try{$pdo = new PDO ("mysql:host= Localhost;dbname=jikexueyuan "," Root "," ");} catch (Pdoexception $e) {die ("Database connection Failed". $e->getmessage ())}//2. preprocessed SQL statement $sql = "INSERT into Stu (id,name,sex,age) VALUES (: Id,:name,:sex,:age) "; $stmt = $pdo->prepare ($sql); 3. Bind//(First binding method)/* $stmt->bindvalue ("id", null), $stmt->bindvalue ("name", "Ceshi1"); $stmt Bindvalue ("Sex", ' w '); $stmt->bindvalue ("age", 22); *///The second binding method/* $stmt->bindparam ("id", $id), $stmt->bindparam ("name", $name); $stmt->bindparam ("Sex", $sex); $ Stmt->bindparam ("Age", $age); $id =null; $name = "Ceshi2"; $sex = "M"; $age = 33; *///Third binding mode//$stmt->execute (Array (null, ' test77 ', ' 22 ', 55)); 4. Execution $stmt->execute ("id" =>null, "name" = "Ceshi3", "Sex" = "w", "Age" =>66)); echo $stmt->rowcount (); 

<?php//Use preprocessing SQL to execute the query, and output//1 with the result of binding. Connect to the database try{  $pdo = new PDO ("Mysql:host=localhost;dbname=jikexueyuan", " Root "," ");} catch (Pdoexception $e) {die  ("Database connection Failed". $e->getmessage ())}//2. preprocessed SQL statement $sql = "Select Id,name,sex,age from Stu "; $stmt = $pdo->prepare ($sql);//3. Execute $stmt->execute (); $stmt->bindcolumn (1, $id); $stmt->bindcolumn (2, $name); $stmt->bindcolumn ("Sex", $sex); $stmt Bindcolumn ("Age", $age); while ($row = $stmt->fetch (pdo::fetch_column)) {  echo "{$id}:{$name}:{$sex}:{$age}<br>";} /* foreach ($stmt as $row) {  echo $row [' ID ']. " --------". $row [' name ']." <br> ";} */

Best way:

1. Connect the database try{  $pdo = new PDO ("Mysql:host=localhost;dbname=jikexueyuan", "Root", "");} catch (Pdoexception $e) {die  ("Database connection Failed". $e->getmessage ())}//2. preprocessed SQL statement $sql = ' Select Catid,catname,catdir From cy_category where parentid =:p Arentid '; $stmt = $pdo->prepare ($sql); $params = Array (  ' parentid ' = = $subcat ID); $stmt->execute ($params); $row = $stm->fetchall (PDO::FETCH_ASSOC), while ($row = $stmt->fetch (PDO::FETCH_ASSOC)) {  var_dump ($row);  echo "<br>";}

Preprocessing Batch operations Examples:

<?php//repeating inserts with preprocessing statements//The following example executes an insert query by replacing the corresponding named placeholder with name and value $stmt = $dbh->prepare ("INSERT into REGISTRY (name, Value) VALUES (: Name,: value), $stmt->bindparam (': Name ', $name), $stmt->bindparam (': Value ', $value); Insert a line $name = ' one '; $value = 1; $stmt->execute (); Insert another line with a different value $name = ' two '; $value = 2; $stmt->execute (); Repeat insert with preprocessing statement//The following example is replaced by the name and value? Placeholder to execute an insert query. $stmt = $dbh->prepare ("INSERT into REGISTRY (name, value) VALUES (?,?)"); $stmt->bindparam (1, $name); $stmt->bindparam (2, $value); Insert a line $name = ' one '; $value = 1; $stmt->execute (); Insert another line with a different value $name = ' two '; $value = 2; $stmt->execute (); Get the data using a preprocessing statement//The following example gets the data based on the form provided by the key value. The user's input is automatically enclosed in quotation marks, so there is no risk of SQL injection attacks. $stmt = $dbh->prepare ("select * from REGISTRY where name =?"); if ($stmt->execute (Array ($_get[' name '))) {while ($row = $stmt->fetch ()) {  print_r ($row);}}? >

Summarize

The above is the entire content of this article, I hope that the content of this article for you to learn or use PHP can help, if there are questions you can message exchange.