PDO (PHP Data Object) 提供了一個 Abstraction Layer 來操作資料庫
連接DB
- define('DB_NAME','mydb');
- define('DB_USER','username');
- define('DB_PASSWD','userpwd');
- define('DB_HOST','localhost');
- define('DB_TYPE','mysql');
- try{
- $dbh = new PDO(DB_TYPE.':host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASSWD,
- array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
- $dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); //錯誤訊息提醒
- }
- catch (PDOException $e){
- echo 'Error!: ' . $e->getMessage() . '<br />';
- }
- PDO 是 Abstraction Layer 所以就算更換儲存媒介，需要花的功夫比起來是最少的;將DB_TYPE改掉,即可移到不同DB使用。
- PDO 可以透過 PDO::setAttribute 來決定連線時的設定，像是 Persistent Connection, 回傳錯誤的方式(Exception, E_WARNING, NULL)。甚至是回傳欄位名稱的大小寫…等等
Connections and Connection management
PDO::setAttribute
PDOException
查詢
ex1:- $sql = 'select ArticleDate,ChineseTitle,SubChineseTitle from Article where
ArticleDate=:seldate'; - $sth = $dbh->prepare($sql);
- $sth ->execute(array(':seldate' => $seldate));
ex2:
- $sth = $dbh->prepare('insert into Article(ArticleDate,Type,EngTitle,ChineseTitle)values (:date,:type,:et,:ct)');
- $sth->bindParam(':date',$seldate);
- $sth->bindParam(':type',$_POST["type"], PDO::PARAM_STR,15);
- $sth->bindParam(':et',$_POST["EngTitle"], PDO::PARAM_STR,30);
- $sth->bindParam(':ct',$_POST["ChineseTitle"], PDO::PARAM_STR,30);
- $sth->execute();
- PDO 支援 Bind Column 的功能，除了基本的 Prepare, Execute 以外，也可以 Bind 單一欄位，並且指定欄位型態。
Predefined Constants
PDO::PARAM PDOStatement 取資料
ex1:- $obj = $sth->fetch(PDO::FETCH_ASSOC);
- echo $obj["ArticleDate"];
- echo $obj["ChineseTitle"];
- echo $obj["SubChineseTitle"];
ex2:
- $obj = $sth->fetch(PDO::FETCH_OBJ);
- echo $obj->ChineseTitle
- echo $obj->SubChineseTitle
PDOStatement::fetch
PDOStatement::fetchAll
參考資料
PDO vs. MySQLi: Which Should You Use?
淺談 PHP-MySQL, PHP-MySQLi, PDO 的差異
PHP MYSQL PDO
[解決] Undefined class constant 'MYSQL_ATTR_INIT_COMMAND'
PDO 基本用法
Comparison of PHP Database Abstraction Classes
