第 10 章 PHP 存取 MySQL 資料庫

本章重點 10 - 1 PHP 與 MySQL 10 - 2 連線 MySQL 伺服器 10 - 3 查詢資料 10 - 4 新增、更新、刪除資料 10 - 5 網頁資料庫程式的安全隱憂 － SQL Injection 10 - 6 實戰演練 － 短網址網站 10 - 7 實戰演練 － 簡易留言板

10 - 1 PHP 與 MySQL PHP 提供了許多 MySQL 相關函式, 可以讓我們存取 MySQL 資料庫。使用這些 MySQL 函式存取資料時, 必須透過以下的流程：

PHP 與 MySQL

PHP 與 MySQL (1) 與資料庫伺服器建立連線：使用 mysql_connect() 函式連線資料庫伺服器。 (2) 選擇資料庫：執行 mysql_select_db() 函式選擇要使用的資料庫。 (3) 從資料庫查詢 / 修改 / 新增 / 刪除資料：使用 mysql_query() 函式執行 SQL 敘述。 (4) 取回查詢的資料：如果在上一步驟是『查詢』動作, 則函式會傳回指向查詢結果的資源 (resource) 代碼。 本例假設查詢結果為『資源代碼 1』。

PHP 與 MySQL 上面是我們使用 MySQL 函式存取資料庫的大致流程, 這些函式會在隨後章節中說明。 (5) 讀取記錄：使用 mysql_fetch_array() 或其他函式向『資源代碼 1』取得一筆記錄。若有多筆記錄, 則必須使用迴圈逐一讀取。 上面是我們使用 MySQL 函式存取資料庫的大致流程, 這些函式會在隨後章節中說明。 除了以上的傳統流程外, PHP 的 PEAR 函式庫也提供了資料庫存取類別, 可以直接繼承套用。 本章只介紹 PHP 傳統的 MySQL 函式。

10 - 2 連線 MySQL 伺服器 連線伺服器 無法連線伺服器的情況 設定連線所使用的字元集與校對 選擇資料庫 建立資料庫引用檔

連線伺服器 在 PHP 要存取資料庫之前, 要先使用 mysql_connect() 函式來連線資料庫伺服器。

連線伺服器 所以使用 mysql_connect() 連線伺服器時, 除了要指定伺服器位址外, 也需指定要登入的帳號名稱與密碼。

連線伺服器 如果成功登入伺服器, mysql_connect() 會回傳一個連線代碼, 若失敗的話, 則會傳回 FALSE。 通常連線代碼只會用於同時連線多個資料庫的狀況, 而一般我們只會在同一資料庫下的不同資料表存取資料, 不太需要使用連線代碼, 所以本書不多作說明。

無法連線伺服器的情況 如果帳號密碼無法通過伺服器的驗證, 網頁便會顯示以下訊息： 此時您必須檢查 mysql_connect() 函式中的帳號密碼是否正確, 才能讓 PHP 程式登入 MySQL 伺服器。

無法連線伺服器的情況 除了帳號密碼錯誤外, 伺服器如果當機, 也會造成無法連線的錯誤。 為了避免無法連線資料庫時, PHP 仍繼續執行後續的資料庫存取步驟, 我們可以判斷 mysql_connect() 的回傳值, 如果回傳 FALSE, 則顯示錯誤訊息並且立刻停止程式的執行：

無法連線伺服器的情況 執行結果： 程式使用 if 判斷 mysql_connect() 是否連線成功, 若不成功, 則使用 die() 來中斷程式的執行, 並且顯示錯誤訊息。 此外, 因為 MySQL 函式本身的錯誤訊息會包含帳號名稱及伺服器位址, 為了避免這些資訊外流, 可以在 mysql_connect() 前面加上 @ 符號, 抑制其錯誤訊息。

設定連線所使用的字元集與校對 前面 8 - 3 節介紹了 MySQL 資料庫的字元集與校對, 其中提到在連線層級也必須指定適當的字元集, 才能取得正確編碼的資料。 例如資料庫採用 "utf8" 字元集, 但是連線時使用 "big5" 字元集, 如此連線後讀取或寫入的資料都會變成亂碼。 因為本書統一採用 UTF8 編碼, 所以成功連線伺服器之後, 請務必使用下面程式碼, 指定該連線要使用 "utf8" 字元集：

設定連線所使用的字元集與校對 mysql_query() 函式可以在連線資料庫後執行 SQL 敘述, 而 "SET NAMES utf8" 則是用來將連線設定為 utf8 編碼的 SQL 敘述。 關於 mysql_query() 的使用方法, 會在 10 - 2 節詳細說明。

選擇資料庫 連線並登入伺服器之後, 便可以使用 mysql_select_db() 函式來選擇要存取的資料庫。 例如筆者登入後想要存取 Ch10 資料庫, 便必須使用以下程式碼：

選擇資料庫 如果成功選擇了資料庫, mysql_select_db() 將回傳 TRUE, 否則便會回傳 FALSE。 如果擔心資料庫無法使用時程式會發生錯誤, 可以參考前面建立連線的例子, 使用 if 判斷選擇的動作是否成功。

建立資料庫引用檔 前面連線伺服器、設定連線字元集, 然後選擇資料庫, 可說是每次存取資料庫時的必要步驟。 通常一個動態網站可能具備註冊帳號、新增資料...等多種不同的資料庫功能, 一般都會將這些功能各自獨立為多個程式檔案。 如果在每個程式內都要執行一次這些步驟, 實在是一件很繁瑣的事情。

建立資料庫引用檔 而且如果有一天需要更改帳號、密碼或資料庫名稱, 一個一個修改檔案更會成為程式設計人員的惡夢。 為了簡化步驟, 並且避免名稱更改可能造成的問題, 我們可以將上面步驟寫成一個獨立的 PHP 引用檔, 然後在其他 PHP 檔案中, 使用 include() 等函式引用其程式碼。 如此就不用在每個檔案放置連線資料庫的程式碼, 而且如果需要更改名稱, 只要修改該引用檔即可。

建立資料庫引用檔 以下是本章所使用的資料庫引用檔：

建立資料庫引用檔 此引用檔的檔名為 mysql.inc.php, 所以其他 PHP 檔案只要執行『include ("mysql.inc.php");』, 就可以連線伺服器, 並且指定要使用的字元集以及資料庫。

資料庫引用檔的安全性 前面 5 - 11 頁曾經提到引用檔的副檔名可能會影響安全性, 這一點在資料庫引用檔上更要特別小心。 因為資料庫引用檔會包含連線伺服器的帳號和密碼, 如果不是使用 .php 為副檔名, 很有可能會被伺服器當作文字檔來處理：

資料庫引用檔的安全性 所以建議您建立資料庫引用檔時, 一定要以 .php 做為副檔名, 這樣即使有人直接連線引用檔, 伺服器也只會執行其中的程式碼, 而不會將內容顯示出來。

10 - 3 查詢資料 連線並登入資料庫伺服器之後, 便可以進行資料的查詢、新增、更新、刪除動作。 本節將先說明如何在 PHP 中查詢與取得查詢的結果, 下一節再介紹資料的新增、更新、刪除。

執行查詢資料的 SQL 敘述 前面 9 - 2 節說明了查詢資料庫的 SELECT 敘述, PHP 程式便是使用這個 SQL 敘述來查詢資料。 PHP 執行 SQL 敘述的函式為 mysql_query(), 下面是查詢資料的語法： 如果以 SELECT 敘述來查詢資料, 執行成功後 mysql_query() 會傳回一個資源代碼, 若執行失敗, 則會回傳 FALSE。

執行查詢資料的 SQL 敘述 下面我們以查詢 books 資料表的所有記錄為例, 說明 mysql_query() 的使用方法： 上面的程式碼會執行 "SELECT * FROM books" 這個 SQL 敘述, 如果成功查詢到資料, 將回傳資源代碼並將其儲存在 $result 變數, 我們便可以透過 $result 變數來讀取查詢結果。 請注意, mysql_query() 一次只能執行一個 SQL 敘述, 所以如果需要執行多個 SQL 敘述, 必須呼叫 mysql_query() 函式多次分別查詢。

使用 mysql_fetch_array() 讀取查詢結果 查詢到資料之後, PHP 提供了許多相關函式可以用來讀取查詢結果, 其中最容易使用的是 mysql_fetch_array() 函式, 其語法如下： mysql_fetch_array() 會透過資源代碼讀取一筆記錄, 第一次執行時會讀取第一筆記錄, 第二次執行則讀取第二筆, 隨後以此類推。 一旦讀完最後一筆後, 再執行 mysql_fetch_array() 便會傳回 FALSE。

使用 mysql_fetch_array() 讀取查詢結果 其示意圖如下：

使用 mysql_fetch_array() 讀取查詢結果 隨後以一個例子來說明, 下面程式使用 mysql_query() 執行上述 SELECT 敘述, 然後以 mysql_fetch_array() 逐筆讀取記錄：

使用 mysql_fetch_array() 讀取查詢結果

使用 mysql_fetch_array() 讀取查詢結果 執行結果： 因為 mysql_fetch_array() 會逐筆讀取資料, 而且讀完最後一筆後, 再執行 mysql_fetch_array 時會傳回 FALSE。

使用 mysql_fetch_array() 讀取查詢結果

使用 mysql_fetch_array() 讀取查詢結果

使用 mysql_fetch_array() 讀取查詢結果 執行結果：

使用 mysql_fetch_array() 讀取查詢結果 (A) 以 mysql_fetch_array() 執行結果做為 while 迴圈的判斷條件。 當讀完資料後, 再執行 mysql_fetch_array() 將回傳 FALSE, 就會讓 while 迴圈停止。

使用 mysql_fetch_row() 讀取查詢結果 除了以 mysql_fetch_array() 讀取查詢結果以外, 也可以使用 mysql_fetch_row() 函式來讀取。 下面是 mysql_fetch_row() 的語法： mysql_fetch_row() 的使用方法與 mysql_fetch_array() 完全一樣, 其透過資源代碼逐筆讀取記錄, 讀完最後一筆後, 再執行 mysql_fetch_row() 便會傳回 FALSE。

使用 mysql_fetch_row() 讀取查詢結果 不過唯一一點與 mysql_fetch_array() 不同的是, 其回傳的陣列只會用依序排列的數字編號做為索引, 不會以欄位名稱為索引。 其示意圖如下。

使用 mysql_fetch_row() 讀取查詢結果

使用 mysql_fetch_row() 讀取查詢結果 因為只用數字做為索引, 所以一般狀況下 mysql_fetch_ row() 的速度會比 mysql_fetch_array() 稍微快一點, 但是 PHP 官方網站也提到其速度上的差異並不明顯。 因此通常我們會使用較方便的 mysql_fetch_array() 來讀取資料。 不過為了未來可能需要閱讀他人的 PHP 程式碼, 您仍然需要瞭解 mysql_fetch_row() 的語法與功能。

取得查詢結果的筆數 當使用 mysql_query() 查詢資料後, 如果需要知道總共查到了幾筆記錄, 可以使用 mysql_num_rows() 來取得查詢結果的筆數。 mysql_num_rows() 的語法如下： mysql_num_rows() 會傳回一個整數, 代表查詢結果中總有多少筆記錄, 若無記錄, 則傳回 0。

取得查詢結果的筆數 以下我們修改前面 Ch10-03.php, 讓程式可以顯示總共找到幾本書：

取得查詢結果的筆數 執行結果。

取得查詢結果的筆數

使用二維陣列儲存查詢結果 前面我們都是一筆一筆地讀取然後處理記錄, 不過有時候可能會需要將查詢結果儲存起來, 以便重複使用。 例如繪製產品銷售報表時, 需要重複使用資料庫內的數據來統計平均銷量、暢銷商品...等。 如果依照前面的方法, 必須多次執行 mysql_query() 與 mysql_fetch_array() 來查詢與取得資料, 一旦資料量較大或系統繁忙時, 可能會造成過重的負荷。

使用二維陣列儲存查詢結果 因為關聯式資料庫使用資料表的形式來儲存資料, 所以如果需要重複處理查詢到的資料, 也可以很直觀地使用二維陣列先將這些資料儲存起來。 我們將用一個範例為您說明如何將查詢結果儲存到二維陣列, 下面程式會查詢 employee 資料表中所有員工的資料, 然後在網頁上用兩個表格分別顯示女性與男性員工的資料：

使用二維陣列儲存查詢結果

使用二維陣列儲存查詢結果

使用二維陣列儲存查詢結果

使用二維陣列儲存查詢結果 執行結果：

使用二維陣列儲存查詢結果 (A) 此處使用迴圈逐一讀取每筆記錄, 然後我們將 mysql_fetch_array() 回傳的一維陣列 $row[] 存放至 $employee[] 陣列內, 便會產生如下的二維陣列： 如果要讀取第 1 筆記錄的姓名欄位, 只要使用 $employee[0]['姓名'] 即可, 而 $employee[1]['性別'] 便代表第 2 筆記錄的性別欄位。

使用二維陣列儲存查詢結果 所以我們便可以在程式中重複使用這些資料, 不需要重新查詢資料庫。 (B) 使用 for 迴圈逐一讀取 $employee[0], $employee[1]... $employee[$total-1], 然後判斷姓名欄位是否為女, 如果是的話, 便顯示其資料。 上面程式為了減少複雜度, 所以程式碼 A 處只是單純地讀取記錄, 將資料存放在二維陣列, 然後在程式後面才判斷性別。

使用二維陣列儲存查詢結果 其實也可以直接在迴圈先判斷性別, 將不同性別的記錄分別存放在兩個二維陣列, 如此程式後面只要顯示陣列內的資料即可。

10 - 4 新增、更新、刪除資料 本節將說明如何使用 PHP 新增、更新、刪除資料庫中的記錄。 本節將專注在 PHP 語法的部分, 如果您對於 SQL 的 INSERT、UPDATE、DELETE 敘述有疑問之處, 請參考本書 9 - 5 節。

新增資料 如果要新增一筆記錄到資料庫, 可以使用 mysql_query() 來執行 INSERT 敘述： 下面程式碼會將訂單資料新增至 order 資料表內： 假設新增資料之後, order 資料表的內容如下：

新增資料 如果需要取得剛才新增訂單的編號 (例如寄確認信給購買者時, 信件中要提供訂單編號), 可以使用 mysql_insert_id() 取得前次新增的記錄中, 具有 auto_increment 屬性的欄位值： 接著我們以一個完整的範例程式, 說明如何將資料新增至資料庫中。

新增資料 下面是一個書籍存貨管理系統, 可以讓使用者將書籍與庫存數量輸入資料庫：

新增資料

新增資料 這個程式使用 inventory 資料表, 結構與內容如下： 下面是此程式的原始碼。

新增資料

新增資料

新增資料

新增資料 為了減少複雜度, 上面程式並沒有檢查使用者輸入的書籍名稱是否重複, 您可以如下改寫程式碼 (B) 處。 (A) 先判斷是否有表單傳遞過來的 name 與 qty 欄位值, 如果這兩個欄位都有值, 表示使用者按送出鈕將資料傳遞過來, 所以程式碼 (B) 處會將這兩個欄位值新增到 inventory 資料表。 (C) 定義表單時, 指定表單要將資料傳遞給 $_SERVER[“PHP_SELF”], 這個變數是目前此 PHP 程式檔案的名稱, 所以表單會將資料傳遞給自己。 為了減少複雜度, 上面程式並沒有檢查使用者輸入的書籍名稱是否重複, 您可以如下改寫程式碼 (B) 處。

新增資料 先用 SELECT 敘述查詢是否已有同樣名稱的書籍, 若否, 才將資料新增到資料庫中：

刪除資料 如果要從資料庫中刪除記錄, 可以使用 mysql_query() 來執行 DELETE 敘述： 下面程式碼會將 order 資料表中, 所有訂購者姓名欄位值為秋大頭的記錄刪除： 如果要取得剛才刪除了多少筆記錄, 可以使用 mysql_affected_rows() 函式：

刪除資料 我們將前面程式 Ch10-06.php 改寫成 Ch10-07.php, 使其具備刪除資料的功能：

刪除資料 Ch10-07.php 的程式碼與 Ch10-06.php 幾乎相同, 但是在顯示資料的迴圈中, 增加了一個刪除連結：

刪除資料 Ch10-07-01.php 程式的原始碼如下：

刪除資料

更新資料 如果要更新 / 修改資料庫中的記錄, 可以使用 mysql_query() 來執行 UPDATE 敘述： 下面程式碼會更新 order 資料表中訂單編號為 2 的記錄, 將訂購者姓名改成王小青： 如果要取得剛才成功更新了多少筆記錄, 可以使用 mysql_affected_rows() 函式：

更新資料 我們將前面程式 Ch10-07.php 改寫成 Ch10-08.php, 使其具備編輯資料的功能：

更新資料 加上編輯功能後, 便可以製作出一個完整的存貨管理系統, 其架構如下：

更新資料 請將 Ch10-07.php 複製為 Ch10-08.php, 然後請參考前一節, 在每一列記錄後面加上一個編輯連結：

更新資料

更新資料

更新資料 (A) 此處會判斷 edit 參數是否為空字串, 如果不是, 則依據 edit 參數內的編號數字查詢資料庫, 取得原本的資料放入 $row 陣列；若 edit 為空字串, 便將網頁轉向回 Ch08-10.php。 執行結果：

更新資料

更新資料 Ch10-08-02.php 取得表單傳遞過來的資料後, 會更新資料庫中的記錄, 下面則是 Ch10-08-02.php 的程式碼：

更新資料

10 - 5 網頁資料庫程式的安全隱憂 － SQL Injection

網頁資料庫程式的安全隱憂 － SQL Injection 以網頁程式常見的使用者登入系統為例, 假設使用了下面程式碼檢查使用者的帳號名稱與密碼：

網頁資料庫程式的安全隱憂 － SQL Injection 當使用者在登入表單的 user 欄位輸入 『tony』, pass 欄位輸入『secret』, 則程式會執行下面 SQL 敘述： 但是若使用者在 pass 欄位輸入的是『'OR ''='』, 那麼程式就會執行以下 SQL 敘述：

網頁資料庫程式的安全隱憂 － SQL Injection 要避免 SQL Injection, 要重要的就是不要相信使用者輸入的資料, 建議如果要以使用者輸入資料來執行 SQL 敘述前, 一定要先過濾掉特殊字元。 在 PHP 中, 您可以使用 mysql_real_escape_string() 函式過濾SQL 敘述中的特殊字元, 其語法如下。

網頁資料庫程式的安全隱憂 － SQL Injection mysql_real_escape_string() 會過濾字串中的特殊字元, 將其加上反斜線, 然後再傳回來, 例如：

網頁資料庫程式的安全隱憂 － SQL Injection 所以上例中『'OR''='』過濾就會變成『\'OR \'\'=\'』, 因為加上了反斜線, 特殊字元將會失去了原有的功能。 所以上面程式應該如下更改： magic_quotes_gpc 的問題 使用 mysql_real_escape_string() 時, 必須注意 PHP 的設定中是否開啟了 magic_quotes_gpc 的功能。

網頁資料庫程式的安全隱憂 － SQL Injection 若開啟了 magic_quotes_gpc, PHP 會自動為特殊字元加上反斜線。 如果沒注意到 magic_quotes_gpc 已經開啟, 使用 mysql_real_escape_string() 之後就會變成加了兩次反斜線, 而導致資料錯誤。 雖然 magic_quotes_gpc 會自動為特殊字元加上反斜線, 但建議您不要只依靠 magic_quotes_gpc 的功能。 否則若遇到系統管理者設定錯誤, 或是執行程式的系統原本就關閉 magic_quotes_gpc, 此時您的程式將立刻變得不安全。

網頁資料庫程式的安全隱憂 － SQL Injection 建議您如下使用 5 - 32 頁的 myStripslashes() 函式, 先將 magic_quotes_gpc 可能加上的反斜線刪除, 執行 SQL 敘述時再使用 mysql_real_escape_string() 過濾特殊字元, 如此才是最安全的作法：