文章关联多分类后重复显示的数据库查询与前端展示问题

我需要实现单篇文章关联多个分类的功能，但遇到了以下问题：添加关联2个分类的文章时，数据库posts表仅存储1条文章数据，postcategories中间表也正确关联了2个分类，但在view_all_posts.php中使用INNER JOIN关联三张表查询并循环展示时，同一文章会按分类数量重复显示，无法在单行内展示该文章的多个分类。

已完成的操作：

• 创建posts、categories、postcategories（中间表）三张数据库表；
• 在add_post.php中编写逻辑：先插入文章数据到posts表，再循环插入关联数据到postcategories表；
• 在view_all_posts.php中通过INNER JOIN关联三张表查询数据并循环渲染表格。

附相关代码 #

add_post.php #

<?php
if(isset($_POST['create_post'])){
    $post_title       = escape($_POST['post_title']);
    $post_author      = escape($_POST['post_author']);
    $post_category_id = $_POST['post_category'];
    $post_status      = escape($_POST['post_status']);

    $post_image = $_FILES['image']['name'];
    $post_image_temp = $_FILES['image']['tmp_name'];
    $data= explode('.', $post_image );
    $extension= end($data);
    $post_image = time() . '.' . $extension;

    $post_tags        = escape($_POST['post_tags']);
    $post_content     = escape($_POST['post_content']);
    $post_date        = escape(date('d-m-y'));

    $post_image = time();
    move_uploaded_file($post_image_temp, "../assets/img/blog/" . $post_image);

    if(!empty($_POST['post_category'])) {
        $query = "INSERT INTO posts
                (post_title, post_author, post_date, 
                post_image, post_content, post_tags, post_status)
                VALUES(?,?,CURDATE(),?,?,?,?)";

        $stmt = $connection->prepare($query);
        $stmt->bind_param('ssssss',
                $_POST['post_title'],
                $_POST['post_author'],
                $post_image,
                $_POST['post_content'],
                $_POST['post_tags'],
                $_POST['post_status']);
        if ( $stmt->execute() ) {
            $the_post_id = $connection->insert_id;
        }

        $sql = 'INSERT INTO postcategories (post_id, cat_id) VALUES(?,?)';
        $stmt = $connection->prepare($sql);

        foreach( $_POST['post_category'] as $cat_id){
                $stmt->bind_param('ii', $the_post_id, $cat_id);
                $stmt->execute();            
        }

        echo "<h4 class='bg-success text-center'>Post Created: <a href='../post.php?p_id={$the_post_id}'>View Post</a> or <a href='posts.php'>View All Posts</a></h4>";
    }
}
?>

view_all_posts.php #

<?php
$query = "SELECT posts.post_id, posts.post_author, posts.post_title, posts.post_status,posts.post_image, posts.post_tags, ";
$query .= "posts.post_comment_count, posts.post_date, categories.cat_id, categories.cat_title, postcategories.post_id, postcategories.cat_id ";
$query .= "FROM postcategories ";
$query .= "INNER JOIN categories USING (cat_id) ";
$query .= "INNER JOIN posts USING (post_id) ";
$query .= "ORDER BY posts.post_id DESC";

$select_posts = mysqli_query($connection, $query);

while($row = mysqli_fetch_assoc($select_posts)){
    $post_id            = escape($row['post_id']);
    $post_author        = escape($row['post_author']);
    $post_title         = escape($row['post_title']);
    $post_status        = escape($row['post_status']);
    $post_image         = escape($row['post_image']);
    $post_tags          = escape($row['post_tags']);
    $post_comment_count = escape($row['post_comment_count']);
    $post_date          = escape($row['post_date']);
    $category_title     = escape($row['cat_title']);
    $category_id        = escape($row['cat_id']);

    echo "<tr>";
?>
<td><input class='checkBoxes' type='checkbox' name='checkBoxArray[]' value='<?php echo $post_id; ?>'></td>
<?php
    echo "<td>$post_id</td>";
    echo "<td>$post_author</td>";
    echo "<td>$post_title</td>";

    $query = "SELECT * FROM postcategories WHERE cat_id = {$category_id} && post_id = {$post_id} ";
    $select_categories_id = mysqli_query($connection, $query);
    while($row = mysqli_fetch_assoc($select_categories_id)) {
        $category_id = escape($row['cat_id']);
        echo "<td>{$category_title}</td>";
    }

    echo "<td>$post_status</td>";
    echo "<td><img src='../assets/img/blog/$post_image' alt='$post_title' width=100px></td>";
    echo "<td>$post_tags</td>";
    echo "<td>$post_comment_count</td>";
    echo "<td>$post_date</td>";
    echo "<td><a class='btn btn-primary' href='../blog-details.php?p_id={$post_id}'>View Post</a></td>";
    echo "<td><a class='btn btn-info' href='posts.php?source=edit_post&p_id={$post_id}'>Edit</a></td>";
?>
<form method="post">
<input type="hidden" name="post_id" value="<?php echo $post_id; ?>">
<?php
    echo "<td><input class='btn btn-danger' type='submit' name='delete' value='Delete' onClick=\"javascript: return confirm('Are you sure you want to delete?');\"></td>";
?>
</form>
<?php
    echo "</tr>";
}
?>

方案1：SQL层面用GROUP_CONCAT合并分类 #

直接修改查询语句，通过分组和字符串合并，把同一文章的所有分类标题合并成一个字段，避免重复行：

$query = "SELECT posts.post_id, posts.post_author, posts.post_title, posts.post_status, posts.post_image, posts.post_tags, 
posts.post_comment_count, posts.post_date, GROUP_CONCAT(categories.cat_title SEPARATOR ', ') AS category_titles
FROM posts
LEFT JOIN postcategories USING (post_id)
LEFT JOIN categories USING (cat_id)
GROUP BY posts.post_id
ORDER BY posts.post_id DESC";

然后在循环渲染时，替换原嵌套查询的部分，直接输出合并后的分类：

// 替换原嵌套查询的代码块
echo "<td>{$category_titles}</td>";

方案2：PHP层面分组处理数据 #

先把查询结果按文章ID分组，收集同一文章的所有分类后再统一渲染：

// 先获取所有数据并按post_id分组
$posts_data = [];
while($row = mysqli_fetch_assoc($select_posts)){
    $post_id = escape($row['post_id']);
    if(!isset($posts_data[$post_id])){
        // 初始化文章基础数据
        $posts_data[$post_id] = [
            'post_author' => escape($row['post_author']),
            'post_title' => escape($row['post_title']),
            'post_status' => escape($row['post_status']),
            'post_image' => escape($row['post_image']),
            'post_tags' => escape($row['post_tags']),
            'post_comment_count' => escape($row['post_comment_count']),
            'post_date' => escape($row['post_date']),
            'categories' => []
        ];
    }
    // 将分类添加到对应文章的分类列表
    $posts_data[$post_id]['categories'][] = escape($row['cat_title']);
}

// 循环分组后的数据渲染表格
foreach($posts_data as $post_id => $post){
    echo "<tr>";
?>
<td><input class='checkBoxes' type='checkbox' name='checkBoxArray[]' value='<?php echo $post_id; ?>'></td>
<?php
    echo "<td>$post_id</td>";
    echo "<td>{$post['post_author']}</td>";
    echo "<td>{$post['post_title']}</td>";
    // 用逗号分隔输出所有分类
    echo "<td>" . implode(', ', $post['categories']) . "</td>";
    echo "<td>{$post['post_status']}</td>";
    echo "<td><img src='../assets/img/blog/{$post['post_image']}' alt='{$post['post_title']}' width=100px></td>";
    echo "<td>{$post['post_tags']}</td>";
    echo "<td>{$post['post_comment_count']}</td>";
    echo "<td>{$post['post_date']}</td>";
    echo "<td><a class='btn btn-primary' href='../blog-details.php?p_id={$post_id}'>View Post</a></td>";
    echo "<td><a class='btn btn-info' href='posts.php?source=edit_post&p_id={$post_id}'>Edit</a></td>";
?>
<form method="post">
<input type="hidden" name="post_id" value="<?php echo $post_id; ?>">
<?php
    echo "<td><input class='btn btn-danger' type='submit' name='delete' value='Delete' onClick=\"javascript: return confirm('Are you sure you want to delete?');\"></td>";
?>
</form>
<?php
    echo "</tr>";
}

额外说明 #

原查询使用INNER JOIN从postcategories表开始，会导致没有关联分类的文章无法被查询到，建议改为LEFT JOIN确保所有文章都能显示。

内容的提问来源于stack exchange，提问作者Sofiane Abou Abderrahim