原始碼使用已棄用的 mysql_* 函式 (PHP 7.0 移除)。本文提供完整 mysqli 版本,並徹底解決中文亂碼的四個層面。
<?php
$host = 'localhost';
$user = 'root';
$pass = 'your_password';
$dbname = 'infosystem';
// 1. 建立連線
$mysqli = new mysqli($host, $user, $pass, $dbname);
if ($mysqli->connect_errno) {
die("連線失敗: " . $mysqli->connect_error);
}
// 2. 關鍵:設定字元集 (解決亂碼核心)
$mysqli->set_charset('utf8mb4');
// 3. 執行查詢
$query = "SELECT dept_name, emp_name, pc_name FROM info";
$result = $mysqli->query($query);
if (!$result) {
die("查詢失敗: " . $mysqli->error);
}
// 4. 輸出 HTML 表格 (防 XSS)
echo "<table border='1' cellpadding='5' cellspacing='0'>";
echo "<tr><th>部門名稱</th><th>員工姓名</th><th>PC 名稱</th></tr>";
while ($row = $result->fetch_assoc()) {
echo "<tr>";
echo "<td>" . htmlspecialchars($row['dept_name']) . "</td>";
echo "<td>" . htmlspecialchars($row['emp_name']) . "</td>";
echo "<td>" . htmlspecialchars($row['pc_name']) . "</td>";
echo "</tr>";
}
echo "</table>";
// 5. 釋放資源
$result->free();
$mysqli->close();
?>
header('Content-Type: text/html; charset=utf-8');
$mysqli->set_charset('utf8mb4'); // 必須在查詢前呼叫
-- 建表指定
CREATE TABLE info (
id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50),
emp_name VARCHAR(50),
pc_name VARCHAR(50)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 修改現有表
ALTER TABLE info CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
編輯器儲存為 UTF-8 無 BOM (非 UTF-8 with BOM)。
<?php
try {
$pdo = new PDO(
"mysql:host=$host;dbname=$dbname;charset=utf8mb4",
$user, $pass,
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);
$stmt = $pdo->query("SELECT dept_name, emp_name, pc_name FROM info");
echo "<table><tr><th>部門</th><th>姓名</th><th>PC</th></tr>";
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "<tr><td>" . htmlspecialchars($row['dept_name']) . "</td>";
echo "<td>" . htmlspecialchars($row['emp_name']) . "</td>";
echo "<td>" . htmlspecialchars($row['pc_name']) . "</td></tr>";
}
echo "</table>";
} catch (PDOException $e) {
error_log("DB Error: " . $e->getMessage());
echo "系統暫時無法連線資料庫";
}
?>
| 舊函式 (已移除) | mysqli 物件導向 | mysqli 程序式 | PDO |
|---|---|---|---|
| mysql_connect | new mysqli() | mysqli_connect() | new PDO() |
| mysql_select_db | $mysqli->select_db() | mysqli_select_db() | DSN 指定 dbname |
| mysql_query | $mysqli->query() | mysqli_query() | $pdo->query() |
| mysql_fetch_array | $result->fetch_array() | mysqli_fetch_array() | $stmt->fetch() |
| mysql_fetch_assoc | $result->fetch_assoc() | mysqli_fetch_assoc() | $stmt->fetch(PDO::FETCH_ASSOC) |
| mysql_free_result | $result->free() | mysqli_free_result() | 自動 / $stmt = null |
| mysql_close | $mysqli->close() | mysqli_close() | $pdo = null |
| mysql_error | $mysqli->error | mysqli_error() | $e->getMessage() |
| mysql_real_escape_string | $mysqli->real_escape_string() | mysqli_real_escape_string() | 參數綁定 (不需轉義) |