PHP PDO preprocessing Statement Example

What is a preprocessing statement? It can be thought of as a compiled template of the SQL you want to run, and it can be customized using variable parameters. Preprocessing statements can provide two major benefits:

• Queries need to be parsed (or preprocessed) only once, but can be executed multiple times with the same or different parameters. When the query is ready, the database parses, compiles, and optimizes the plan for executing 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 greatly reduce the speed of your application. By using preprocessing statements, you can avoid repeating the parse/compile/optimize cycle. In short, preprocessing statements consume less resources and thus run faster.

• 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 SQL injection does not occur. (However, there is still a risk of SQL injection if other parts of the query are built from an escaped input).

Preprocessing statements are so useful that their only feature is that PDO will simulate processing when the driver is not supported. This ensures that the application is able to use the same data access pattern regardless of whether the database has such functionality.

Preprocessing instances:

<?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. Parameter binding//(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; *///the third Way of binding//$stmt->execute (Array (null, ' test77 ', ' 22 ', 55)); 4. Execute $stmt->execute (Array (null, ' test77 ', ' $ ')), echo $stmt->rowcount (); 

<?php//alias type of preprocessing 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; *///the third Way of binding//$stmt->execute (Array (null, ' test77 ', ' 22 ', 55)); 4. Execute $stmt->execute ("id" =>null, "name" = "Ceshi3", "Sex" = "w", "Age" =>66)); Echo $stmt RowCount (); 

<?php//uses preprocessing SQL to execute the query, and outputs the//1 using the binding results. Connect to the database try{  $pdo = new PDO ("Mysql:host=localhost;dbname=jikexueyuan", " Root "," ");} catch (Pdoexception $e) {die  ("Database connection Failed". $e->getmessage ());} 2. Pre-processed 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 ($s QL); $params = Array (  ' parentid ' = $subcatid); $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//repeated insertions 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 one line $name = ' one '; $value = 1; $stmt->execute ();//insert another line with different values $name = ' two '; $value = 2 ; $stmt->execute ();//Repeat insert with preprocessing statement//The following example is replaced by 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 one line $name = ' one '; $value = 1; $stmt->execute ();// Insert another line with different values $name = ' two '; $value = 2; $stmt->execute ();//Use preprocessing statements to get data//The following example gets the data based on the form that the key value has provided. 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);}}?