PHP MySQL 簡易留言板實作範例（現代化重構版）

資料庫設計

CREATE DATABASE IF NOT EXISTS `guestbook` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `guestbook`;

CREATE TABLE `messages` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `content` TEXT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `ip_address` VARCHAR(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

PDO 版本完整程式碼

<!DOCTYPE html>
<html lang="zh-Hant">
<head>
    <meta charset="UTF-8">
    <title>PHP MySQL 留言板</title>
    <style>
        body { font-family: "Microsoft JhengHei", sans-serif; max-width: 800px; margin: 2rem auto; padding: 0 1rem; }
        textarea { width: 100%; min-height: 100px; margin-bottom: 0.5rem; }
        .msg { border-bottom: 1px solid #eee; padding: 1rem 0; }
        .meta { color: #666; font-size: 0.9rem; margin-bottom: 0.5rem; }
    </style>
</head>
<body>
<h1>留言板</h1>

<form method="POST" action="">
    <textarea name="content" placeholder="請輸入留言內容..." required></textarea>
    <button type="submit">送出留言</button>
</form>

<hr>

<?php
// 設定
$dsn = 'mysql:host=localhost;dbname=guestbook;charset=utf8mb4';
$user = 'your_username';
$pass = 'your_password';
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);

    // 處理表單送出
    if ($_SERVER['REQUEST_METHOD'] === 'POST' && !empty($_POST['content'])) {
        $stmt = $pdo->prepare('INSERT INTO messages (content, ip_address) VALUES (?, ?)');
        $stmt->execute([$_POST['content'], $_SERVER['REMOTE_ADDR'] ?? 'unknown']);
        header('Location: ' . $_SERVER['PHP_SELF']); // PRG 模式防重複送出
        exit;
    }

    // 讀取留言（分頁）
    $page = max(1, (int)($_GET['page'] ?? 1));
    $perPage = 10;
    $offset = ($page - 1) * $perPage;

    $total = $pdo->query('SELECT COUNT(*) FROM messages')->fetchColumn();
    $pages = ceil($total / $perPage);

    $stmt = $pdo->prepare('SELECT id, content, created_at, ip_address FROM messages ORDER BY created_at DESC LIMIT ? OFFSET ?');
    $stmt->bindValue(1, $perPage, PDO::PARAM_INT);
    $stmt->bindValue(2, $offset, PDO::PARAM_INT);
    $stmt->execute();
    $messages = $stmt->fetchAll();

    // 顯示留言
    foreach ($messages as $msg) {
        echo '<div class="msg">';
        echo '<div class="meta">#' . $msg['id'] . ' | ' . htmlspecialchars($msg['created_at']) . ' | IP: ' . htmlspecialchars($msg['ip_address']) . '</div>';
        echo '<div>' . nl2br(htmlspecialchars($msg['content'])) . '</div>';
        echo '</div>';
    }

    // 分頁導航
    if ($pages > 1) {
        echo '<div style="margin-top:1rem; text-align:center;">';
        for ($i = 1; $i <= $pages; $i++) {
            $class = $i === $page ? ' style="font-weight:bold;"' : '';
            echo "<a href='?page=$i'$class>$i</a> ";
        }
        echo '</div>';
    }

} catch (PDOException $e) {
    error_log('Database error: ' . $e->getMessage());
    echo '<p style="color:red;">系統暫時無法使用，請稍後再試。</p>';
}
?>
</body>
</html>

關鍵安全與最佳實踐說明

• 預處理語句：prepare() + execute() 完全防止 SQL 注入
• 輸出轉義：htmlspecialchars() 防止 XSS 攻擊
• PRG 模式：Post-Redirect-Get 避免重新整理重複送出
• 字符集：全鏈路 utf8mb4 支援 Emoji 與完整 Unicode
• 錯誤處理：PDO 異常模式 + error_log() 記錄，不向用戶暴露敏感資訊
• 分頁：LIMIT ... OFFSET 基礎分頁，大資料量建議改用游標分頁