您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 相關知識

PHP + MySQL 留言本完整實現教程（含資料庫設計、配置、核心代碼）

編輯：PHP 相關知識

留言本是 PHP 入門經典項目，涵蓋表單處理、數據庫 CRUD、分頁、後台管理等核心知識。本文提供完整可運行的實現代碼，採用 PDO 預處理語句 防止 SQL 注入，結構清晰易擴展。

1. 資料庫設計

CREATE DATABASE `guestbook` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `guestbook`;

-- 留言表
CREATE TABLE `messages` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `nickname` VARCHAR(50) NOT NULL COMMENT '昵稱',
    `subject` VARCHAR(100) DEFAULT NULL COMMENT '主題',
    `content` TEXT NOT NULL COMMENT '留言內容',
    `email` VARCHAR(100) DEFAULT NULL COMMENT '郵箱',
    `website` VARCHAR(200) DEFAULT NULL COMMENT '個人主頁',
    `qq` VARCHAR(20) DEFAULT NULL COMMENT 'QQ號',
    `ip` VARCHAR(45) NOT NULL COMMENT 'IPv4/IPv6',
    `user_agent` VARCHAR(255) DEFAULT NULL COMMENT '瀏覽器標識',
    `is_approved` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '審核狀態 0待審 1通過 2拒絕',
    `reply_content` TEXT DEFAULT NULL COMMENT '管理員回覆',
    `reply_time` DATETIME DEFAULT NULL COMMENT '回覆時間',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_approved_time` (`is_approved`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='留言主表';

2. 配置文件 `config.php`

<?php
return [
    'db' => [
        'host' => '127.0.0.1',
        'port' => 3306,
        'name' => 'guestbook',
        'user' => 'root',
        'pass' => 'your_password',
        'charset' => 'utf8mb4',
    ],
    'app' => [
        'page_size' => 10,
        'auto_approve' => false, // true=自動通過，false=需後台審核
    ],
];

3. 數據庫連接單例 `Database.php`

<?php
class Database {
    private static $instance = null;
    private $pdo;

    private function __construct(array $config) {
        $dsn = "mysql:host={$config['host']};port={$config['port']};dbname={$config['name']};charset={$config['charset']}";
        $this->pdo = new PDO($dsn, $config['user'], $config['pass'], [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]);
    }

    public static function getInstance(array $config): self {
        if (self::$instance === null) {
            self::$instance = new self($config);
        }
        return self::$instance;
    }

    public function getPDO(): PDO { return $this->pdo; }
}

4. 核心業務邏輯 `MessageService.php`

<?php
class MessageService {
    private $pdo;

    public function __construct(PDO $pdo) { $this->pdo = $pdo; }

    // 提交留言
    public function add(array $data): bool {
        $sql = "INSERT INTO messages (nickname, subject, content, email, website, qq, ip, user_agent, is_approved)
                VALUES (:nickname, :subject, :content, :email, :website, :qq, :ip, :ua, :approved)";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([
            ':nickname' => $data['nickname'],
            ':subject'  => $data['subject'] ?? '',
            ':content'  => $data['content'],
            ':email'    => $data['email'] ?? '',
            ':website'  => $data['website'] ?? '',
            ':qq'       => $data['qq'] ?? '',
            ':ip'       => $data['ip'],
            ':ua'       => $data['ua'],
            ':approved' => $data['approved'] ? 1 : 0,
        ]);
    }

    // 前台分頁列表（僅顯示通過審核）
    public function getList(int $page = 1, int $pageSize = 10): array {
        $offset = ($page - 1) * $pageSize;
        $stmt = $this->pdo->prepare("SELECT * FROM messages WHERE is_approved = 1 ORDER BY created_at DESC LIMIT :limit OFFSET :offset");
        $stmt->bindValue(':limit', $pageSize, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function getTotalApproved(): int {
        return (int)$this->pdo->query("SELECT COUNT(*) FROM messages WHERE is_approved = 1")->fetchColumn();
    }

    // 後台管理：獲取所有留言（含待審）
    public function getAllForAdmin(int $page = 1, int $pageSize = 20): array {
        $offset = ($page - 1) * $pageSize;
        $stmt = $this->pdo->prepare("SELECT * FROM messages ORDER BY created_at DESC LIMIT :limit OFFSET :offset");
        $stmt->bindValue(':limit', $pageSize, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    // 審核/回覆
    public function moderate(int $id, int $status, string $reply = ''): bool {
        $sql = "UPDATE messages SET is_approved = :status, reply_content = :reply, reply_time = NOW() WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([':status' => $status, ':reply' => $reply, ':id' => $id]);
    }
}

5. 前台頁面 `index.php`

<?php
require_once 'config.php';
require_once 'Database.php';
require_once 'MessageService.php';

$cfg = require 'config.php';
$db  = Database::getInstance($cfg['db']);
$svc = new MessageService($db->getPDO());

// 處理提交
$errors = [];
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $nickname = trim($_POST['nickname'] ?? '');
    $content  = trim($_POST['content'] ?? '');
    if ($nickname === '') $errors[] = '昵稱不能為空';
    if ($content === '')  $errors[] = '內容不能為空';
    if (!$errors) {
        $data = [
            'nickname' => $nickname,
            'subject'  => trim($_POST['subject'] ?? ''),
            'content'  => $content,
            'email'    => trim($_POST['email'] ?? ''),
            'website'  => trim($_POST['website'] ?? ''),
            'qq'       => trim($_POST['qq'] ?? ''),
            'ip'       => $_SERVER['REMOTE_ADDR'],
            'ua'       => $_SERVER['HTTP_USER_AGENT'],
            'approved' => $cfg['app']['auto_approve'],
        ];
        $svc->add($data);
        header('Location: index.php?success=1');
        exit;
    }
}

// 分頁
$page = max(1, (int)($_GET['page'] ?? 1));
$pageSize = $cfg['app']['page_size'];
$messages = $svc->getList($page, $pageSize);
$total = $svc->getTotalApproved();
$totalPages = ceil($total / $pageSize);
?>
<!DOCTYPE html>
<html lang="zh-Hant">
<head><meta charset="UTF-8"><title>留言本</title>
<style>body{font-family:system-ui;margin:2rem auto;max-width:720px}.msg{border:1px solid #ddd;padding:1rem;margin:1rem 0;border-radius:4px}.msg meta{color:#666;font-size:.9em}.err{color:#c00}.ok{color:#090}</style></head>
<body>
<h1>📝 留言本</h1>

<?php if (isset($_GET['success'])): ?><p class="ok">提交成功！</p><?php endif; ?>
<?php foreach ($errors as $e): ?><p class="err"><?= htmlspecialchars($e) ?></p><?php endforeach; ?>

<form method="post" action="index.php">
    <div><label>昵稱*</label><br><input name="nickname" required maxlength="50"></div>
    <div><label>主題</label><br><input name="subject" maxlength="100"></div>
    <div><label>內容*</label><br><textarea name="content" rows="5" required></textarea></div>
    <div><label>郵箱</label><br><input name="email" type="email" maxlength="100"></div>
    <div><label>主頁</label><br><input name="website" type="url" maxlength="200" placeholder="https://"></div>
    <div><label>QQ</label><br><input name="qq" maxlength="20"></div>
    <button type="submit">提交留言</button>
</form>

<hr>
<h2>最新留言</h2>
<?php foreach ($messages as $msg): ?>
    <div class="msg">
        <strong><?= htmlspecialchars($msg['nickname']) ?></strong>
        <?php if ($msg['subject']): ?><em>【<?= htmlspecialchars($msg['subject']) ?>】</em><?php endif; ?>
        <div><?= nl2br(htmlspecialchars($msg['content'])) ?></div>
        <meta><?= $msg['created_at'] ?> <?php if ($msg['reply_content']): ?>| 管理員回覆: <?= nl2br(htmlspecialchars($msg['reply_content'])) ?><?php endif; ?></meta>
    </div>
<?php endforeach; ?>

<nav>
    <?php for ($p = 1; $p <= $totalPages; $p++): ?>
        <a href="?page=<?= $p ?>" <?= $p === $page ? 'style="font-weight:bold"' : '' ?>><?= $p ?></a>
    <?php endfor; ?>
</nav>
</body></html>

6. 後台管理 `admin.php`（簡易版，實際需加登錄驗證）

<?php
require_once 'config.php';
require_once 'Database.php';
require_once 'MessageService.php';

$cfg = require 'config.php';
$db  = Database::getInstance($cfg['db']);
$svc = new MessageService($db->getPDO());

// 處理審核/回覆
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action'], $_POST['id'])) {
    $id = (int)$_POST['id'];
    if ($_POST['action'] === 'approve') {
        $svc->moderate($id, 1, $_POST['reply'] ?? '');
    } elseif ($_POST['action'] === 'reject') {
        $svc->moderate($id, 2, $_POST['reply'] ?? '');
    }
    header('Location: admin.php');
    exit;
}

$page = max(1, (int)($_GET['page'] ?? 1));
$messages = $svc->getAllForAdmin($page, 20);
// 總數查詢略...
?>
<!DOCTYPE html>
<html lang="zh-Hant">
<head><meta charset="UTF-8"><title>後台管理</title><style>table{width:100%;border-collapse:collapse}td,th{border:1px solid #ddd;padding:.5rem}form textarea{width:100%}</style></head>
<body>
<h1>🛠 留言管理</h1>
<table><thead><tr><th>ID</th><th>昵稱</th><th>主題</th><th>內容</th><th>IP</th><th>時間</th><th>狀態</th><th>操作</th></tr></thead><tbody>
<?php foreach ($messages as $msg): ?>
<tr>
    <td><?= $msg['id'] ?></td>
    <td><?= htmlspecialchars($msg['nickname']) ?></td>
    <td><?= htmlspecialchars($msg['subject']) ?></td>
    <td><?= nl2br(htmlspecialchars($msg['content'])) ?></td>
    <td><?= $msg['ip'] ?></td>
    <td><?= $msg['created_at'] ?></td>
    <td>
        <?php 
            $status = ['待審','通過','拒絕'];
            echo $status[$msg['is_approved']] ?? '未知';
        ?>
    </td>
    <td>
        <form method="post">
            <input type="hidden" name="id" value="<?= $msg['id'] ?>">
            <textarea name="reply" placeholder="回覆內容（可選）" rows="2"><?= htmlspecialchars($msg['reply_content'] ?? '') ?></textarea>
            <br>
            <button name="action" value="approve" <?= $msg['is_approved']==1?'disabled':'' ?>>通過</button>
            <button name="action" value="reject" <?= $msg['is_approved']==2?'disabled':'' ?>>拒絕</button>
        </form>
    </td>
</tr>
<?php endforeach; ?>
</tbody></table>
</body></html>

安全與擴展建議

XSS 防護：輸出時統一 htmlspecialchars()，內容存儲原文。
CSRF 防護：表單添加 <input type="hidden" name="csrf" value="<?= $_SESSION['csrf'] ?>"> 並驗證。
垃圾內容過濾：接入關鍵詞過濾、Akismet API 或簡單的頻率限制（同 IP 分鐘限制）。
分頁優化：大數據量改用游標分頁（WHERE id < last_id）避免 OFFSET 深分頁性能問題。
後台認證：務必加入登錄驗證（Session/JWT），區分管理員權限。