怎样在PHP项目中实现笔记系统？

本文目录导读：

1. 系统架构设计
2. 数据库设计
3. 核心功能实现
4. 高级功能实现
5. 安全考虑
6. 性能优化

我来详细介绍如何实现一个功能完整的PHP笔记系统,我将从基础架构到高级功能逐步说明。

系统架构设计

目录结构

note-system/
├── config/
│   └── database.php
├── includes/
│   ├── auth.php
│   ├── functions.php
│   └── session.php
├── assets/
│   ├── css/
│   ├── js/
│   └── images/
├── views/
│   ├── login.php
│   ├── register.php
│   ├── dashboard.php
│   └── note-editor.php
├── api/
│   ├── notes.php
│   ├── categories.php
│   └── search.php
└── index.php

数据库设计

SQL建表语句

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL
);
-- 笔记分类表
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    color VARCHAR(7) DEFAULT '#007bff',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- 笔记主表
CREATE TABLE notes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    category_id INT,VARCHAR(255) NOT NULL,
    content TEXT,
    is_pinned BOOLEAN DEFAULT FALSE,
    is_archived BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_user_created (user_id, created_at DESC),
    FULLTEXT INDEX idx_search (title, content)
);
-- 标签表
CREATE TABLE tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_tag (user_id, name)
);
-- 笔记-标签关联表
CREATE TABLE note_tags (
    note_id INT,
    tag_id INT,
    PRIMARY KEY (note_id, tag_id),
    FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

核心功能实现

1 数据库连接配置 (config/database.php)

<?php
class Database {
    private $host = 'localhost';
    private $db_name = 'note_system';
    private $username = 'root';
    private $password = '';
    private $conn;
    public function getConnection() {
        $this->conn = null;
        try {
            $this->conn = new PDO(
                "mysql:host={$this->host};dbname={$this->db_name};charset=utf8mb4",
                $this->username,
                $this->password
            );
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        } catch(PDOException $e) {
            error_log("Connection error: " . $e->getMessage());
            return null;
        }
        return $this->conn;
    }
}

2 笔记CRUD操作 (api/notes.php)

<?php
require_once '../config/database.php';
require_once '../includes/auth.php';
class NoteManager {
    private $db;
    private $user_id;
    public function __construct($user_id) {
        $database = new Database();
        $this->db = $database->getConnection();
        $this->user_id = $user_id;
    }
    // 创建笔记
    public function createNote($title, $content, $category_id = null, $tags = []) {
        try {
            $this->db->beginTransaction();
            $query = "INSERT INTO notes (user_id, category_id, title, content) 
                      VALUES (:user_id, :category_id, :title, :content)";
            $stmt = $this->db->prepare($query);
            $stmt->bindParam(':user_id', $this->user_id);
            $stmt->bindParam(':category_id', $category_id);
            $stmt->bindParam(':title', $title);
            $stmt->bindParam(':content', $content);
            $stmt->execute();
            $note_id = $this->db->lastInsertId();
            // 添加标签
            if (!empty($tags)) {
                $this->addTagsToNote($note_id, $tags);
            }
            $this->db->commit();
            return ['success' => true, 'note_id' => $note_id];
        } catch (Exception $e) {
            $this->db->rollBack();
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }
    // 获取用户的所有笔记
    public function getNotes($page = 1, $per_page = 20, $category_id = null, $search = null) {
        $offset = ($page - 1) * $per_page;
        $query = "SELECT n.*, c.name as category_name, c.color as category_color 
                  FROM notes n 
                  LEFT JOIN categories c ON n.category_id = c.id 
                  WHERE n.user_id = :user_id AND n.is_archived = FALSE";
        $params = [':user_id' => $this->user_id];
        if ($category_id) {
            $query .= " AND n.category_id = :category_id";
            $params[':category_id'] = $category_id;
        }
        if ($search) {
            $query .= " AND MATCH(n.title, n.content) AGAINST(:search IN BOOLEAN MODE)";
            $params[':search'] = $search;
        }
        $query .= " ORDER BY n.is_pinned DESC, n.updated_at DESC LIMIT :limit OFFSET :offset";
        $stmt = $this->db->prepare($query);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->bindValue(':limit', $per_page, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }
    // 更新笔记
    public function updateNote($note_id, $data) {
        try {
            $query = "UPDATE notes SET 
                      title = :title,
                      content = :content,
                      category_id = :category_id
                      WHERE id = :note_id AND user_id = :user_id";
            $stmt = $this->db->prepare($query);
            $stmt->bindParam(':title', $data['title']);
            $stmt->bindParam(':content', $data['content']);
            $stmt->bindParam(':category_id', $data['category_id']);
            $stmt->bindParam(':note_id', $note_id);
            $stmt->bindParam(':user_id', $this->user_id);
            return $stmt->execute();
        } catch (PDOException $e) {
            return false;
        }
    }
    // 删除笔记
    public function deleteNote($note_id) {
        $query = "DELETE FROM notes WHERE id = :note_id AND user_id = :user_id";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':note_id', $note_id);
        $stmt->bindParam(':user_id', $this->user_id);
        return $stmt->execute();
    }
    // 置顶/取消置顶
    public function togglePin($note_id) {
        $query = "UPDATE notes SET is_pinned = NOT is_pinned 
                  WHERE id = :note_id AND user_id = :user_id";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':note_id', $note_id);
        $stmt->bindParam(':user_id', $this->user_id);
        return $stmt->execute();
    }
    // 归档/取消归档
    public function toggleArchive($note_id) {
        $query = "UPDATE notes SET is_archived = NOT is_archived 
                  WHERE id = :note_id AND user_id = :user_id";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':note_id', $note_id);
        $stmt->bindParam(':user_id', $this->user_id);
        return $stmt->execute();
    }
    // 添加标签到笔记
    private function addTagsToNote($note_id, $tags) {
        foreach ($tags as $tag_name) {
            // 查找或创建标签
            $tag_query = "INSERT IGNORE INTO tags (user_id, name) VALUES (:user_id, :name)";
            $stmt = $this->db->prepare($tag_query);
            $stmt->bindParam(':user_id', $this->user_id);
            $stmt->bindParam(':name', $tag_name);
            $stmt->execute();
            // 获取标签ID
            $tag_id_query = "SELECT id FROM tags WHERE user_id = :user_id AND name = :name";
            $stmt = $this->db->prepare($tag_id_query);
            $stmt->bindParam(':user_id', $this->user_id);
            $stmt->bindParam(':name', $tag_name);
            $stmt->execute();
            $tag_id = $stmt->fetchColumn();
            // 关联笔记和标签
            $link_query = "INSERT IGNORE INTO note_tags (note_id, tag_id) VALUES (:note_id, :tag_id)";
            $stmt = $this->db->prepare($link_query);
            $stmt->bindParam(':note_id', $note_id);
            $stmt->bindParam(':tag_id', $tag_id);
            $stmt->execute();
        }
    }
}

3 笔记编辑器视图 (views/note-editor.php)

<?php
require_once '../includes/auth.php';
require_once '../includes/functions.php';
if (!is_logged_in()) {
    header('Location: login.php');
    exit;
}
$note_id = isset($_GET['id']) ? (int)$_GET['id'] : null;
$note = null;
if ($note_id) {
    $note = get_note_by_id($note_id);
}
?>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">笔记编辑器</title>
    <link rel="stylesheet" href="../assets/css/editor.css">
</head>
<body>
    <div class="editor-container">
        <div class="editor-header">
            <input type="text" id="note-title" 
                   value="<?php echo htmlspecialchars($note['title'] ?? ''); ?>" 
                   placeholder="输入标题...">
            <div class="editor-actions">
                <select id="category-select">
                    <option value="">选择分类</option>
                    <?php
                    $categories = get_user_categories();
                    foreach ($categories as $category) {
                        $selected = ($note && $note['category_id'] == $category['id']) ? 'selected' : '';
                        echo "<option value='{$category['id']}' {$selected}>{$category['name']}</option>";
                    }
                    ?>
                </select>
                <button onclick="saveNote()" class="btn-save">保存</button>
                <button onclick="archiveNote()" class="btn-archive">归档</button>
                <button onclick="deleteNote()" class="btn-delete">删除</button>
            </div>
        </div>
        <div class="editor-body">
            <textarea id="note-content" placeholder="开始写作..."><?php 
                echo htmlspecialchars($note['content'] ?? ''); 
            ?></textarea>
        </div>
        <div class="editor-footer">
            <div class="tags-input">
                <input type="text" id="tag-input" placeholder="添加标签 (按回车)"> 
                <div id="tags-container">
                    <?php if ($note): ?>
                        <?php foreach (get_note_tags($note['id']) as $tag): ?>
                            <span class="tag"><?php echo htmlspecialchars($tag); ?></span>
                        <?php endforeach; ?>
                    <?php endif; ?>
                </div>
            </div>
            <div class="note-info">
                <span id="word-count">0 字</span>
                <span id="last-saved">最后保存: <span id="save-time">--</span></span>
            </div>
        </div>
    </div>
    <script src="../assets/js/editor.js"></script>
    <script>
    const noteId = <?php echo $note_id ?? 'null'; ?>;
    // 自动保存功能
    let autoSaveTimer = null;
    const saveNote = async () => {
        const title = document.getElementById('note-title').value;
        const content = document.getElementById('note-content').value;
        const category_id = document.getElementById('category-select').value;
        const tags = getTags();
        try {
            const response = await fetch('/api/save-note.php', {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/json',
                },
                body: JSON.stringify({
                    id: noteId,
                    title: title,
                    content: content,
                    category_id: category_id,
                    tags: tags
                })
            });
            const result = await response.json();
            if (result.success) {
                updateSaveTime();
                showToast('保存成功');
            }
        } catch (error) {
            console.error('保存失败:', error);
            showToast('保存失败，请重试', 'error');
        }
    };
    // 实时字数统计
    document.getElementById('note-content').addEventListener('input', function() {
        const wordCount = this.value.length;
        document.getElementById('word-count').textContent = `${wordCount} 字`;
    });
    // 标签处理
    document.getElementById('tag-input').addEventListener('keypress', function(e) {
        if (e.key === 'Enter') {
            e.preventDefault();
            const tag = this.value.trim();
            if (tag && !getTags().includes(tag)) {
                addTag(tag);
                this.value = '';
            }
        }
    });
    function addTag(tag) {
        const container = document.getElementById('tags-container');
        const tagElement = document.createElement('span');
        tagElement.className = 'tag';
        tagElement.innerHTML = `${tag} <span onclick="this.parentElement.remove()">&times;</span>`;
        container.appendChild(tagElement);
    }
    function getTags() {
        const tagElements = document.querySelectorAll('#tags-container .tag');
        return Array.from(tagElements).map(el => el.textContent.replace('×', '').trim());
    }
    </script>
</body>
</html>

高级功能实现

1 搜索功能

// api/search.php
class NoteSearch {
    public function searchNotes($user_id, $query, $filters = []) {
        $sql = "SELECT n.*, c.name as category_name 
                FROM notes n 
                LEFT JOIN categories c ON n.category_id = c.id 
                WHERE n.user_id = :user_id";
        $params = [':user_id' => $user_id];
        // 全文搜索
        if ($query) {
            $sql .= " AND MATCH(n.title, n.content) AGAINST(:query IN BOOLEAN MODE)";
            $params[':query'] = $query;
        }
        // 日期筛选
        if (!empty($filters['date_from'])) {
            $sql .= " AND n.created_at >= :date_from";
            $params[':date_from'] = $filters['date_from'] . ' 00:00:00';
        }
        if (!empty($filters['date_to'])) {
            $sql .= " AND n.created_at <= :date_to";
            $params[':date_to'] = $filters['date_to'] . ' 23:59:59';
        }
        // 分类筛选
        if (!empty($filters['category_id'])) {
            $sql .= " AND n.category_id = :category_id";
            $params[':category_id'] = $filters['category_id'];
        }
        $sql .= " ORDER BY n.updated_at DESC LIMIT 50";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }
}

2 导出功能

// api/export.php
class NoteExport {
    public function exportToMarkdown($note_id, $user_id) {
        $note = $this->getNote($note_id, $user_id);
        $markdown = "# {$note['title']}\n\n";
        $markdown .= "> 创建时间: {$note['created_at']}\n";
        $markdown .= "> 更新时间: {$note['updated_at']}\n\n";
        $markdown .= "---\n\n";
        $markdown .= $note['content'];
        header('Content-Type: text/markdown');
        header('Content-Disposition: attachment; filename="' . 
               sanitize_filename($note['title']) . '.md"');
        echo $markdown;
    }
    public function exportToPDF($note_ids, $user_id) {
        require_once '../vendor/autoload.php'; // 使用mpdf或其他PDF库
        $mpdf = new \Mpdf\Mpdf();
        foreach ($note_ids as $note_id) {
            $note = $this->getNote($note_id, $user_id);
            $html = "<h1>{$note['title']}</h1>";
            $html .= "<p>创建时间: {$note['created_at']}</p>";
            $html .= "<hr>";
            $html .= nl2br($note['content']);
            $html .= "<pagebreak>";
            $mpdf->WriteHTML($html);
        }
        $mpdf->Output('notes-export.pdf', 'D');
    }
}

安全考虑

1 XSS防护

// 输出时使用htmlspecialchars
function h($string) {
    return htmlspecialchars($string, ENT_QUOTES, 'UTF-8');
}
// 存储时使用参数化查询
// 使用Content-Security-Policy头
header("Content-Security-Policy: default-src 'self'; script-src 'self' 'unsafe-inline'");

2 CSRF防护

// 生成CSRF令牌
function generateCSRFToken() {
    if (empty($_SESSION['csrf_token'])) {
        $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
    }
    return $_SESSION['csrf_token'];
}
// 验证CSRF令牌
function verifyCSRFToken($token) {
    return hash_equals($_SESSION['csrf_token'], $token);
}

性能优化

1 缓存策略

class NoteCache {
    public function getCachedNotes($user_id, $cache_key) {
        $cache_file = "cache/{$user_id}/{$cache_key}.cache";
        if (file_exists($cache_file) && (time() - filemtime($cache_file)) < 3600) {
            return unserialize(file_get_contents($cache_file));
        }
        return null;
    }
    public function setCachedNotes($user_id, $cache_key, $data) {
        $cache_dir = "cache/{$user_id}";
        if (!is_dir($cache_dir)) {
            mkdir($cache_dir, 0755, true);
        }
        file_put_contents(
            "{$cache_dir}/{$cache_key}.cache", 
            serialize($data)
        );
    }
}

这个笔记系统实现了：

• 完整的CRUD操作
• 分类和标签管理
• 搜索和筛选功能
• 自动保存功能
• 安全防护措施
• 性能优化方案

你可以根据实际需求在此基础上添加：

• 富文本编辑器（如TinyMCE、Quill等）
• 版本历史管理
• 协作编辑功能
• 移动端适配
• API接口供外部调用