Detailed explanation of php_pdo prepared statements

This article mainly introduces the php_pdo preprocessing statement. Not much to say below, let’s take a look at the detailed content.

1. Preprocessing statements can bring two major benefits:

1. The query only needs to be parsed (or preprocessed) once, but can be executed multiple times with the same or different parameters. When a query is ready, the database will analyze, compile, and optimize
the plan for executing the query. For complex queries, this process takes a long time, and if the same query needs to be repeated multiple times with different parameters, this process will significantly slow down the application. By using prepared statements, you can avoid repeated analysis/compile/optimization cycles. In short, prepared statements take up fewer resources and run faster because of
.

2. The parameters provided to the prepared statement do not need to be enclosed in quotation marks, the driver will automatically process them. If the application only uses prepared statements, it can be ensured that SQL injection will not

occur. (However, if other parts of the query are constructed from unescaped input, there is still a risk of SQL injection).

2. Preprocessing example:

<?php
 
//？号式的预处理语句 一共有3种绑定方式
//1.连接数据库 
try{
  $pdo = new PDO("mysql:host=localhost;dbname=jikexueyuan","root","");
}catch(PDOException $e){
  die("数据库连接失败".$e->getMessage());
}
 
//2.预处理的SQL语句
$sql = "insert into stu(id,name,sex,age) values(?,?,?,?)";
$stmt = $pdo->prepare($sql);
 
//3.对?号的参数绑定
//（第一种绑定方式）
 
/* $stmt->bindValue(1,null);
$stmt->bindValue(2,&#39;test55&#39;);
$stmt->bindValue(3,&#39;w&#39;);
$stmt->bindValue(4,22); */
 
//第二种绑定方式
/* $stmt->bindParam(1,$id);
$stmt->bindParam(2,$name);
$stmt->bindParam(3,$sex);
$stmt->bindParam(4,$age);
$id=null;
$name="test66";
$sex="m";
$age=33; */
 
//第三种绑定方式
//$stmt->execute(array(null,&#39;test77&#39;,&#39;22&#39;,55)); 
//4.执行
 
$stmt->execute(array(null,&#39;test77&#39;,&#39;22&#39;,55));
 
echo $stmt->rowCount();

<?php
 
//别名式号式的预处理语句 一共有3种绑定方式
//1.连接数据库 
try{
  $pdo = new PDO("mysql:host=localhost;dbname=jikexueyuan","root","");
}catch(PDOException $e){
  die("数据库连接失败".$e->getMessage());
}
 
//2.预处理的SQL语句
$sql = "insert into stu(id,name,sex,age) values(:id,:name,:sex,:age)";
$stmt = $pdo->prepare($sql);
 
//3.对?号的参数绑定
//（第一种绑定方式）
/* $stmt->bindValue("id",null);
$stmt->bindValue("name",&#39;ceshi1&#39;);
$stmt->bindValue("sex",&#39;w&#39;);
$stmt->bindValue("age",22); */
 
//第二种绑定方式
/* $stmt->bindParam("id",$id);
$stmt->bindParam("name",$name);
$stmt->bindParam("sex",$sex);
$stmt->bindParam("age",$age);
$id=null;
$name="ceshi2";
$sex="m";
$age=33; */
 
//第三种绑定方式
//$stmt->execute(array(null,&#39;test77&#39;,&#39;22&#39;,55)); 
//4.执行
 
$stmt->execute(array("id"=>null,"name"=>"ceshi3","sex"=>"w","age"=>66));
 
echo $stmt->rowCount();

<?php
 
//采用预处理SQL执行查询，并采用绑定结果方式输出
//1.连接数据库 
try{
  $pdo = new PDO("mysql:host=localhost;dbname=jikexueyuan","root","");
}catch(PDOException $e){
  die("数据库连接失败".$e->getMessage());
}
 
//2.预处理的SQL语句
$sql = "select id,name,sex,age from stu";
$stmt = $pdo->prepare($sql);
//3.执行
$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[&#39;id&#39;]."--------".$row[&#39;name&#39;]."<br>";
}
 */

Best way:

//1.连接数据库 
try{
  $pdo = new PDO("mysql:host=localhost;dbname=jikexueyuan","root","");
}catch(PDOException $e){
  die("数据库连接失败".$e->getMessage());
}
 
//2.预处理的SQL语句
$sql = 'select catid,catname,catdir from cy_category where parentid = :parentid';
$stmt = $pdo->prepare($sql);
$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 operation example:

<?php
//用预处理语句进行重复插入
//下面例子通过用 name 和 value 替代相应的命名占位符来执行一个插入查询
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(&#39;:name&#39;, $name);
$stmt->bindParam(&#39;:value&#39;, $value);
 
// 插入一行
$name = &#39;one&#39;;
$value = 1;
$stmt->execute();
 
// 用不同的值插入另一行
$name = &#39;two&#39;;
$value = 2;
$stmt->execute();
 
//用预处理语句进行重复插入
//下面例子通过用 name 和 value 取代 ? 占位符的位置来执行一条插入查询。
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);
 
// 插入一行
$name = &#39;one&#39;;
$value = 1;
$stmt->execute();
 
// 用不同的值插入另一行
$name = &#39;two&#39;;
$value = 2;
$stmt->execute();
 
//使用预处理语句获取数据
//下面例子获取数据基于键值已提供的形式。用户的输入被自动用引号括起来，因此不会有 SQL 注入攻击的危险。
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
if ($stmt->execute(array($_GET[&#39;name&#39;]))) {
 while ($row = $stmt->fetch()) {
  print_r($row);
 }
}
?>

Summary

The above is the entire content of this article. I hope the content of this article can be helpful to everyone in learning or using PHP. If you have any questions, you can leave a message to communicate.

For more detailed explanations of php_pdo preprocessing statements and related articles, please pay attention to the PHP Chinese website!