PHP PDO

1.
簡介
簡述:PHP 資料物件 (PDO) 擴展為PHP存取資料庫定義了一個輕量級的一致接口
特色:PDO 提供了一個 資料存取 抽象層,不管使用哪種資料庫,都可以用相同的函數(方法)來查詢和存取資料
2.
連結與管理
連接到 MySQL
1
$dbh new PDO('mysql:host=localhost;dbname=test'$user$pass);
處理連結錯誤
1
2
3
4
5
6
7
8
9
10
try {
    $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
    foreach($dbh->query('SELECT * from FOO') as $row) {
        print_r($row);
    }
    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
關閉一個連結
1
$dbh = null;
持續連線
優點:無需每次建立一個新的連結,讓web application 更快
1
2
3
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass, array(
    PDO::ATTR_PERSISTENT => true
));
3.
交易與自動提交
四大特性
原子性(Atomicity)
一致性(Consistency)
隔離性(Isolation)
持久性(Durability)
假如資料庫不支援Transactions可運行的程式碼
啟動
1
PDO::beginTransaction()
提交
1
PDO::commit()
倒回
1
PDO::rollBack()
一個交易處理例子
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
try {
  $dbh = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2',
      array(PDO::ATTR_PERSISTENT => true));
  echo "Connected\n";
} catch (Exception $e) {
  die("Unable to connect: " . $e->getMessage());
}
try
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $dbh->beginTransaction();
  $dbh->exec("insert into staff (id, first, last) values (23, 'Joe', 'Bloggs')");
  $dbh->exec("insert into salarychange (id, amount, changedate)
      values (23, 50000, NOW())");
  $dbh->commit();
  
} catch (Exception $e) {
  $dbh->rollBack();
  echo "Failed: " . $e->getMessage();
}
4.
預先語句與儲存過程
預先語句的插入
1
2
3
4
5
6
7
8
9
10
11
12
13
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);
// 插入一行
$name = 'one';
$value = 1;
$stmt->execute();
//  用不同的值插入另一行
$name = 'two';
$value = 2;
$stmt->execute();
預防 SQL 注入攻擊
1
2
3
4
5
6
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
if ($stmt->execute(array($_GET['name']))) {
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
}
設定 限制 輸入與輸出 型態
1
2
3
4
5
6
7
8
$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
// 调用存储过程
$stmt->execute();
print "procedure returned $value\n";
正確的LIKE使用
1
2
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->execute(array("%$_GET[name]%"));
5.
錯誤與處理
PDO::ERRMODE_SILENT
1
PDO::ERRMODE_SILENT

簡單設置錯誤碼,可使用

1
2
PDO::errorCode()
PDO::errorInfo()
檢查語句與資料庫物件
PDO::ERRMODE_WARNING
主要功能:發出E_WARNING資訊,不中斷應用程式
使用時機:測試期
PDO::ERRMODE_EXCEPTION
主要功能:設置錯誤碼、丟出PDOException異常類別,設置他的屬性來反映錯誤程式碼與錯誤訊息
一個簡單的pdo錯誤處理
1
2
3
4
5
6
7
8
9
10
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';
try {
    $dbh = new PDO($dsn, $user, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
6.
Large Objects
主要用於大量資料,通常是4kb以上,可使用PDO::PARAM_LOB處理
使用PDO::PARAM_LOB顯示一張照片
1
2
3
4
5
6
7
8
9
$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->prepare("select contenttype, imagedata from images where id=?");
$stmt->execute(array($_GET['id']));
$stmt->bindColumn(1, $type, PDO::PARAM_STR, 256);
$stmt->bindColumn(2, $lob, PDO::PARAM_LOB);
$stmt->fetch(PDO::FETCH_BOUND);
header("Content-Type: $type");
fpassthru($lob);
使用PDO::PARAM_LOB插入一張照片
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->prepare("insert into images (id, contenttype, imagedata) values (?, ?, ?)");
$id = get_new_id(); // 调用某个函数来分配一个新 ID
// 假设处理一个文件上传
// 可以在 PHP 文档中找到更多的信息
$fp = fopen($_FILES['file']['tmp_name'], 'rb');
$stmt->bindParam(1, $id);
$stmt->bindParam(2, $_FILES['file']['type']);
$stmt->bindParam(3, $fp, PDO::PARAM_LOB);
$db->beginTransaction();
$stmt->execute();
$db->commit();
7.

PDO

PDO::beginTransaction

一個transactions簡單的例子
1
2
3
4
5
6
7
8
9
10
11
12
/* 開始一個Transaction,關閉自動提交 */
$dbh->beginTransaction();
/* 更改資料庫架構及資料 */
$sth = $dbh->exec("DROP TABLE fruit");
$sth = $dbh->exec("UPDATE dessert
    SET name = 'hamburger'");
/* 識別出錯誤並倒回更改 */
$dbh->rollBack();
/* 資料庫連接現在返回到自動提交模式 */
PDO::commit
一個簡單的範例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/* 開始一個Transaction,關閉自動提交 */
$dbh->beginTransaction();
/* 在全有或全無的基礎上插入多行記錄(全部插入,全部不插入) */
$sql = 'INSERT INTO fruit
    (name, colour, calories)
    VALUES (?, ?, ?)';
$sth = $dbh->prepare($sql);
foreach ($fruits as $fruit) {
    $sth->execute(array(
        $fruit->name,
        $fruit->colour,
        $fruit->calories,
    ));
}
/* 提交更改 */
$dbh->commit();
/* 現在資料庫連接返回到自動提交模式 */

PDO::__construct

建立一個資料庫連結的PDO實例
一個簡單的例子
1
2
3
4
5
6
7
8
9
10
/* Connect to an ODBC database using driver invocation */
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';
try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
PDO::errorCode
一個簡單的範例
1
2
3
4
5
/* 引發一个錯誤 -- BONES 資料表不存在 */
$dbh->exec("INSERT INTO bones(skull) VALUES ('lucy')");
echo "\nPDO::errorCode(): ";
print $dbh->errorCode();// 輸出PDO::errorCode(): 42S02
PDO::errorInfo
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/* Provoke an error -- bogus SQL syntax */
$stmt = $dbh->prepare('bogus sql');
if (!$stmt) {
    echo "\nPDO::errorInfo():\n";
    print_r($dbh->errorInfo());
}
/*
PDO::errorInfo():
Array
(
    [0] => HY000
    [1] => 1
    [2] => near "bogus": syntax error
)
*/
PDO::getAttribute
取回一個資料庫連結的屬性
  1. PDO::ATTR_AUTOCOMMIT
  2. PDO::ATTR_CASE
  3. PDO::ATTR_CLIENT_VERSION
  4. PDO::ATTR_CONNECTION_STATUS
  5. PDO::ATTR_DRIVER_NAME
  6. PDO::ATTR_ERRMODE
  7. PDO::ATTR_ORACLE_NULLS
  8. PDO::ATTR_PERSISTENT
  9. PDO::ATTR_PREFETCH
  10. PDO::ATTR_SERVER_INFO
  11. PDO::ATTR_SERVER_VERSION
  12. PDO::ATTR_TIMEOUT
一個簡單的範例
1
2
3
4
5
6
7
8
9
10
11
$conn = new PDO('odbc:sample', 'db2inst1', 'ibmdb2');
$attributes = array(
    "AUTOCOMMIT", "ERRMODE", "CASE", "CLIENT_VERSION", "CONNECTION_STATUS",
    "ORACLE_NULLS", "PERSISTENT", "PREFETCH", "SERVER_INFO", "SERVER_VERSION",
    "TIMEOUT"
);
foreach ($attributes as $val) {
    echo "PDO::ATTR_$val: ";
    echo $conn->getAttribute(constant("PDO::ATTR_$val")) . "\n";
}
PDO::getAvailableDrivers
回傳一個可使用的驅動
一個簡單的範例
1
print_r(PDO::getAvailableDrivers());
PDO::inTransaction
檢查是否在一個Transactions內
PDO::lastInsertId
回傳最後插入的id或序列值
PDO::prepare
預先準備語句 回傳一個語句的物件
一個簡單的範例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
$sql = 'SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':calories' => 150, ':colour' => 'red'));
$red = $sth->fetchAll();
$sth->execute(array(':calories' => 175, ':colour' => 'yellow'));
$yellow = $sth->fetchAll();
// 執行重複語句
/* Execute a prepared statement by passing an array of values */
$sth $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');
$sth->execute(array(150, 'red'));
$red $sth->fetchAll();
$sth->execute(array(175, 'yellow'));
$yellow $sth->fetchAll();

PDO::query

執行SQL語句並回傳結果
一個簡單的範例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
function getFruit($conn) {
    $sql = 'SELECT name, color, calories FROM fruit ORDER BY name';
    foreach ($conn->query($sql) as $row) {
        print $row['name'] . "\t";
        print $row['color'] . "\t";
        print $row['calories'] . "\n";
    }
/*
輸出
apple   red     150
banana  yellow  250
kiwi    brown   75
lemon   yellow  25
orange  orange  300
pear    green   150
watermelon      pink    90
/*
}
8.
PDOStatement
PDOStatement::bindColumn
使用方法:PDOStatement::bindColumn ( mixed $column , mixed &$param [, int $type [, int $maxlen [, mixed $driverdata ]]] )
參數說明:
  • column:欄位序號(從1開始索引)或欄位名稱。如果使用欄位名稱,注意名稱應該與由驅動回傳的列名大小寫保持一致。
  • param:將綁定到列的 PHP 變數名稱
  • type:通過 PDO::PARAM_* 常數指定的參數的資料類型。
  • maxlen:預分配提示。
  • driverdata:驅動的可選參數。
一個簡單的範例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
function readData($dbh) {
  $sql 'SELECT name, colour, calories FROM fruit';
  try {
    $stmt $dbh->prepare($sql);
    $stmt->execute();
    /* Bind by column number */
    $stmt->bindColumn(1, $name);
    $stmt->bindColumn(2, $colour);
    
    /* Bind by column name */
    $stmt->bindColumn('calories'$cals);
    while ($row $stmt->fetch(PDO::FETCH_BOUND)) {
      $data $name "\t" $colour "\t" $cals "\n";
      print $data;
    }
  }
  catch (PDOException $e) {
    print $e->getMessage();
  }
}
readData($dbh);
PDOStatement::bindParam
绑定一个參數到指定的變數名稱
使用方法:
bool PDOStatement::bindParam ( mixed $parameter , mixed &$variable [, int $data_type = PDO::PARAM_STR [, int $length [, mixed $driver_options ]]] )
參數說明:
  • parameter:參數標識符號。對於使用命名佔位符的預處理語句,應是類似 :name 形式的參數名。對於使用問號佔位符的預處理語句,應是以1開始索引的參數位置。
  • variable:綁定到 SQL 語句參數的 PHP 變量名。
  • data_type:使用 PDO::PARAM_* 常數明確地指定參數的類型。
  • length:資料類型的長度。為表明參數是一個存儲過程的 OUT 參數,必須明確地設置此長度。
  • driver_options
一個簡單的範例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
// 對應的
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour 'red';
$sth $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');
$sth->bindParam(1, $calories, PDO::PARAM_INT);
$sth->bindParam(2, $colour, PDO::PARAM_STR, 12);
$sth->execute();
PDOStatement::bindValue
參數說明:
  • parameter:參數標識符。對於使用命名佔位符的預處理語句,應是類似 :name 形式的參數名。對於使用問號佔位符的預處理語句,應是以1開始索引的參數位置。
  • value:綁定到參數的值
  • data_type:使用 PDO::PARAM_* 常數明確地指定參數的類型。
差異 bindParamm 與 bindValue
bindParam會將一個PHP變數與SQL綁定,當變數變化時,SQL也會變動,bindValue則不會,只會執行最初綁定的值
PDOStatement::columnCount
回傳執行結果用了幾個欄位
一個簡單的範例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$dbh = new PDO('odbc:sample', 'db2inst1', 'ibmdb2');
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
/* Count the number of columns in the (non-existent) result set */
$colcount = $sth->columnCount();
print("Before execute(), result set has $colcount columns (should be 0)\n");
$sth->execute();
/* Count the number of columns in the result set */
$colcount = $sth->columnCount();
print("After execute(), result set has $colcount columns (should be 2)\n");
/*
Before execute(), result set has 0 columns (should be 0)
After execute(), result set has 2 columns (should be 2)
*/
PDOStatement::debugDumpParams
一個簡單的範例
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
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
$sth->debugDumpParams();
/*
SQL: [96] SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour
Params:  2
Key: Name: [9] :calories
paramno=-1
name=[9] ":calories"
is_param=1
param_type=1
Key: Name: [7] :colour
paramno=-1
name=[7] ":colour"
is_param=1
param_type=2
*/
PDOStatement::errorCode
一個簡單的範例
1
2
3
4
5
6
/* Provoke an error -- the BONES table does not exist */
$err = $dbh->prepare('SELECT skull FROM bones');
$err->execute();
echo "\nPDOStatement::errorCode(): ";
print $err->errorCode(); // PDOStatement::errorCode(): 42S02
PDOStatement::errorInfo
回傳值
0    SQLSTATE error code (a five characters alphanumeric identifier defined in the ANSI SQL standard).
1    Driver specific error code.
2    Driver specific error message.
一個簡單的範例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/* Provoke an error -- the BONES table does not exist */
$sth = $dbh->prepare('SELECT skull FROM bones');
$sth->execute();
echo "\nPDOStatement::errorInfo():\n";
$arr = $sth->errorInfo();
print_r($arr);
/*
PDOStatement::errorInfo():
Array
(
    [0] => 42S02
    [1] => -204
    [2] => [IBM][CLI Driver][DB2/LINUX] SQL0204N  "DANIELS.BONES" is an undefined name.  SQLSTATE=42704
)
*/
PDOStatement::execute
執行一個預備語句
一個簡單的範例
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
/* Execute a prepared statement by binding a variable and value */
$calories = 150;
$colour = 'gre';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour LIKE :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', "%{$colour}%");
$sth->execute();
// 以陣列執行
$calories = 150;
$colour 'red';
$sth $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->execute(array(':calories' => $calories':colour' => $colour));
//使用?執行
/* Execute a prepared statement by passing an array of insert values */
$calories = 150;
$colour 'red';
$sth $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');
$sth->execute(array($calories$colour));
使用in語句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/* Execute a prepared statement using an array of values for an IN clause */
$params = array(1, 21, 63, 171);
/* Create a string for the parameter placeholders filled to the number of params */
$place_holders = implode(',', array_fill(0, count($params), '?'));
/*
    This prepares the statement with enough unnamed placeholders for every value
    in our $params array. The values of the $params array are then bound to the
    placeholders in the prepared statement when the statement is executed.
    This is not the same thing as using PDOStatement::bindParam() since this
    requires a reference to the variable. PDOStatement::execute() only binds
    by value instead.
*/
$sth = $dbh->prepare("SELECT id, name FROM contacts WHERE id IN ($place_holders)");
$sth->execute($params);
PDOStatement::fetch
模式
  1. PDO::FETCH_ASSOC
  2. PDO::FETCH_BOTH
  3. PDO::FETCH_BOUND
  4. PDO::FETCH_CLASS
  5. PDO::FETCH_INTO
  6. PDO::FETCH_LAZY
  7. PDO::FETCH_NUM
  8. PDO::FETCH_OBJ
一個簡單的比較
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();
/* 使用 PDOStatement::fetch 風格 */
print("PDO::FETCH_ASSOC: ");
print("Return next row as an array indexed by column name\n");
$result = $sth->fetch(PDO::FETCH_ASSOC);
print_r($result);
print("\n");
print("PDO::FETCH_BOTH: ");
print("Return next row as an array indexed by both column name and number\n");
$result = $sth->fetch(PDO::FETCH_BOTH);
print_r($result);
print("\n");
print("PDO::FETCH_LAZY: ");
print("Return next row as an anonymous object with column names as properties\n");
$result = $sth->fetch(PDO::FETCH_LAZY);
print_r($result);
print("\n");
print("PDO::FETCH_OBJ: ");
print("Return next row as an anonymous object with column names as properties\n");
$result = $sth->fetch(PDO::FETCH_OBJ);
print $result->NAME;
print("\n");
/*
PDO::FETCH_ASSOC: Return next row as an array indexed by column name
Array
(
    [name] => apple
    [colour] => red
)
PDO::FETCH_BOTH: Return next row as an array indexed by both column name and number
Array
(
    [name] => banana
    [0] => banana
    [colour] => yellow
    [1] => yellow
)
PDO::FETCH_LAZY: Return next row as an anonymous object with column names as properties
PDORow Object
(
    [name] => orange
    [colour] => orange
)
PDO::FETCH_OBJ: Return next row as an anonymous object with column names as properties
kiwi
*/
PDOStatement::fetchAll
取得所有的資料
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
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();
/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();
print_r($result);
/*
Array
(
    [0] => Array
        (
            [name] => pear
            [0] => pear
            [colour] => green
            [1] => green
        )
    [1] => Array
        (
            [name] => watermelon
            [0] => watermelon
            [colour] => pink
            [1] => pink
        )
)
*/
fetch 與 fetchAll 差異
抓取單筆資料用fetch 多筆使用fetchAll
PDOStatement::fetchColumn
取得欄位名稱
一個簡單的範例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();
print("Fetch the first column from the first row in the result set:\n");
$result = $sth->fetchColumn();
print("name = $result\n");
print("Fetch the second column from the second row in the result set:\n");
$result = $sth->fetchColumn(1);
print("colour = $result\n");
/*
Fetch the first column from the first row in the result set:
name = lemon
Fetch the second column from the second row in the result set:
colour = red
*/
PDOStatement::nextRowset
advances to the next rowset in a multi-rowset statement handle
一個簡單的用途
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
31
32
33
34
35
$sql = 'CALL multiple_rowsets()';
$stmt = $conn->query($sql);
$i = 1;
do {
    $rowset = $stmt->fetchAll(PDO::FETCH_NUM);
    if ($rowset) {
        printResultSet($rowset, $i);
    }
    $i++;
} while ($stmt->nextRowset());
function printResultSet(&$rowset, $i) {
    print "Result set $i:\n";
    foreach ($rowset as $row) {
        foreach ($row as $col) {
            print $col . "\t";
        }
        print "\n";
    }
    print "\n";
}
/*
Result set 1:
apple    red
banana   yellow
Result set 2:
orange   orange    150
banana   yellow    175
Result set 3:
lime     green
apple    red
banana   yellow
*/
PDOStatement::rowCount
回傳受到影響的資料有幾筆
一個刪除影響幾筆資料的簡單範例
1
2
3
4
5
6
7
8
9
10
11
12
/* Delete all rows from the FRUIT table */
$del = $dbh->prepare('DELETE FROM fruit');
$del->execute();
/* Return number of rows that were deleted */
print("Return number of rows that were deleted:\n");
$count = $del->rowCount();
print("Deleted $count rows.\n");
/*
Return number of rows that were deleted:
Deleted 9 rows.
*/
也可以藉由query 來顯示每筆受影響的資料
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
$sql = "SELECT COUNT(*) FROM fruit WHERE calories > 100";
if ($res = $conn->query($sql)) {
    /* Check the number of rows that match the SELECT statement */
  if ($res->fetchColumn() > 0) {
        /* Issue the real SELECT statement and work with the results */
         $sql = "SELECT name FROM fruit WHERE calories > 100";
       foreach ($conn->query($sql) as $row) {
           print "Name: " $row['NAME'] . "\n";
         }
    }
    /* No rows matched -- do something else */
  else {
      print "No rows matched the query.";
    }
}
$res = null;
$conn = null;
/*
apple
banana
orange
pear
*/
PDOStatement::setFetchMode
設定抓取資料的預設模式
一個設為PDO::FETCH_NUM模式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$sql = 'SELECT name, colour, calories FROM fruit';
try {
  $stmt = $dbh->query($sql);
  $result = $stmt->setFetchMode(PDO::FETCH_NUM);
  while ($row = $stmt->fetch()) {
    print $row[0] . "\t" . $row[1] . "\t" . $row[2] . "\n";
  }
}
catch (PDOException $e) {
  print $e->getMessage();
}
/*
apple   red     150
banana  yellow  250
orange  orange  300
kiwi    brown   75
lemon   yellow  25
pear    green   150
watermelon      pink    90
*/
9.
PDOException
顯示pdo產生的錯誤
類別摘要
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
PDOException extends RuntimeException {
/* 屬性 */
public array $errorInfo ;
protected string $message ;
protected string $code ;
/* 繼承的方法 */
final public string Exception::getMessage ( void )
final public Exception Exception::getPrevious ( void )
final public int Exception::getCode ( void )
final public string Exception::getFile ( void )
final public int Exception::getLine ( void )
final public array Exception::getTrace ( void )
final public string Exception::getTraceAsString ( void )
public string Exception::__toString ( void )
final private void Exception::__clone ( void )
}
屬性
  • errorInfo:相當於PDO::errorInfo() 或 PDOStatement::errorInfo()
  • message:文本錯誤訊息。用 Exception::getMessage() 來存取。
  • code:SQLSTATE 錯誤碼。用Exception::getCode() 來存取。

發表迴響