如何在PHP+MySQL中实现分类页同时显示分类信息及对应文章列表

问题背景 #

我在使用GitHub上的CMS-BLOG作为网站后端，这是一个基础的分类与文章管理系统。当前点击分类名称后会进入对应文章列表页，希望在该页面同时展示该分类的名称、描述（后续可能还会加图片）。比如把艺术家设为分类，文章列表是其画作，要在同一页呈现。

数据库表结构 #

文章表 admin_panel #

CREATE TABLE `admin_panel` (
`id` int(10) NOT NULL,
`datetime` varchar(50) NOT NULL,
`title` varchar(200) NOT NULL,
`category` varchar(100) NOT NULL,
`author` varchar(100) NOT NULL,
`image` varchar(200) NOT NULL,
`post` varchar(10000) NOT NULL
);

分类表 category #

CREATE TABLE `category` (
`id` int(10) NOT NULL,
`name` varchar(100) NOT NULL,
`description` varchar(200) NOT NULL,
`creatorname` varchar(200) NOT NULL
);

修改方案 #

1. 核心查询语句优化 #

当用户访问分类列表页时，先从category表获取分类详情，再查询该分类下的文章。必须使用预处理语句避免SQL注入风险，原代码直接拼接用户输入存在安全漏洞。

获取分类信息的预处理查询：

// 处理分类参数
if(isset($_GET['category'])) {
    $category = $_GET['category'];
    // 预处理查询分类名称、描述
    $categoryQuery = "SELECT name, description FROM category WHERE name = ?";
    $stmt = mysqli_prepare($connection, $categoryQuery);
    mysqli_stmt_bind_param($stmt, "s", $category);
    mysqli_stmt_execute($stmt);
    $categoryResult = mysqli_stmt_get_result($stmt);
    $categoryData = mysqli_fetch_assoc($categoryResult);
}

查询分类下文章的预处理语句：

// 预处理查询该分类下的文章列表
$postQuery = "SELECT * FROM admin_panel WHERE category = ? ORDER BY id DESC";
$stmtPost = mysqli_prepare($connection, $postQuery);
mysqli_stmt_bind_param($stmtPost, "s", $category);
mysqli_stmt_execute($stmtPost);
$execute = mysqli_stmt_get_result($stmtPost);

2. 前端代码修改 #

在文章列表渲染前，先输出分类的名称和描述，位置可以放在页面顶部：

<!-- 输出分类信息（仅分类页面显示） -->
<?php if(isset($categoryData)): ?>
<div class="category-header">
    <h2><?php echo htmlentities($categoryData['name']); ?></h2>
    <p class="category-desc"><?php echo htmlentities($categoryData['description']); ?></p>
    <!-- 后续添加分类图片时，可增加以下代码：
    <img src="分类图片存储路径/<?php echo $categoryData['image']; ?>" alt="<?php echo htmlentities($categoryData['name']); ?>" class="category-img">
    -->
</div>
<?php endif; ?>

3. 完整的blog.php代码片段（替换原分类分支逻辑） #

<?php
global $connection;

$categoryData = null; // 初始化分类数据变量

if(isset($_GET['Submit'])) {
    // 原搜索逻辑，建议同步改为预处理语句
    $search = $_GET['Search'];
    $ViewQuery = "select * from admin_panel
        where datetime like '%$search%' or title like '%$search%' or category like '%$search%'
        or author like '%$search%' or post like '%$search%' order by id desc";
    $execute = mysqli_query($connection, $ViewQuery);
} else if(isset($_GET['page'])) {
    // 原分页逻辑，建议同步改为预处理语句
    $pageNumber = $_GET['page'];
    $showPostFrom = $pageNumber*5-5;

    $queryPagination = "select count(*) from admin_panel";
    $executePagination = mysqli_query($connection, $queryPagination);
    $rowPagination = mysqli_fetch_array($executePagination);
    $totalPost = array_shift($rowPagination);
    $postPerPage = ceil($totalPost/5);

    if($pageNumber <= 0) {
        $showPostFrom = 0;
        $pageNumber = 1;
    } else if($pageNumber > $postPerPage) {
        $pageNumber = $pageNumber - 1;
        $showPostFrom = $pageNumber*5-5;
    }

    $ViewQuery = "select * from admin_panel order by id desc limit $showPostFrom, 5";
    $execute = mysqli_query($connection, $ViewQuery);

} else if(isset($_GET['category'])) {
    $category = $_GET['category'];
    
    // 1. 查询分类详情
    $categoryQuery = "SELECT name, description FROM category WHERE name = ?";
    $stmt = mysqli_prepare($connection, $categoryQuery);
    mysqli_stmt_bind_param($stmt, "s", $category);
    mysqli_stmt_execute($stmt);
    $categoryResult = mysqli_stmt_get_result($stmt);
    $categoryData = mysqli_fetch_assoc($categoryResult);

    // 2. 查询该分类下的文章
    $postQuery = "SELECT * FROM admin_panel WHERE category = ? ORDER BY id DESC";
    $stmtPost = mysqli_prepare($connection, $postQuery);
    mysqli_stmt_bind_param($stmtPost, "s", $category);
    mysqli_stmt_execute($stmtPost);
    $execute = mysqli_stmt_get_result($stmtPost);

} else {
    $ViewQuery = "select * from admin_panel order by id desc limit 0,5";
    $execute = mysqli_query($connection, $ViewQuery);
}
?>

<!-- 分类信息展示区 -->
<?php if($categoryData): ?>
<div class="category-banner">
    <h1><?php echo htmlentities($categoryData['name']); ?></h1>
    <p><?php echo htmlentities($categoryData['description']); ?></p>
</div>
<?php endif; ?>

<!-- 文章列表循环 -->
<?php while($DataRows = mysqli_fetch_array($execute)) {
    $id = $DataRows["id"];
    $datetime = $DataRows["datetime"];
    $title = $DataRows["title"];
    $category = $DataRows["category"];
    $author = $DataRows["author"];
    $image = $DataRows["image"];
    $post = $DataRows["post"];
?>
<div class="blogpost img-thumbnail">
    <img src="upload/<?php echo htmlentities($image); ?>" class="img-fluid mx-auto d-block" alt="Responsive image"/>
    <div class="caption">
        <h3 id="heading"><?php echo htmlentities($title); ?></h3>
        <p class="description">Category:<?php echo htmlentities($category); ?><span style="margin-left:5px; float:right;">Published on:
            <?php echo htmlentities($datetime);?></span>
        </p>
        <p class="post"><?php 
            if(strlen($post) > 150) {
                $post = substr($post, 0, 150)."...";
            }
            echo htmlentities($post);
        ?></p>
    </div>
</div>
<?php } ?>

关键注意事项 #

• 原代码存在SQL注入风险，所有直接拼接用户输入的SQL语句都应改为预处理语句，上述代码仅修复了分类分支，建议搜索、分页逻辑同步修改。
• 若后续需要添加分类图片，只需在category表新增image字段，再在查询和前端输出部分对应添加代码即可。
• 可以给分类信息的HTML元素添加自定义CSS类（如category-banner），方便设置背景、内边距等样式优化展示效果。

内容的提问来源于stack exchange，提问作者Trevor