關於 PDO+PDOStatement

• PDO (資料庫主要操作)
• PDOStatement (敘述整合)

使用 PDO 的方式開啟資料庫

config.php

// For PDO MySQL

define('DB_SOURCE', 'mysql:host='.DB_SERVERIP.';dbname='.DB_DATABASE);

function db_open()

{

try {

$pdo = new PDO(DB_SOURCE, DB_USERNAME, DB_PASSWORD); if(defined('SET_CHARACTER')) $pdo->query(SET_CHARACTER);

} catch (PDOException $e) { die("Error!: " . $e->getMessage()); } return $pdo;

}

對 SQL 指令和『資料』的綁定

 add_save.php

// 方法一：使用綁定參數

$sqlstr = "INSERT INTO person(usercode, username, address, birthday, height, weight, remark) VALUES (:usercode, :username, :address, :birthday, :height, :weight, :remark)";

$sth = $pdo->prepare($sqlstr);

$sth->bindParam(':usercode', $usercode, PDO::PARAM_STR);

$sth->bindParam(':username', $username, PDO::PARAM_STR); $sth->bindParam(':address' , $address , PDO::PARAM_STR);

$sth->bindParam(':birthday', $birthday, PDO::PARAM_STR);

$sth->bindParam(':height' , $height , PDO::PARAM_INT);

$sth->bindParam(':weight' , $weight , PDO::PARAM_INT);

$sth->bindParam(':remark' , $remark , PDO::PARAM_STR);

對 SQL 指令和『資料』的綁定

 add_save.php

// 方法二：使用數值，依順序對應

$sqlstr = "INSERT INTO person(usercode, username, address, birthday, height, weight, remark) VALUES (?, ?, ?, ?, ?, ?, ?)";

$sth = $pdo->prepare($sqlstr);

$sth->bindValue(1, $usercode, PDO::PARAM_STR);

$sth->bindValue(2, $username, PDO::PARAM_STR);

$sth->bindValue(3, $address , PDO::PARAM_STR);

$sth->bindValue(4, $birthday, PDO::PARAM_STR);

$sth->bindValue(5, $height , PDO::PARAM_INT);

$sth->bindValue(6, $weight , PDO::PARAM_INT);

$sth->bindValue(7, $remark , PDO::PARAM_STR);

PDO+PDOStatement 程式的範例

display.php

<?php

include 'config.php';

include 'utility.php';

$uid = isset($_GET['uid']) ? $_GET['uid'] : 0;

// 連接資料庫

$pdo = db_open();

// 寫出 SQL 語法

$sqlstr = "SELECT * FROM person WHERE uid=? ";

$sth = $pdo->prepare($sqlstr);

$sth->bindValue(1, $uid, PDO::PARAM_INT);

PDO+PDOStatement 程式的範例

display.php

// 執行SQL及處理結果

if($sth->execute())

{ // 成功執行 query 指令

if($row = $sth->fetch(PDO::FETCH_ASSOC))

{ $uid = $row['uid'];

$usercode = convert_to_html($row['usercode']);

$username = convert_to_html($row['username']);

$address = convert_to_html($row['address']);

$birthday = convert_to_html($row['birthday']);

$height = convert_to_html($row['height']);

$weight = convert_to_html($row['weight']);

$remark = convert_to_html($row['remark']);

提取資料 fetch() 用法

PDO+PDOStatement 程式的範例

display.php

$data = <<< HEREDOC
       <table>
         <tr><th>代碼</th><td>{$usercode}</td></tr>
         <tr><th>姓名</th><td>{$username}</td></tr>
         <tr><th>地址</th><td>{$address}</td></tr>
         <tr><th>生日</th><td>{$birthday}</td></tr>
         <tr><th>身高</th><td>{$height}</td></tr>
         <tr><th>體重</th><td>{$weight}</td></tr>
         <tr><th>備註</th><td>{$remark}</td></tr>
       </table>
HEREDOC;

PDO+PDOStatement 程式的範例

display.php

 }

   else

   {

     $data = '查不到相關記錄！';

   }

}

else

{

   // 無法執行 query 指令時

   $data = error_message('display');

}

PDO+PDOStatement 程式的範例

display.php

$html = <<< HEREDOC
<button onclick="location.href='list_page.php';">返回列表</button>
<h2>顯示資料</h2>
{$data}
HEREDOC;
 
include 'pagemake.php';
pagemake($html, '');
?>

SQL 程式與資料分離