2020年11月4日 星期三

PHP 學習筆記 : 使用 PDO 操作 MySQL 資料庫

最近在測試 Ajax 時需要用到後端 PHP, 因此花了點時間複習之前的筆記, 整理資料庫存取時在下面這本書的第 25 章看到使用 PDO (PHP Data Object) 操作 MySQL 資料庫的介紹, 我覺得比傳統方式簡單好用 : 

# 徹底研究 jQuery Mobile + PHP 手機程式及網站開發 (上奇, 張亞飛)

我用 phpinfo() 函數查詢所租的英國 Hostinger 主機發現也有支援 PDO (MySQL 與 SQlite), 所以就順便做一下測試與整理 :




使用 PDO 存取資料庫的程序如下 : 


1. 建立資料庫連線 : 

使用 new PDO($dsn, $username, $password) 建立一個 PDO 資料庫連線物件, 傳入參數有三 :
  • $dsn (資料來源字串) :
    包含 MySQL 資料庫主機位址 (host), 通訊埠 (port), 以及資料庫名稱 (dbname). 例如 :
    $dsn="mysql:host=mysql.hostinger.co.uk;port=3306;dbname=u137801000_test";  
    $dsn="mysql:host=localhost;port=3306;dbname=test"; (使用本機伺服器)
  • $username (資料庫使用者名稱) :
    即 MySQL 資料庫使用者名稱, 例如 :
    $username="u137801000_test";
    $username="root"; (使用本機伺服器)
  • $password (資料庫使用者密碼) :
    即 MySQL 資料庫使用者密碼, 例如 :
    $password="mysql";
例如我在 Hostinger 的主機用的是 :

//建立 PDO 連線物件
$dsn="mysql:host=mysql.hostinger.co.uk;port=3306;dbname=u137801000_test"; 
$username="u137801000_test"; 
$password="a123456"; 
$conn=new PDO($dsn, $username, $password);

在本機 XAMPP 用的是 :

//建立 PDO 連線物件
$dsn="mysql:host=localhost;port=3306;dbname=test"; 
$username="root"; 
$password="mysql"; 
$conn=new PDO($dsn, $username, $password);

因為呼叫 PDO() 發生錯誤時可能暴露資料庫連線資訊形成資安漏洞, 例如若將上面的資料庫名稱改為不存在的 test1 就會出現如下例外 (exception) : 

Fatal error: Uncaught PDOException: SQLSTATE[HY000] [1049] Unknown database 'test1' in D:\xampp\htdocs\php_pdo_test_1.php:7 Stack trace: #0 D:\xampp\htdocs\php_pdo_test_1.php(7): PDO->__construct('mysql:host=loca...', 'root', 'mysql') #1 {main} thrown in D:\xampp\htdocs\php_pdo_test_1.php on line 7

補強辦法是將 PDO() 指令放在 try-catch 中, 當發生連線錯誤捕捉到 PDOException 例外事件時呼叫 die() 函數結束程序, 避免直接暴露資料庫存取資訊 : 

try {$conn=new PDO($dsn, $username, $password);}
catch (PDOException $e) {
  echo "資料庫連線錯誤!";
  die();
  }


2. 設定資料庫編碼 : 

若資料庫內儲存資料含有中文字元, 則需以下列指令設定資料庫編碼為 utf-8 :

$conn->exec("SET CHARACTER SET utf8"); 


3. 執行資料庫操作 : 

資料庫主要有 CRUD (Create/Read/Update/Delete, 增刪查改) 等操作, 常用的 SQL 指令如下表 : 


 資料庫操作 SQL 指令範例
 建立 (Create) INSERT INTO users(name,age,gender) VALUES('Tom',25,'M')
 讀取 (Read) SELECT * FROM users WHERE gender='F' AND age < 40 ORDER BY name DESC 
 更新 (Update) UPDATE users SET age=23,gender='F' WHERE name='Amy'
 刪除 (Delete) DELETE FROM users WHERE name = "Tom"


另外有時還會用到建立資料表之操作, 指令例如 : 

$SQL="CREATE TABLE users(name varchar(50) NOT NULL, age int NOT NULL)"

更多 SQL 指令範例參考 :


呼叫 PDO 連線物件的 query() 方法並傳入 SQL 字串即可執行 SQL 指令, 此方法會傳回一個 PDOStatement 物件 (即受影響之資料集) :

$RS=$conn->query($SQL);

此資料集物件常用之方法有兩個 :
  • rowCount() : 傳回資料集的筆數 (SQL 指令操作影響之列數)
  • fetch() :  傳回資料集中最上面的一筆資料並將指標往下移
例如 : 

$row=$RS->fetch();   //傳回資料集中最上面的一筆資料
if ($RS->rowCount() > 0) {echo "更新完成";}

fetch() 方法傳回值為一個關聯式陣列, 可用陣列之鍵 (即資料表之欄位名稱) 來存取, 例如 :

echo $row["name"]; 


4. 關閉資料庫連線 : 

因連線物件會占用記憶體, 故執行完只要將 PDO 連線物件指定 NULL 值即可關閉連線. 

$conn=NULL;


以下測試使用最近為網友解題於 Hostinger 上所建的一個測試資料表為例, 其 SQL 指令為 : 

--
-- 資料表結構 `pid_valid_date`
--

CREATE TABLE `pid_valid_date` (
  `pid` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `valid_date` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
建好後輸入如下測試資料,  SQL 指令為 :

--
-- 傾印資料表的資料 `pid_valid_date`
--

INSERT INTO `pid_valid_date` (`pid`, `valid_date`) VALUES
('A123456789', '2021-02-13'),
('Q123456789', '2025-11-21'),
('S123456789', '2023-05-29'),
('T123456789', '2020-12-31');

參考 :

# 用 PHP 與 jQuery Ajax 存取資料庫 (習題)



<?php
header('Content-Type: text/html;charset=UTF-8');
//建立 PDO 連線物件
$dsn="mysql:host=localhost;port=3306;dbname=test"; 
$username="root"; 
$password="mysql"; 
try {$conn=new PDO($dsn, $username, $password);}
catch (PDOException $e) {
  echo "資料庫連線錯誤!";
  die();
  }
//設定資料編碼
$conn->exec("SET CHARACTER SET utf8"); 
//讀取資料表
$SQL="SELECT * FROM `pid_valid_date`"; 
$RS=$conn->query($SQL);
//讀取第一列資料
$row=$RS->fetch(); 
//傾印陣列內容
var_dump($row); 
if(!empty($row)) {
  $msg="pid=".$row["pid"]." valid_date=".$row["valid_date"]."<br>";
  } 
echo $msg;
//關閉資料庫連線
$conn=NULL;
?>

此例用 DPO 連線物件的 query() 方法從資料表 pid_valid_date 中擷取所有欄位資料後傳回資料集 (PDOStatement 物件), 呼叫其 fetch() 方法傳回資料集中的第一列資料 (關聯式陣列), 以欄位名稱即可取得每一欄之值, 原始的資料表傳回結果如下 :

array(4) { ["pid"]=> string(10) "A123456789" [0]=> string(10) "A123456789" ["valid_date"]=> string(10) "2021-02-13" [1]=> string(10) "2021-02-13" } pid=A123456789 valid_date=2021-02-13

資料集 PDOStatement 物件的 fetch() 方法只傳回資料集中的第一筆資料, 若要列出全部資料集需使用迴圈進行迭代, 例如 : 


測試 2 : 讀取資料庫中的資料表 (列出全部資料) [看原始碼]

<?php
header('Content-Type: text/html;charset=UTF-8');
//建立 PDO 連線物件
$dsn="mysql:host=localhost;port=3306;dbname=test"; 
$username="root"; 
$password="mysql";
try {$conn=new PDO($dsn, $username, $password);}
catch (PDOException $e) {
  echo "資料庫連線錯誤!";
  die();
  }
//設定資料編碼
$conn->exec("SET CHARACTER SET utf8"); 
//讀取資料表
$SQL="SELECT * FROM `pid_valid_date`"; 
$RS=$conn->query($SQL);
//讀取第一列資料
$row=$RS->fetch(); 
//迭代整個資料集
while(!empty($row)) {
  echo "pid=".$row["pid"]." valid_date=".$row["valid_date"]."<br>";
  $row=$RS->fetch();
  } 
//關閉資料庫連線
$conn=NULL;
?>

此例呼叫 fetch() 取得第一筆資料後用 while 迴圈檢查資料是否為空, 否則就印出該筆資料並取出下一筆資料直到資料集結束, 結果如下 : 

pid=A123456789 valid_date=2021-02-14
pid=Q123456789 valid_date=2025-11-21
pid=S123456789 valid_date=2023-05-29
pid=T123456789 valid_date=2020-12-31

下面是其它資料表操作例如新增, 更新, 刪除等全部整合在一起的範例 :


測試 3 : 在資料表中新增一筆資料 [看原始碼]

<!DOCTYPE html>
<html>
  <head>
    <title></title>
    <meta charset="utf-8">
    <meta http-equiv="cache-control" content="no-cache">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <script src="http://code.jquery.com/jquery-3.5.1.min.js"></script>
  </head>
  <body>
    <label for="pid">身分證字號(10個字元)<label>
    <input type="text" id="pid" name="pid" placeholder="A123456789"><br>
    <label for="valid_date">有效期限<label>
    <input type="date" id="valid_date" name="valid_date" placeholder="2020-11-03"><br><br>
    <button id="create">新增</button>
    <button id="read">讀取</button>
    <button id="update">修改</button>
    <button id="delete">刪除</button>
    <button id="read_all">讀取全部</button>
    <div id="display"></div>
    <script>
      $(document).ready(function(){ 
        $('#create').on('click', function() {
          var pid=$("#pid").val();
          var valid_date=$("#valid_date").val();
          if(pid.length!=10){alert("身分證字號為10個字元");return;}
          if(valid_date.length==0){alert("請選擇日期");return;}
          $.post({
            url: "http://tony1966.xyz/test/phptest/php_pdo_test_3_create.php",
            dataType: "html",
            data: {pid: pid, valid_date: valid_date},
            success: function(res) {
              alert(res);
              }
            });
          });
        $('#read').on('click', function() {
          var pid=$("#pid").val();
          if(pid.length!=10){alert("身分證字號為10個字元");return;}
          $.post({
            url: "http://tony1966.xyz/test/phptest/php_pdo_test_3_read.php",
            dataType: "html",
            data: {pid: pid},
            success: function(res) {
              $("#valid_date").val(res);
              $("#display").html(res);
              }
            });
          });
        $("#update").on("click", function(e){
          var pid=$("#pid").val();
          var valid_date=$("#valid_date").val();
          if(pid.length!=10){alert("身分證字號為10個字元");return;}
          if(valid_date.length==0){alert("請選擇日期");return;}
          $.post({
            url: "http://tony1966.xyz/test/phptest/php_pdo_test_3_update.php",
            dataType: "html",
            data: {pid: pid, valid_date: valid_date},
            success: function(res) {
              alert(res);
              }
            });
          });
        $("#delete").on("click", function(e){
          var pid=$("#pid").val();
          if(pid.length!=10){alert("身分證字號為10個字元");return;}
          $.post({
            url: "http://tony1966.xyz/test/phptest/php_pdo_test_3_delete.php",
            dataType: "html",
            data: {pid: pid},
            success: function(res) {
              alert(res);
              }
            });
          });
        $("#read_all").on("click", function(e){
          $.get({
            url: "http://tony1966.xyz/test/phptest/php_pdo_test_2.php",
            dataType: "html",
            success: function(res) {
              $("#display").html(res);
              }
            });
          });
        });
    </script>
  </body>
</html>

此例使用了 HTML5 的 input[type=date] 元件來輸入與顯示日期, 透過五個按鈕來進行 CRUD 操作, 關於 date 元件參考 :

# HTML5 的日期輸入功能教學

按 "讀取全部" 鈕會透過 Ajax 向上面範例的 php_pdo_test_2.php 提出請求, 傳回資料表 pid_valid_date 中的所有資料, 顯示於 id=display 的 div 元件中, 結果如下 : 



按 "讀取" 會先檢查有無輸入長度為 10 個字元的身分證字號, 通過後會透過 Ajax 向後端程式 php_pdo_test_3_read.php 提交參數 pid, 查詢結果為有效日期欄位 valid_date, 除了設定輸入欄位外也會顯示在 id=display 的 div 元素中.  

#php_pdo_test_3_read.php
<?php
header('Content-Type: text/html;charset=UTF-8');
//建立 PDO 連線物件
$dsn="mysql:host=mysql.hostinger.co.uk;port=3306;dbname=u137801000_test"; 
$username="u137801000_test"; 
$password="a123456"; 
try {$conn=new PDO($dsn, $username, $password);}
catch (PDOException $e) {
  echo "資料庫連線錯誤!";
  die();
  }
//設定資料編碼
$conn->exec("SET CHARACTER SET utf8"); 
$pid=$_REQUEST["pid"];
//讀取資料表
$SQL="SELECT * FROM `pid_valid_date` WHERE pid='".$pid."'"; 
$RS=$conn->query($SQL);
//讀取第一列資料 (傳回關聯式陣列)
$row=$RS->fetch();
echo $row["valid_date"];
//關閉資料庫連線
$conn=NULL;
?>

按 "更新" 按鈕會檢查輸入身分證字號與有效日期這兩個欄位是否有輸入合規之字串, 然後向後端程式 php_pdo_test_3_update.php 提交參數 pid 與 valid_date, 傳回的結果字串會用 alert() 顯示 :

#php_pdo_test_3_update.php 
<?php
header('Content-Type: text/html;charset=UTF-8');
//建立 PDO 連線物件
$dsn="mysql:host=mysql.hostinger.co.uk;port=3306;dbname=u137801000_test"; 
$username="u137801000_test"; 
$password="a123456"; 
try {$conn=new PDO($dsn, $username, $password);}
catch (PDOException $e) {
  echo "資料庫連線錯誤!";
  die();
  }
//設定資料編碼
$conn->exec("SET CHARACTER SET utf8"); 
$pid=$_REQUEST["pid"];
$valid_date=$_REQUEST["valid_date"];
//讀取資料表
$SQL="UPDATE `pid_valid_date` SET valid_date='".$valid_date."' ".
     "WHERE pid='".$pid."'";
$RS=$conn->query($SQL);
if($RS->rowCount() > 0) {echo "更新紀錄成功";}
else {echo "更新紀錄失敗";}
//關閉資料庫連線
$conn=NULL;
?>

按 "刪除" 按鈕會檢查輸入身分證字號欄位是否有輸入合規之字串, 然後向後端程式 php_pdo_test_3_delete.php 提交參數 pid, 傳回的結果字串會用 alert() 顯示 :

#php_pdo_test_3_delete.php
<?php
header('Content-Type: text/html;charset=UTF-8');
//建立 PDO 連線物件
$dsn="mysql:host=mysql.hostinger.co.uk;port=3306;dbname=u137801000_test"; 
$username="u137801000_test"; 
$password="a123456"; 
try {$conn=new PDO($dsn, $username, $password);}
catch (PDOException $e) {
  echo "資料庫連線錯誤!";
  die();
  }
//設定資料編碼
$conn->exec("SET CHARACTER SET utf8"); 
$pid=$_REQUEST["pid"];
$valid_date=$_REQUEST["valid_date"];
//讀取資料表
$SQL="DELETE FROM `pid_valid_date` WHERE pid='".$pid."'";
$RS=$conn->query($SQL);
if($RS->rowCount() > 0) {echo "刪除紀錄成功";}
else {echo "刪除紀錄失敗";}
//關閉資料庫連線
$conn=NULL;
?>

按 "新增" 按鈕會檢查輸入身分證字號與有效日期這兩個欄位是否有輸入合規之字串, 然後向後端程式 php_pdo_test_3_create.php 提交參數 pid 與 valid_date, 傳回的結果字串會用 alert() 顯示 :

#php_pdo_test_3_create.php
<?php
header('Content-Type: text/html;charset=UTF-8');
//建立 PDO 連線物件
$dsn="mysql:host=localhost;port=3306;dbname=test"; 
$username="root"; 
$password="mysql"; 
try {$conn=new PDO($dsn, $username, $password);}
catch (PDOException $e) {
  echo "資料庫連線錯誤!";
  die();
  }
//設定資料編碼
$conn->exec("SET CHARACTER SET utf8"); 
$pid=$_REQUEST["pid"];
$valid_date=$_REQUEST["valid_date"];
//讀取資料表
$SQL="INSERT INTO `pid_valid_date`(pid, valid_date) VALUES('".
     $pid."','".$valid_date."')"; 
$RS=$conn->query($SQL);
if($RS->rowCount() > 0) {echo "新增紀錄成功";}
else {echo "新增紀錄失敗";}
//關閉資料庫連線
$conn=NULL;
?>

使用 PDO 物件存取 MySQL 資料庫感覺比傳統的函數式操作要簡單. 

張貼者： 於
標籤： ,

沒有留言 :

張貼留言

訂閱： 張貼留言 ( Atom )