PHP教學-PDO 介紹
PDO (PHP Data Object)資料庫抽象層
PDO 的特性之一，就是把存取資料庫的動作抽象化（data-access abstraction layer），
讓使用者可以用一致的介面來處理與資料庫相關作業。
PDO 在 PHP 5.1 才正式被支援，在 PHP 5.0 時是被當作 PECL 擴充，
在 PHP 5.0 之前無法使用 PDO。
以一個最簡單的例子來比較兩者寫法差異。
舊的寫法：
1: $dbHost = 'localhost';
2: $user = 'username;
3: $password = 'password';
4:
5: $link = mysql_connect($dbHost, 'mysql_user', 'mysql_password');
6: if (!$link) {
7: die('Could not connect: ' . mysql_error());
8: }
9:
10: $result = mysql_query("SELECT max(sid) FROM content;", $link);
11: if (!$result) {
12: echo 'Could not run query: ' . mysql_error();
13: exit;
14: }
15:
16: list($sid) = mysql_fetch_row($result);
17:
18: mysql_close($link);
使用 PDO 的寫法：
1: $dsn = 'mysql:dbname=testdb;host=127.0.0.1';
2: $user = 'username;
3: $password = 'password';
4:
5: // connect to the db
6: try {
7: $link = new PDO($dsn, $user, $password);
8: } catch (PDOException $e) {
9: printf("DatabaseError: %s ", $e->getMessage());
10: }
11:
12: // query data
13: list($sid) = $link->query("SELECT max(sid) FROM content;")->fetch();
14:
15: // close the db link
16: $link = null;
怎知用哪種類型資料庫？
其實答案就在第一行，很明顯是在定義 mysql 資料庫：「mysql:dbname」，
換句話說，如果要連結 PostgreSQL 則使用（以此類推）：
1: $dsn = 'pgsql:dbname=testdb;host=127.0.0.1';
2: $dbh = new PDO($dsn, $username, $password);
可以發現只要連結時設定不同的資料庫型態，
接下來呼叫都可以採相同模式、相同的函示名稱，
將來如果要換資料庫時非常方便，看起來也比較清爽～～ :D
特別注意 PDO::query()、PDO::exec()、PDO::prepare() 的差別：
PDO::query()
Executes an SQL statement, returning a result set as a PDOStatement object
用於會回傳 result set 的 SQL 操作，例如 SELECT。
PDO::exec()
Execute an SQL statement and return the number of affected rows
針對沒有 result set 的 SQL 操作，會回傳所影響的列數，例如 INSERT、UPDATE、DELETE。
PDO::prepare()
Prepares a statement for execution and returns a statement object
接下來要談到 Prepared Statements（又稱 parameterized statement）。
之前看到有人的 SQL script 中出現一些問號「?」，
問同事說是 PDO 的指令，
但我研究之後，發現這跟 PDO 無關，是 DBMS 的特性之一，
其最大好處在於
高查詢效能：當需要執行同個 script 很多次時
高安全性：將 SQL script 與須查詢的欄位關鍵字（data）分開
尤其是第二個特性最重要，可解決 SQL injection 的問題，
至於原理為何可以看這篇，有很精闢的解釋。
Prepared Statements 的運作流程（來自 Wiki）：
Prepare: The statement template is created by the application and sent to the database management system (DBMS). Certain values are left unspecified, called parameters, placeholders or bind variables (labelled "?" below):
INSERT INTO PRODUCT (name, price) VALUES (?, ?)
準備步驟：首先使用者撰寫 statement template，跟一般的 SQL statement 很像，但查詢對應值用「?」替代（稱為 parameters、placeholders 或 bind variables），可視其為參數，就像以前會用 ($name, $price) 這樣。
The DBMS parses, compiles, and performs query optimization on the statement template, and stores the result without executing it.
DBMS 會對該 statement template 剖析、編譯並且處理 query 最佳化，然後將最佳化的結果存下來但不會去執行它。
Execute: At a later time, the application supplies (or binds) values for the parameters, and the DBMS executes the statement (possibly returning a result). The application may execute the statement as many times as it wants with different values. In this example, it might supply 'Bread' for the first parameter and '1.00' for the second parameter.
執行步驟：當使用者為這些參數「?」綁定值（例如第一個 ? 給值 'Bread'，第二個 ? 給值 '1.00'）後，DBMS 便會執行剛剛最佳化的 statement。
使用者可以在程式中多次用不同的值去執行該 statement。
這樣的流程可以看出，
為什麼當同個 script 需要執行多次時，效率會很好；
但另一方面，如果只需要執行一次，
效率當然會比不使用 prepared statement 技術差～～XD
但覺得光是可以避開 SQL injection 就值得使用它了。
大部分的 DBMS 都有支援 Prepared Statements，
例如： MySQL、Oracle、DB2、Microsoft SQL Server 和 PostgreSQL；
也有些程式語言會在標準函式庫中，以模擬的方式來支援 Prepared Statements，
這表示儘管它們所連結的資料庫並不支援 Prepared Statements，
這些程式語言還是能藉由模擬來達到相同的效果，
例如：Java's JDBC、Perl's DBI、PHP's PDO 和 Python's DB-API.
以 PHP 為例，PDO 的範例如下：
1: $stmt = $dbh->prepare("SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?");
2: $stmt->execute(array($username, $password));
Mysqli（MySQL Improved Extension）也有支援 Prepared Statements，
但那是使用 MySQL 本身的功能，並非在 client 端（指的是 PHP 端）處理。
Mysqli 範例：
1: /* Prepared statement, stage 1: prepare */
2: if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
3: echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
4: }
5:
6: /* Prepared statement, stage 2: bind and execute */
7: $id = 1;
8: if (!$stmt->bind_param("i", $id)) {
9: echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
10: }
11:
12: if (!$stmt->execute()) {
13: echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
14: }
其中 bind_param() 第一個參數代表「型別」：
i：integer
d：double
s：string
b：a blob and will be sent in packets
可以一次設定數個參數：
1: $stmt->bind_param('dsss', $source_id, $source_name, $source_gender, $source_location);
可以檢查型別更讓人放心～～XD
雖然上述 PDO 範例採取 array 的方式來 bind value，
但它其實也有類似 bind_param 的函式：bindParam()。
範例：
1: $calories = 150;
2: $colour = 'red';
3: $sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < ? AND colour = ?');
4: $sth->bindParam(1, $calories, PDO::PARAM_INT);
5: $sth->bindParam(2, $colour, PDO::PARAM_STR, 12);
6: $sth->execute();
bindParam() 的第一個參數，代表是要 bind 第幾個 placeholder 的 index（由 1 開始） ，
但腦筋動得快的人就會想到，
如果到時候 script 的條件要增加，或是 placeholder 的順序有調換，
那不就要重新編 index？也太麻煩。
所以還提供另一種 named placeholders 的方法 :param_name，
範例：
1: $calories = 150;
2: $colour = 'red';
3: $sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour');
4: $sth->bindParam(':calories', $calories, PDO::PARAM_INT);
5: $sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
6: $sth->execute();
這樣不管 placeholder 如何變動，bindParam() 部分都不需要跟著更改～～ ^_^
Named placeholders 的設定方式也可以用在 array 方式上：
1: $sql = 'SELECT * FROM some_table WHERE some_value > :value OR some_value < :value';
2: $stmt = $dbh->prepare($sql);
3: $stmt->execute( array( ':value' => 3 ) );
這一切努力除了是要避免 SQL injection 外，也是為了讓 SQL script 更簡潔，
避免以往為了串參數值，
或是為了避免 SQL injection 而出現一堆 mysql_real_escape_string() 導致的雜亂狀況，
讓 script 與 parameter 區隔開來，更顯結構化。
在查 bindParam() 資料時也看到 bindValue()，
連範例都很類似，
兩者的區別在於「bind variable」和「bind value」的不同，
前者是一個 reference 後者就純粹是個 value 而已。
一個簡單的 bindParam 範例：
1: $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
2: $stmt->bindParam(':name', $name);
3: $stmt->bindParam(':value', $value);
4:
5: // insert one row
6: $name = 'one';
7: $value = 1;
8: $stmt->execute();
9:
10: // insert another row with different values
11: $name = 'two';
12: $value = 2;
13: $stmt->execute();
可以看到 bindParam() 只要先 bind variable 後，
之後就可以一直指定這些 variables 的值，然後呼叫 execute() 來執行。
但是 bindValue() 事實上就只是給值而已，無 reference 的效果：
1: $calories = 150;
2: $colour = 'red';
3:
4: $sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour');
5: $sth->bindValue(':calories', $calories, PDO::PARAM_INT);
6: $sth->bindValue(':colour', $colour, PDO::PARAM_STR);
7: $sth->execute();
跟直接填值入該參數是相同的道理。
PDO 的特性之一，就是把存取資料庫的動作抽象化（data-access abstraction layer），
讓使用者可以用一致的介面來處理與資料庫相關作業。
PDO 在 PHP 5.1 才正式被支援，在 PHP 5.0 時是被當作 PECL 擴充，
在 PHP 5.0 之前無法使用 PDO。
以一個最簡單的例子來比較兩者寫法差異。
舊的寫法：
1: $dbHost = 'localhost';
2: $user = 'username;
3: $password = 'password';
4:
5: $link = mysql_connect($dbHost, 'mysql_user', 'mysql_password');
6: if (!$link) {
7: die('Could not connect: ' . mysql_error());
8: }
9:
10: $result = mysql_query("SELECT max(sid) FROM content;", $link);
11: if (!$result) {
12: echo 'Could not run query: ' . mysql_error();
13: exit;
14: }
15:
16: list($sid) = mysql_fetch_row($result);
17:
18: mysql_close($link);
使用 PDO 的寫法：
1: $dsn = 'mysql:dbname=testdb;host=127.0.0.1';
2: $user = 'username;
3: $password = 'password';
4:
5: // connect to the db
6: try {
7: $link = new PDO($dsn, $user, $password);
8: } catch (PDOException $e) {
9: printf("DatabaseError: %s ", $e->getMessage());
10: }
11:
12: // query data
13: list($sid) = $link->query("SELECT max(sid) FROM content;")->fetch();
14:
15: // close the db link
16: $link = null;
怎知用哪種類型資料庫？
其實答案就在第一行，很明顯是在定義 mysql 資料庫：「mysql:dbname」，
換句話說，如果要連結 PostgreSQL 則使用（以此類推）：
1: $dsn = 'pgsql:dbname=testdb;host=127.0.0.1';
2: $dbh = new PDO($dsn, $username, $password);
可以發現只要連結時設定不同的資料庫型態，
接下來呼叫都可以採相同模式、相同的函示名稱，
將來如果要換資料庫時非常方便，看起來也比較清爽～～ :D
特別注意 PDO::query()、PDO::exec()、PDO::prepare() 的差別：
PDO::query()
Executes an SQL statement, returning a result set as a PDOStatement object
用於會回傳 result set 的 SQL 操作，例如 SELECT。
PDO::exec()
Execute an SQL statement and return the number of affected rows
針對沒有 result set 的 SQL 操作，會回傳所影響的列數，例如 INSERT、UPDATE、DELETE。
PDO::prepare()
Prepares a statement for execution and returns a statement object
接下來要談到 Prepared Statements（又稱 parameterized statement）。
之前看到有人的 SQL script 中出現一些問號「?」，
問同事說是 PDO 的指令，
但我研究之後，發現這跟 PDO 無關，是 DBMS 的特性之一，
其最大好處在於
高查詢效能：當需要執行同個 script 很多次時
高安全性：將 SQL script 與須查詢的欄位關鍵字（data）分開
尤其是第二個特性最重要，可解決 SQL injection 的問題，
至於原理為何可以看這篇，有很精闢的解釋。
Prepared Statements 的運作流程（來自 Wiki）：
Prepare: The statement template is created by the application and sent to the database management system (DBMS). Certain values are left unspecified, called parameters, placeholders or bind variables (labelled "?" below):
INSERT INTO PRODUCT (name, price) VALUES (?, ?)
準備步驟：首先使用者撰寫 statement template，跟一般的 SQL statement 很像，但查詢對應值用「?」替代（稱為 parameters、placeholders 或 bind variables），可視其為參數，就像以前會用 ($name, $price) 這樣。
The DBMS parses, compiles, and performs query optimization on the statement template, and stores the result without executing it.
DBMS 會對該 statement template 剖析、編譯並且處理 query 最佳化，然後將最佳化的結果存下來但不會去執行它。
Execute: At a later time, the application supplies (or binds) values for the parameters, and the DBMS executes the statement (possibly returning a result). The application may execute the statement as many times as it wants with different values. In this example, it might supply 'Bread' for the first parameter and '1.00' for the second parameter.
執行步驟：當使用者為這些參數「?」綁定值（例如第一個 ? 給值 'Bread'，第二個 ? 給值 '1.00'）後，DBMS 便會執行剛剛最佳化的 statement。
使用者可以在程式中多次用不同的值去執行該 statement。
這樣的流程可以看出，
為什麼當同個 script 需要執行多次時，效率會很好；
但另一方面，如果只需要執行一次，
效率當然會比不使用 prepared statement 技術差～～XD
但覺得光是可以避開 SQL injection 就值得使用它了。
大部分的 DBMS 都有支援 Prepared Statements，
例如： MySQL、Oracle、DB2、Microsoft SQL Server 和 PostgreSQL；
也有些程式語言會在標準函式庫中，以模擬的方式來支援 Prepared Statements，
這表示儘管它們所連結的資料庫並不支援 Prepared Statements，
這些程式語言還是能藉由模擬來達到相同的效果，
例如：Java's JDBC、Perl's DBI、PHP's PDO 和 Python's DB-API.
以 PHP 為例，PDO 的範例如下：
1: $stmt = $dbh->prepare("SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?");
2: $stmt->execute(array($username, $password));
Mysqli（MySQL Improved Extension）也有支援 Prepared Statements，
但那是使用 MySQL 本身的功能，並非在 client 端（指的是 PHP 端）處理。
Mysqli 範例：
1: /* Prepared statement, stage 1: prepare */
2: if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
3: echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
4: }
5:
6: /* Prepared statement, stage 2: bind and execute */
7: $id = 1;
8: if (!$stmt->bind_param("i", $id)) {
9: echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
10: }
11:
12: if (!$stmt->execute()) {
13: echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
14: }
其中 bind_param() 第一個參數代表「型別」：
i：integer
d：double
s：string
b：a blob and will be sent in packets
可以一次設定數個參數：
1: $stmt->bind_param('dsss', $source_id, $source_name, $source_gender, $source_location);
可以檢查型別更讓人放心～～XD
雖然上述 PDO 範例採取 array 的方式來 bind value，
但它其實也有類似 bind_param 的函式：bindParam()。
範例：
1: $calories = 150;
2: $colour = 'red';
3: $sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < ? AND colour = ?');
4: $sth->bindParam(1, $calories, PDO::PARAM_INT);
5: $sth->bindParam(2, $colour, PDO::PARAM_STR, 12);
6: $sth->execute();
bindParam() 的第一個參數，代表是要 bind 第幾個 placeholder 的 index（由 1 開始） ，
但腦筋動得快的人就會想到，
如果到時候 script 的條件要增加，或是 placeholder 的順序有調換，
那不就要重新編 index？也太麻煩。
所以還提供另一種 named placeholders 的方法 :param_name，
範例：
1: $calories = 150;
2: $colour = 'red';
3: $sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour');
4: $sth->bindParam(':calories', $calories, PDO::PARAM_INT);
5: $sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
6: $sth->execute();
這樣不管 placeholder 如何變動，bindParam() 部分都不需要跟著更改～～ ^_^
Named placeholders 的設定方式也可以用在 array 方式上：
1: $sql = 'SELECT * FROM some_table WHERE some_value > :value OR some_value < :value';
2: $stmt = $dbh->prepare($sql);
3: $stmt->execute( array( ':value' => 3 ) );
這一切努力除了是要避免 SQL injection 外，也是為了讓 SQL script 更簡潔，
避免以往為了串參數值，
或是為了避免 SQL injection 而出現一堆 mysql_real_escape_string() 導致的雜亂狀況，
讓 script 與 parameter 區隔開來，更顯結構化。
在查 bindParam() 資料時也看到 bindValue()，
連範例都很類似，
兩者的區別在於「bind variable」和「bind value」的不同，
前者是一個 reference 後者就純粹是個 value 而已。
一個簡單的 bindParam 範例：
1: $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
2: $stmt->bindParam(':name', $name);
3: $stmt->bindParam(':value', $value);
4:
5: // insert one row
6: $name = 'one';
7: $value = 1;
8: $stmt->execute();
9:
10: // insert another row with different values
11: $name = 'two';
12: $value = 2;
13: $stmt->execute();
可以看到 bindParam() 只要先 bind variable 後，
之後就可以一直指定這些 variables 的值，然後呼叫 execute() 來執行。
但是 bindValue() 事實上就只是給值而已，無 reference 的效果：
1: $calories = 150;
2: $colour = 'red';
3:
4: $sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour');
5: $sth->bindValue(':calories', $calories, PDO::PARAM_INT);
6: $sth->bindValue(':colour', $colour, PDO::PARAM_STR);
7: $sth->execute();
跟直接填值入該參數是相同的道理。
留言
張貼留言