《現代 PHP》學習筆記（十六）：資料庫

《現代 PHP》Study Notes
,

前言

本文為《現代 PHP》一書的學習筆記。

環境

  • Windows 10
  • Wnmp 3.1.0

資料庫

PHP 應用程式以各種資料庫來儲存資料，例如 MySQL、PostgreSQL、SQLite 等。MySQL 使用了 mysqli 擴充，添加了許多 mysqli_*() 函式到 PHP 中。

PDO 擴充

PDO（PHP Data Objects）是一組 PHP 類別，可以使用單一使用者介面和許多不同的 SQL 資料庫溝通，讓資料庫的實作方法被抽象化出來。

即使 PDO 提供單一介面給不同資料庫，仍然需要撰寫 SQL 陳述式。建議在使用 PDO 時撰寫 ANSI/ISO SQL，避免在切換資料庫系統時導致 SQL 無法使用。

資料庫連線和 DSN

在 PHP 中實例化 PDO 類別，利用 PDO 實體可以建立一個 PHP 和資料庫之間的連線。

PDO 類別建構式接受一個字串參數，稱作 DSN 或資料來源名稱，DSN 的連線字串大致上包含了以下：

  • 主機名稱（host）
  • 資料庫名稱（dbname）
  • 埠號碼（port）
  • 字元組（charset）

範例 5-18：PDO 建構式

1
2
3
4
5
6
7
8
9
10
try {
    $pdo = new PDO(
        'mysql:host=127.0.0.1;dbname=books;port=3306;charset=utf8',
        'USERNAME',
        'PASSWORD'
    );
} catch (PDOException $e) {
    echo "Database connection failed";
    exit;
}
  • DSN 以 mysql: 開頭，在「:」後指定 host、dbname、port 和 charset。

資料庫認證資訊

絕對不要在 PHP 檔案中寫入資料庫認證資訊，應該要把資料庫認證資訊移到根目錄之下的部署檔，在需要時引用到 PHP 檔案。

絕對不要版本控制資料庫認證資訊，應該要寫入 .gitignore 檔以忽略版本控制。

接下來的範例，其目錄結構如下：

1
2
3
|- settings.php
|- public_html/
  |- index.php

settings.php 檔如下

1
2
3
4
5
6
7
8
$settings = [
    'host' => '127.0.0.1',
    'port' => '3306',
    'name' => 'books',
    'username' => 'root',
    'password' => 'jlwaxhaw',
    'charset' => 'utf8'
];

範例 5-19：外部設定的 PDO 建構式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
require 'settings.php';

try {
    $pdo = new PDO(
        sprintf(
            'mysql:host=%s;dbname=%s;port=%s;charset=%s',
            $settings['host'],
            $settings['name'],
            $settings['port'],
            $settings['charset']
        ),
        $settings['username'],
        $settings['password']
    );
} catch (PDOException $e) {
    echo "Database connection failed";
    exit;
}

預備陳述式

初學的開發者可能會建立一個 SQL 陳述式如下：

1
2
3
4
$sql = sprintf(
    'SELECT id FROM users WHERE email = "%s"',
    filter_input(INPUT_GET, 'email')
);
  • 這提供了一個好方法讓駭客入侵資料庫，幸好 PDO 擴充引入了預備陳述式和範圍（bound）參數，讓消毒使用者輸入變得非常簡單。

以 PDO 實體的 prepare() 方法獲取預備陳述式的物件，此方法接受一個 SQL 陳述式字串作為第一個參數，並回傳一個 PDOStatement 實體。

1
2
$sql = 'SELECT id FROM users WHERE email = :email';
$statement = $pdo->prepare($sql);
  • :email 作為名稱占位符，可以安全地綁定任何値。

範例 5-20：帶有電子郵件位址的預備陳述式

1
2
3
4
5
$sql = 'SELECT id FROM users WHERE email = :email';
$email = filter_input(INPUT_GET, 'email');

$statement = $pdo->prepare($sql);
$statement->bindValue(':email', $email);
  • bindValue() 方法的第三個參數是資料型態，沒有的話會假設資料為字串型態。

範例 5-21：帶有 ID 的預備陳述式

1
2
3
4
5
$sql = 'SELECT email FROM users WHERE id = :id';
$userId = filter_input(INPUT_GET, 'id');

$statement = $pdo->prepare($sql);
$statement->bindValue(':id', $userId, PDO::PARAM_INT);
  • 使用 PDO::PARAM_INT 常數作為 bindValue() 方法的第三個參數。

其他的 PDO 常數還有：

  • PDO::PARAM_BOOL
  • PDO::PARAM_NULL
  • PDO::PARAM_INT
  • PDO::PARAM_STR

查詢結果

使用預備陳述式的 execute() 方法執行 SQL 陳述式，包括 INSERT、UPDATE 或 DELETE 陳述式。

使用 fetch()、fetchAll()、fetchColumn 和 fetchObject() 方法，可以擷取查詢結果。

範例 5-22：預備陳述式的結果作為關聯陣列

1
2
3
4
$statement->execute();
while (($result = $statement->fetch(PDO::FETCH_ASSOC)) !== false) {
    echo $result['email'], PHP_EOL;
}
  • fetch() 方法適合用在記憶體無法完全容納整個查詢結果的時候。

以下常數皆可作為 fetch() 方法的第一個參數：

  • PDO::FETCH_ASSOC 會回傳以資料庫欄位名稱作為索引鍵的陣列
  • PDO::FETCH_NUM 會回傳以數字作為索引鍵的陣列
  • PDO::FETCH_BOTH 是 PDO::FETCH_ASSOC 和 PDO::FETCH_NUM 的結合
  • PDO::FETCH_OBJ 會回傳物件，屬性為資料庫欄位名稱

如果處理較小的資料集，並且百分之百確定記憶體可以容納整個查詢結果，則可以使用 fetchAll() 方法擷取所有查詢結果。

範例 5-23：預備陳述式擷取整個結果

1
2
3
4
5
$statement->execute();
$allResults = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($allResults as $result) {
    echo $result['email'], PHP_EOL;
}

如果只要查詢結果中的一個欄位，可以使用預備陳述式的 fetchColumn() 方法，其唯一參數是目標欄位的索引値。

範例 5-24：預備陳述式擷取單一欄位單一列

1
2
3
4
$statement->execute();
while (($email = $statement->fetchColumn(1)) !== false) {
    echo $email, PHP_EOL;
}

可以使用預備陳述式的 fetchObject() 方法擷取查詢結果作為一個物件。

範例 5-25：預備陳述式擷取列作為物件

1
2
3
4
$statement->execute();
while (($result = $statement->fetchObject()) !== false) {
    echo $result->email, PHP_EOL;
}

交易

「交易」是一組自動執行的資料庫陳述式，同時執行成功或同時執行失敗的 SQL 查詢，可有效提升效能。

交易要使用 PDO 實體的 beginTransaction() 和 commit() 方法包覆 SQL 陳述式，如果交易中的任何查詢失敗，所有交易中的查詢都不會被執行。

範例 5-26：不使用交易的資料庫查詢

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
$stmtSubtract = $pdo->prepare('
    UPDATE accounts
    SET amount = amount - :amount
    WHERE name = :name
');
$stmtAdd = $pdo->prepare('
    UPDATE accounts
    SET amount = amount + :amount
    WHERE name = :name
');

// 提款
$fromAccount = 'Checking';
$withdrawal = 50;
$stmtSubtract->bindParam(':name', $fromAccount);
$stmtSubtract->bindParam(':amount', $withDrawal, PDO::PARAM_INT);
$stmtSubtract->execute();

// 存款
$toAccount = 'Savings';
$deposit = 50;
$stmtAdd->bindParam(':name', $toAccount);
$stmtAdd->bindParam(':amount', $deposit, PDO::PARAM_INT);
$stmtAdd->execute();
  • 如果從第一個帳戶提款後，發生斷電，這筆帳款將不會存入第二個帳戶。

以下範例將提款和存款包裝成單一的資料庫交易，確保兩個動作會同時執行成功，或是同時執行失敗。

範例 5-27：以交易使用資料庫查詢

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
$stmtSubtract = $pdo->prepare('
    UPDATE accounts
    SET amount = amount - :amount
    WHERE name = :name
');
$stmtAdd = $pdo->prepare('
    UPDATE accounts
    SET amount = amount + :amount
    WHERE name = :name
');

// 開始交易
$pdo->beginTransaction();

// 提款
$fromAccount = 'Checking';
$withdrawal = 50;
$stmtSubtract->bindParam(':name', $fromAccount);
$stmtSubtract->bindParam(':amount', $withDrawal, PDO::PARAM_INT);
$stmtSubtract->execute();

// 存款
$toAccount = 'Savings';
$deposit = 50;
$stmtAdd->bindParam(':name', $toAccount);
$stmtAdd->bindParam(':amount', $deposit, PDO::PARAM_INT);
$stmtAdd->execute();

// 承認交易
$pdo->commit();

參考資料

  • Josh Lockhart（2015）。現代 PHP。台北市：碁峯資訊。