Php_pdo implementing pre-processing statements

Many mature databases support the concept of preprocessing statements (Prepared statements). Can be used in a variety of ways to achieve preprocessing, the following through this article to give you a detailed introduction of the PHP_PDO preprocessing statements, the text through the example code introduced in a very detailed, the need for friends can reference, the following to see together.

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. 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 preprocessing statements there are 3 kinds of binding methods//1. Connecting to a 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//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 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 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 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);}}? >

The above is the whole content of this article, I hope that everyone's study has helped.