PHP数据对象PDO操作技巧小结

5年以前 | 阅读数：1097 次 | 编程语言：PHP

本文实例讲述了PHP数据对象PDO操作技巧。分享给大家供大家参考，具体如下：

PHP 数据对象（PDO）扩展为PHP访问数据库定义了一个轻量级的一致接口。


    <?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()执行

与query()相比，exec()返回的是受影响行数


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

PDO实现预编译

指的是预先编译sql的结构的一种执行sql的语法

如果执行多条结构相同的sql，编译的中间结果（语法树）应该也是一致的，因此可以将相同的结构，统一编译，每次使用不同的数据执行即可。

编译统一的结构


    $pdoStatement = $pdo->prepare(sql结构)

绑定数据到中间编译结果


    $pdoStatement ->bindValue()

执行


    $pdoStatement ->execute()
    //$sql = "insert into table values(null,?)";
    $sql = "insert into table values(null,:name)";
    $stmt = $pdo->prepare($sql);
    //多组数据也是一编译一执行
    //$stmt->bindValue(1,'bee');
    $stmt->bindValue(':name','bee');
    $res = $stmt->execute();
    var_dump($res);

预编译能更好地防止sql注入，是因为预编译时候不需要用户的数据参与，因此编译时结构固定，所以数据不影响到sql结构。

$pdo->query()与$pdo->execute()如果需要防止sql注入，可以使用$pdo->quote()(其作用是先转义后加引号)

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));
    ?>

运行效果图如下：