PDO预处理

PDO预处理

PDOStatement类：准备语句，处理结果集也就是预处理,安全,高效,推荐使用

两种占位符号:?参数         索引数组,按索引顺序使用
             名子参数      关联数组,按名称使用,和顺序无关,以冒号开头,自己定义

$stmt=$pdo->prepare($sql);     $sql可是是任意sql语句,这与mysqli不同

两种点位符号

<?php
  try{
    $pdo=new PDO("mysql:host=localhost;dbname=mysqldb","root","snail");
  }catch(PDOException $e){
    echo $e->getMessage();
  }

  //准备一条语句,并放到服务器端,而且编译
  $stmt=$pdo->prepare("insert into shop(name,price)values(?,?)");
//  $stmt=$pdo->prepare("insert into shop(name,price)values(:na,:pr)");

  //绑定参数(变量和参数绑定)
  $stmt->bindparam(1,$name);
  $stmt->bindparam(2,$price);

//  $stmt->bindparam(":na",$name);
//  $stmt->bindparam(":pr",$price);

  $name="liwu11";
  $price=234.4311;

  if($stmt->execute()){
    echo "执行成功";
    echo "最后插入的ID:".$pdo->lastInsertId();
  }else{
    echo "执行失败";
  }
?>

<?php
//以数组方式向服务器传值 

 try{
    $pdo=new PDO("mysql:host=localhost;dbname=mysqldb","root","snail");
  }catch(PDOException $e){
    echo $e->getMessage();
  }

  $stmt=$pdo->prepare("select * from shop where id >:id");

  $stmt->execute(array(':id'=>130));

  $row=$stmt->fetch();
  print_r($row);
  echo '<br>';
?>

<?php

//用fetch(),fetchAll()来获取查询结果
  try{
    $pdo=new PDO("mysql:host=localhost;dbname=mysqldb","root","snail");
  }catch(PDOException $e){
    echo $e->getMessage();
  }

  $stmt=$pdo->prepare("select * from shop where id >:id");

  $stmt->execute(array(':id'=>130));

/*  单条获取fetch()
  $stmt->setFetchMode(PDO::FETCH_ASSOC);  //设置获取模式
  while($row=$stmt->fetch()){
    print_r($row);
    echo '<br>';
  }
*/

  //多条获取fetchAll()
//  $stmt->setFetchMode(PDO::FETCH_ASSOC);
  $data=$stmt->fetchAll(PDO::FETCH_ASSOC);  //也可以用上句进行设置
  echo '<pre>';
  print_r($data);
  echo '</pre>';
?>

<?php
//以表格输出查询结果 

try{
    $pdo=new PDO("mysql:host=localhost;dbname=mysqldb","root","snail");
  }catch(PDOException $e){
    echo $e->getMessage();
  }

  $stmt=$pdo->prepare("select id,name,price from shop where id >:id");

  $stmt->execute(array(':id'=>130));

  $stmt->bindColumn(id,$id);
  $stmt->bindColumn(name,$name);
  $stmt->bindColumn(price,$price);

  echo '<table border=1 align="center" width=800px>';
  echo '<tr>';
  for($i=0;$i<$stmt->columncount();$i++){
    $field=$stmt->getColumnMeta($i);
    echo '<th>'.$field["name"].'</th>';
  }
  echo "</tr>";


  while($stmt->fetch()){
    echo '<tr>';
    echo '<td>'.$id.'</td>';
    echo '<td>'.$name.'</td>';
    echo '<td>'.$price.'</td>';
    echo '</tr>';
  }

  echo '</table>';

  echo "行:".$stmt->rowcount()."<br>";
  echo "列:".$stmt->columncount()."<br>";

?>