php实现新闻管理

数据库设计

新闻管理系统需要设计合理的数据库表结构。常见的表包括新闻表（news）、分类表（categories）和用户表（users）。

新闻表字段示例：

CREATE TABLE news (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    category_id INT,
    author_id INT,
    publish_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    status ENUM('draft', 'published') DEFAULT 'draft',
    FOREIGN KEY (category_id) REFERENCES categories(id),
    FOREIGN KEY (author_id) REFERENCES users(id)
);

分类表字段示例：

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE
);

连接数据库

使用PDO连接MySQL数据库：

$host = 'localhost';
$dbname = 'news_db';
$username = 'root';
$password = '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

新闻增删改查功能

新闻添加功能实现：

function addNews($title, $content, $categoryId, $authorId) {
    global $pdo;
    $stmt = $pdo->prepare("INSERT INTO news (title, content, category_id, author_id) VALUES (?, ?, ?, ?)");
    return $stmt->execute([$title, $content, $categoryId, $authorId]);
}

新闻查询功能实现：

function getNews($limit = 10, $offset = 0) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT n.*, c.name as category_name 
                          FROM news n 
                          LEFT JOIN categories c ON n.category_id = c.id 
                          ORDER BY n.publish_date DESC 
                          LIMIT ? OFFSET ?");
    $stmt->execute([$limit, $offset]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

分类管理

添加分类功能：

function addCategory($name, $slug) {
    global $pdo;
    $stmt = $pdo->prepare("INSERT INTO categories (name, slug) VALUES (?, ?)");
    return $stmt->execute([$name, $slug]);
}

获取所有分类：

function getAllCategories() {
    global $pdo;
    $stmt = $pdo->query("SELECT * FROM categories ORDER BY name");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

用户权限控制

简单的权限检查示例：

function checkPermission($userId, $requiredRole) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT role FROM users WHERE id = ?");
    $stmt->execute([$userId]);
    $user = $stmt->fetch();
    return ($user && $user['role'] === $requiredRole);
}

前端展示

新闻列表页示例：

$newsList = getNews();
foreach ($newsList as $news) {
    echo "<article>
            <h3>{$news['title']}</h3>
            <p class='category'>{$news['category_name']}</p>
            <div class='content'>".substr($news['content'], 0, 200)."...</div>
            <a href='news_detail.php?id={$news['id']}'>Read more</a>
          </article>";
}

安全措施

防止SQL注入：

$stmt = $pdo->prepare("SELECT * FROM news WHERE id = ?");
$stmt->execute([$_GET['id']]);

XSS防护：

function safeOutput($string) {
    return htmlspecialchars($string, ENT_QUOTES, 'UTF-8');
}