PHP數(shù)據(jù)對象PDO操作技巧小結(jié)

更新時間：2016年09月27日 11:28:56 作者：ligbee

這篇文章主要介紹了PHP數(shù)據(jù)對象PDO操作方法,結(jié)合實例形式總結(jié)分析了php基于pdo的各種常見數(shù)據(jù)庫操作相關技巧與注意事項,需要的朋友可以參考下

本文實例講述了PHP數(shù)據(jù)對象PDO操作技巧。分享給大家供大家參考，具體如下：

PHP 數(shù)據(jù)對象（PDO）擴展為PHP訪問數(shù)據(jù)庫定義了一個輕量級的一致接口。

<?php
 try {
  $dsn = "mysql:host=localhost; port=3306; dbname=wsq_hotel; charset=utf-8";
  $user = 'root';
  $psw ='root';
  $pdo = new PDO($dsn,$user,$psw);
  $sql = 'select goods_prices from wsq_goods_info where goods_id=2';
  // $sql = "show database";
  $res = $pdo->query($sql) or var_dump($pdo->errorInfo());
  // var_dump($res);
  $mon = $res->fetch(PDO::FETCH_ASSOC);
  echo $mon['goods_price'];
 } catch (PDOException $e) {
  echo $e->getMessage();
 }
?>

PDO操作事務

//開啟事務
beginTransacition()
//回滾
rollback()
//提交
commit()
//判斷是否處于事務之中
inTransaction()

返回最后插入行的ID

PDO::lastInsertID()

exec()執(zhí)行

與query()相比，exec()返回的是受影響行數(shù)

$sql = "insert into table values('$val')";
if(false===$pdo->exec($sql)){
 echo '執(zhí)行失敗';
}

PDO實現(xiàn)預編譯

指的是預先編譯sql的結(jié)構(gòu)的一種執(zhí)行sql的語法

如果執(zhí)行多條結(jié)構(gòu)相同的sql，編譯的中間結(jié)果（語法樹）應該也是一致的，因此可以將相同的結(jié)構(gòu)，統(tǒng)一編譯，每次使用不同的數(shù)據(jù)執(zhí)行即可。

編譯統(tǒng)一的結(jié)構(gòu)

$pdoStatement = $pdo->prepare(sql結(jié)構(gòu))

綁定數(shù)據(jù)到中間編譯結(jié)果

$pdoStatement ->bindValue()

執(zhí)行

$pdoStatement ->execute()
//$sql = "insert into table values(null,?)";
$sql = "insert into table values(null,:name)";
$stmt = $pdo->prepare($sql);
//多組數(shù)據(jù)也是一編譯一執(zhí)行
//$stmt->bindValue(1,'bee');
$stmt->bindValue(':name','bee');
$res = $stmt->execute();
var_dump($res);

預編譯能更好地防止sql注入，是因為預編譯時候不需要用戶的數(shù)據(jù)參與，因此編譯時結(jié)構(gòu)固定，所以數(shù)據(jù)不影響到sql結(jié)構(gòu)。

$pdo->query()與$pdo->execute()如果需要防止sql注入，可以使用$pdo->quote()(其作用是先轉(zhuǎn)義后加引號)

PDOstatement常用方法：

errorInfo()
errorCode()
fetchColumn()
fetch()
fetchAll()
rowCount()
closeCursor()

pdo應用

<?php
header('content-type:text/html;charset=utf-8');
 class PDODB{
  static private $_init;
  private $_host;
  private $_port;
  private $_dbname;
  private $_username;
  private $_password;
  private $_charset;
  private $_dns;
  private $_pdo;
  private function __construct($config){
   $this->_initParamas($config);
   $this->_initDNS();
   $this->_initDriverOptions();
   $this->_initPDO();
  }
  private function __clone(){}
  static public function getInstance($config){
   if(!static::$_init instanceof static){
    static::$_init = new static($config);
   }
   return static::$_init;
  }
  private function _initParamas($config){
   $this->_host = isset($config['host'])?$config['host']:'localhost';
   $this->_port = isset($config['port'])?$config['port']:'3306';
   $this->_dbname = isset($config['dbname'])?$config['dbname']:'';
   $this->_username = isset($config['username'])?$config['username']:'root';
   $this->_passward = isset($config['passward'])?$config['passward']:'';
   $this->_charset = isset($config['charset'])?$config['charset']:'utf8';
  }
  private function _initDNS(){
   $this->_dns = "mysql:host=$this->_host;port=$this->_port;dbname=$this->_dbname";
  }
  private function _initDriverOptions(){
   $this->_driverOptions = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => "set names $this->_charset"
   );
  }
  private function _initPDO(){
   $this->_pdo = new PDO($this->_dns,$this->_username,$this->_passward,$this->_driverOptions) or die("fail");
  }
  public function query($sql){
   if(!$result = $this->_pdo->query($sql)){
    $erro = $this->_pdo->errorInfo();
    echo '失敗的語句'.$sql.'<br>';
    echo '錯誤代碼'.$erro[1].'<br>';
    echo '錯誤信息'.$erro[2].'<br>';
    die;
   }
   return $result;
  }
  public function fetchAll($sql){
   $res = $this->query($sql);
   $list = $res->fetchAll(PDO::FETCH_ASSOC);
   $res->closeCursor();
   return $list;
  }
  public function fetchRow($sql){
   $res = $this->query($sql);
   $row = $res->fetch(PDO::FETCH_ASSOC);
   $res->closeCursor();
   return $row;
  }
  public function fetchOne($sql){
   $res = $this->query($sql);
   $one = $res->fetchColumn();
   $res->closeCursor();
   return $one;
  }
  public function escape_string($data){
   return $this->_pdo->quote($data);
  }
 }
 $config = array(
  "host"=>"localhost",
  "username"=>"root",
  "passward"=>"root",
  "dbname"=>"students"
 );
 $pdo = PDODB::getInstance($config);
 $sql = "select sdept from student where sage=21";
 var_dump($pdo->fetchRow($sql));
?>

運行效果圖如下：