PHP MySQL帖子与评论一对多关联的SQL查询语句编写咨询

Got it, let's break this down step by step since you already have the one-to-many relationship sorted (posts have many comments, comments belong to one post). First, let's assume your table structures look like this (super common setup for this use case):

-- Posts table (parent)
CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Comments table (child, linked to posts via foreign key)
CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    author VARCHAR(100) NOT NULL,
    content TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
);

1. Fetch all posts with their associated comments (including posts with no comments) #

Use a LEFT JOIN here so posts without any comments still show up in your results (replace with INNER JOIN if you only want posts that have at least one comment):

SELECT 
    p.id AS post_id,
    p.title AS post_title,
    p.content AS post_content,
    p.created_at AS post_created,
    c.id AS comment_id,
    c.author AS comment_author,
    c.content AS comment_content,
    c.created_at AS comment_created
FROM posts p
LEFT JOIN comments c ON p.id = c.post_id
ORDER BY p.created_at DESC, c.created_at DESC;

2. Fetch a single post and all its comments #

If you're loading a specific post (e.g., from a ?post_id=X URL parameter), you can filter with a WHERE clause:

SELECT 
    p.id AS post_id,
    p.title AS post_title,
    p.content AS post_content,
    c.id AS comment_id,
    c.author AS comment_author,
    c.content AS comment_content,
    c.created_at AS comment_created
FROM posts p
INNER JOIN comments c ON p.id = c.post_id
WHERE p.id = ? -- Replace ? with your post ID (use prepared statements in PHP!)
ORDER BY c.created_at DESC;

3. Handling results in PHP (to group comments under their posts) #

Your SQL query will return a row for each comment + post combination. To display posts with all their comments grouped together, you can restructure the data in PHP like this:

// Assume $pdo is your database connection
$stmt = $pdo->prepare("SELECT ..."); // Use the first query from above
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Group posts and comments
$posts = [];
foreach ($results as $row) {
    $postId = $row['post_id'];
    // If we haven't added this post to the array yet, initialize it
    if (!isset($posts[$postId])) {
        $posts[$postId] = [
            'id' => $row['post_id'],
            'title' => $row['post_title'],
            'content' => $row['post_content'],
            'created_at' => $row['post_created'],
            'comments' => []
        ];
    }
    // Add the comment if it exists (LEFT JOIN might return NULL for comment fields)
    if ($row['comment_id'] !== null) {
        $posts[$postId]['comments'][] = [
            'id' => $row['comment_id'],
            'author' => $row['comment_author'],
            'content' => $row['comment_content'],
            'created_at' => $row['comment_created']
        ];
    }
}

// Now $posts is an array where each entry has a 'comments' sub-array ready for display

Bonus: Simplified query with GROUP_CONCAT (for basic displays) #

If you just want to show a comma-separated list of comments per post (not ideal for full comment display, but useful for previews), you can use GROUP_CONCAT:

SELECT 
    p.id,
    p.title,
    p.content,
    GROUP_CONCAT(CONCAT(c.author, ': ', c.content) SEPARATOR '<br>') AS comments
FROM posts p
LEFT JOIN comments c ON p.id = c.post_id
GROUP BY p.id
ORDER BY p.created_at DESC;

Note: This has limitations (like character length limits for GROUP_CONCAT), so stick with the first approach for full-featured comment displays.

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