[PHP] PDO

PHP Data Objects(PDO) 很早就隨著 PHP 5 一起推出，但是多數的人還是使用 mysql 或 mysqli 的方式與資料庫進行連結，究竟 PDO 有什麼更多的好處？又該怎麼使用？

為什麼我們不繼續使用 mysql 或 mysqli 呢？

1. PDO為物件導向的設計：使用物件導向的PDO，將可以節省更多的時間。
2. PDO為一抽象概念的設計：過去我們連結不同資料庫時（例如，mysql, PostgreSQL, MS SQL Server、SQLite），都需要使用不同的語法（例如，mysql_connect, mssql_connect, pq_connect）。但PDO的特性，是把存取資料庫的動作抽象化，讓使用者可以使用一致的方式存取和處理資料的作業。
3. 你可以將存取出來的資料存入物件當中。
4. mysql 的指令會在 php7 中被移除
5. 避免可能的 sql injection（database access with php）
資料來源 ☞ PHP Data Objects (PDO) 簡易使用說明(1)-啟用PDO讀取資料庫資料

使用 PDO (PHP Data Object) 連接你的資料庫

<?php 
class db extends PDO {
 private $error;
 private $sql;
 private $bind;
 private $errorCallbackFunction;
 private $errorMsgFormat;

 // 連線
 function __construct($config){ 
     try {
         // make the connection
         parent::__construct($config['db_type'].':host='.$config['db_host'].';dbname='.$config['db_name'].';charset=utf8', $config['db_username'], $config['db_password']
                ,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
     
     } catch(PDOException $e){ 
         //otherwise there was an error               
         die('ERROR: '. $e->getMessage());
     }

 } //
  
    // 錯誤處理
 private function debug() {
  if(!empty($this->errorCallbackFunction)) {
   $error = array("Error" => $this->error);
   if(!empty($this->sql))
    $error["SQL Statement"] = $this->sql;
   if(!empty($this->bind))
    $error["Bind Parameters"] = trim(print_r($this->bind, true));

   $backtrace = debug_backtrace();
   if(!empty($backtrace)) {
    foreach($backtrace as $info) {
     if($info["file"] != __FILE__)
      $error["Backtrace"] = $info["file"] . " at line " . $info["line"]; 
    }  
   }

   $msg = "";
   if($this->errorMsgFormat == "html") {
    if(!empty($error["Bind Parameters"]))
     $error["Bind Parameters"] = "<pre>" . $error["Bind Parameters"] . "</pre>";
    $css = trim(file_get_contents(dirname(__FILE__) . "/error.css"));
    $msg .= '<style type="text/css">' . "\n" . $css . "\n</style>";
    $msg .= "\n" . '<div class="db-error">' . "\n\t<h3>SQL Error</h3>";
    foreach($error as $key => $val)
     $msg .= "\n\t<label>" . $key . ":</label>" . $val;
    $msg .= "\n\t</div>\n</div>";
   }
   elseif($this->errorMsgFormat == "text") {
    $msg .= "SQL Error\n" . str_repeat("-", 50);
    foreach($error as $key => $val)
     $msg .= "\n\n$key:\n$val";
   }

   $func = $this->errorCallbackFunction;
   $func($msg);
  }
 }
  
    // 共通
    private function filter($table, $info) {
  $driver = $this->getAttribute(PDO::ATTR_DRIVER_NAME);
  if($driver == 'sqlite') {
   $sql = "PRAGMA table_info('" . $table . "');";
   $key = "name";
  }
  elseif($driver == 'mysql') {
   $sql = "DESCRIBE " . $table . ";";
   $key = "Field";
  }
  else { 
   $sql = "SELECT column_name FROM information_schema.columns WHERE table_name = '" . $table . "';";
   $key = "column_name";
  } 

  if(false !== ($list = $this->run($sql))) {
   $fields = array();
   foreach($list as $record)
    $fields[] = $record[$key];
   return array_values(array_intersect($fields, array_keys($info)));
  }
  return array();
 }

 private function cleanup($bind) {
  if(!is_array($bind)) {
   if(!empty($bind))
    $bind = array($bind);
   else
    $bind = array();
  }
  return $bind;
 }

    public function setErrorCallbackFunction($errorCallbackFunction, $errorMsgFormat="html") {
  //Variable functions for won't work with language constructs such as echo and print, so these are replaced with print_r.
  if(in_array(strtolower($errorCallbackFunction), array("echo", "print")))
   $errorCallbackFunction = "print_r";

  if(function_exists($errorCallbackFunction)) {
   $this->errorCallbackFunction = $errorCallbackFunction; 
   if(!in_array(strtolower($errorMsgFormat), array("html", "text")))
    $errorMsgFormat = "html";
   $this->errorMsgFormat = $errorMsgFormat; 
  } 
 }
  
    // 指令執行
    public function run($sql, $bind="") {
  $this->sql = trim($sql);
  $this->bind = $this->cleanup($bind);
  $this->error = "";
    
  try {
   $pdostmt = $this->prepare($this->sql);
   if($pdostmt->execute($this->bind) !== false) {
    if(preg_match("/^(" . implode("|", array("select", "describe", "pragma")) . ") /i", $this->sql)) {
     return $pdostmt->fetchAll(PDO::FETCH_ASSOC);
          } elseif (preg_match("/^(" . implode("|", array("delete", "insert", "update")) . ") /i", $this->sql)) {
              return $pdostmt->rowCount();
          } 
   } 
  } catch (PDOException $e) {
        $this->error = $e->getMessage();
   $this->debug();
        die('ERROR: '. "Exception occurred !!");
   return false;
  }
 }

    // 刪除
 public function delete($table, $where, $bind="") {
  $sql = "DELETE FROM " . $table . " WHERE " . $where . ";";
  $this->run($sql, $bind);
 }

   // 新增
 public function insert($table, $info) {
  $fields = $this->filter($table, $info);
  $sql    = "INSERT INTO " . $table . " (" . implode($fields, ", ") . ") VALUES (:" . implode($fields, ", :") . ");";
  $bind   = array();
  foreach($fields as $field)
   $bind[":$field"] = $info[$field];

  //echo "{$sql}";
  return $this->run($sql, $bind);
 }

    // 查詢
    public function select($sql, $bind="") {

     $result = $this->run($sql, $bind);
     
     //echo "<pre>";
     //print_r($result);
     //echo "</pre>"; 
     return $result;
 }

 // 修改
 public function update($table, $info, $where, $bind="") {
  $fields = $this->filter($table, $info);
  $fieldSize = sizeof($fields);

  $sql = "UPDATE " . $table . " SET ";
  for($f = 0; $f < $fieldSize; ++$f) {
   if($f > 0)
    $sql .= ", ";
   $sql .= $fields[$f] . " = :update_" . $fields[$f]; 
  }
  $sql .= " WHERE " . $where . ";";

  $bind = $this->cleanup($bind);
  foreach($fields as $field)
   $bind[":update_$field"] = $info[$field];
  
  return $this->run($sql, $bind);
 }
} // class
?>

更改 PDO 連線語法，目的在解決取回的資料庫資料亂碼

舊寫法

$options = array(
  PDO::ATTR_PERSISTENT => true, 
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);

try {
  parent::__construct('mysql::host=localhost;dbname='.資料庫名稱, 帳號, 密碼, $options);
  $this->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, 'SET NAMES utf8');

} catch(PDOException $e){ 
  //otherwise there was an error               
  die('ERROR: '. $e->getMessage());
  exit;
}

變更為

try {
  // PDO::MYSQL_ATTR_INIT_COMMAND 設定編碼
  parent::__construct('mysql::host=localhost;dbname='.資料庫名稱.';charset=utf8', 帳號, 密碼,
  array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

} catch(PDOException $e){ 
  //otherwise there was an error               
  die('ERROR: '. $e->getMessage());
  exit;
}

---

Source

☞ Connecting To Your MySQL Database With PDO (PHP Data Object)
☞ PHP PDO Wrapper Class
☞ PDO database for arabic characters
☞ PHP & MYSQL PDO 使用方法

Image Source

☞ PDO vs. MySQLi: Which Should You Use?