使用 MySQLi 進行連線與簡易的 CRUD 操作
本文將介紹如何以物件導向的方式使用 MySQLi 延伸套件連結 MySQL (MariaDB) Server，並進行簡易的資料新增、查詢、更新、刪除等操作。建立連線
PHP 可以使用 mysqli 類別建立與 MySQL Server 間的連線，要實例化一個 mysqli 物件需要依序提供：MySQL Server 的 IP、資料庫的使用者名稱、使用者密碼、資料庫的名稱、MySQL Server 的連接埠、以及要使用的插座 (Socket) 或是具名管道 (Named Pipe)；後兩者一般都是使用預設值即可。下列的程式碼為物件導向 PHP 使用 mysqli 來建立與 MySQL Server 間的連線：
$dbHost = 'localhost';
$dbUser = 'db_username';
$dbPassword = 'db_user_password';
$dbName = 'db_name';
$connect = new mysqli($dbHost, $dbUser, $dbPassword, $dbName);
if ($connect->connect_errno) {
echo "Failed to connect to MySQL: (" . $connect->connect_errno
. ") " . $connect->connect_error . PHP_EOL;
}
echo $connect->host_info . PHP_EOL;
CRUD
在建立了跟資料庫伺服器的連線後，就可以對選定的資料庫進行資料的建立 (Create)、讀取 (Read)、更新 (Update)、以及刪除 (Delete) 等操作；這四項資料操作取其首字母，而被簡稱為 CRUD。MySQL 是關聯式資料庫管理系統 (Relational DataBase Management System, RDBMS)，使用結構化查詢語言 (Structured Query Language, SQL) 進行管理。SQL 使用資料操作語言 (Data Manipulation Language, DML) 來處理資料庫中資料表裡的資料，這些操作對應到 CRUD 分別是 INSERT、SELECT、UPDATE、以及 DELETE。以 PHP 開發的應用中，查詢敘述通常是動態產生的 (例如，使用者在表單上輸入要查詢的資料)，如果沒有妥善的處理查詢敘述，非常容易讓惡意的使用者以 SQL 隱碼攻擊 (SQL Injection) 的方式，破壞或是入侵資料庫系統。mysqli 提供了預備敘述，讓輸入參數化以避免 SQL 隱碼攻擊。
INSERT
下例會在資料表中新增資料，假設資料 (姓名、電子郵件信箱、住址、以及訊息等) 是由使用者輸入而取得：$name = '許杰夫';
$email = 'jeff.hsu@gmail.com';
$zip = 10001;
$address = '台北市中正區中正路1號';
$score = 98.9;
$message = '資料新增';
$sql = "INSERT INTO `users` (`name`, `email`, `zip`, `address`, `score`, `message`)
VALUES (?, ?, ?, ?, ?, ?)";
$stmt = $connect->prepare($sql);
if (!$stmt) {
echo "Prepare failed: (" . $connect->errno . ") " . $connect->error;
}
$stmt->bind_param('ssisds', $name, $email, $zip, $address, $score, $message);
$stmt->execute();
$stmt->close();
為了避免 SQL 隱碼攻擊，需要：
1. 將查詢敘述參數化，mysqli 使用問號 ? 做為佔位符 (Placeholder) 來取代變數名稱；
2. 利用 mysqli::prepare 產生預備敘述 (建立 mysqli_stmt 物件)；
3. 使用 mysqli_stmt::bind_param 將變數綁定到預備敘述作為參數，其中第一個參數為變數型別，分別以：i 代表整數、s 代表字串、d 代表雙精度浮點數、b 代表二進位大型物件；
4. 使用 mysqli_stmt::execute 執行綁定參數後的預備敘述；
5. 在查詢完成後使用 mysqli_stmt::close() 將查詢敘述關閉。
SELECT
下例會讀取資料表中第一筆到最後一筆的資料：$id = $connect->insert_id;
$sql = "SELECT * FROM `users` WHERE `id` <= ?";
$stmt = $connect->prepare($sql);
if (!$stmt) {
echo "Prepare failed: (" . $connect->errno . ") " . $connect->error;
}
$stmt->bind_param('i',$id);
$stmt->execute();
$stmt->bind_result($id, $name, $email, $zip, $address, $score, $message);
while($stmt->fetch()){
echo $id . " , " . $name . " , " . $email . " , " . $zip
. $address . " , " . $score . " , " . $message . PHP_EOL;
}
$stmt->close();
在本例中：
1. 使用了 mysqli::$insert_id，傳回最後查詢 (通常是 INSERT) 時自動生成的 ID (當資料表中有 AUTO_INCREMENT 欄位時)；
2. 預備敘述的參數綁定與執行；
3. mysqli_stmt::bind_result 將預備敘述執行結果 (結果集合) 中的欄位名稱綁定到指定的變數；
4. mysqli_stmt::fetch 會將結果集合中的資料逐筆對應到由 mysqli_stmt::bind_result 綁定的變數；
5. 每執行一次 mysqli_stmt::fetch，結果集合就會少一筆記錄，直到結果集合成為空集合， mysqli_stmt::fetch 會傳回 false。
6. 在查詢完成後使用 mysqli_stmt::close() 將查詢敘述關閉。
除了上述的輸出方式之外，也可以透過 mysqli_stmt::get_result 將結果集合轉換為 mysqli_result 物件集合後，再使用 mysqli_result::fetch_object 以物件的方式輸出資料。如下列的程式：
$objSet = $stmt->get_result();
while($obj = $objSet->fetch_object()){
echo $obj->id . " , " . $obj->name . " , " . $obj->email . " , " . $obj->zip
. $obj->address . " , " . $obj->score . " , " . $obj->message , PHP_EOL;
}
要注意的是，在使用 mysqli_stmt::get_result 後，會將結果集合清空。
UPDATE
下例會更新資料表中的最後一筆新增的資料：$name = '許班杰';
$email = 'benjamin@test.com';
$sql = "UPDATE `users` SET `name` = ?, `email` = ? WHERE `id` = ?";
$stmt = $connect->prepare($sql);
if (!$stmt) {
echo "Prepare failed: (" . $connect->errno . ") "
. $connect->error;
}
$stmt->bind_param('ssi', $name, $email, $id);
$stmt->execute();
$stmt->close();
DELETE
下例會刪除資料表中的最後一筆新增的資料：$sql = "DELETE FROM `users` WHERE `id` = ?";
$stmt = $connect->prepare($sql);
if (!$stmt) {
echo "Prepare failed: (" . $connect->errno . ") "
. $connect->error;
}
$stmt->bind_param('i',$id);
$stmt->execute();
$stmt->close();
$connect->close();
在所有的查詢工作結束後，記得要將連線關閉，釋放佔用的資源。
沒有留言:
張貼留言