使用MySQL和PHP检索每个注释的五个响应

Question

I'm trying to loop through my MySQL database and retrieve five responses for each comment. The output should be as following

1. First comment
   • First response
   • Second response
   • ...
   • Fifth Response
2. Second comment
   • First response
   • Second response
   • ...
   • Fifth response
3. ...

in order to achieve the above, I'm using the following SQL statement and PHP snippet

$query = $db->prepare("
    SELECT c.id, c.user_update, r.id reply_id, r.response,
    FROM comments AS c
    LEFT JOIN (SELECT * FROM responses LIMIT 5) AS r ON (r.reply_id = c.id)
");

try {
    $query->execute();              
    $comments = array();
    $comments_replies = array();

    while($row = $query->fetch(PDO::FETCH_ASSOC)) {
        $comm_id = $row['id'];
        $comments[$comm_id] = $row;
        $comments_replies[$comm_id][] = $row;
    }

    foreach ($comments as $comm_id => $row) {
        echo "<b>".$row['comment']."</b></br></br>";
        foreach ($comments_replies[$comm_id] as $reply_id => $row) {    
            echo $row['response']."</br></br>";
        }
    }
} catch (PDOException $e) {
    echo $e->getMessage();
    exit();
}

but it doesn't work so I'm trying to find out what should I change to make it work - to retrieve five responses for each comment and output them as shown above.

Answer 1

Table structure

+----------------+           +-------------------+
| comments       |           | responses         |
+----------------+           +-------------------+
| id      (int)  | [PK] --|  | id         (int)  | [PK]
| comment (text) |        |--| comment_id (int)  | [FK]     
+----------------+           | response   (text) |   
                             +-------------------+

SQL Statement - Select five responses for each comment

SET @num := 0, @group := '';
SELECT id, comment_id, response
FROM (SELECT id, comment_id, response,
       @num := IF(@group = comment_id, @num + 1, 1) AS row,
       @group := comment_id AS dummy
  FROM responses
  ORDER BY id, comment_id) AS x
WHERE row <= 5

If you want only the last five, here's the modified statement

SET @num := 0, @group := '';
SELECT id, comment_id, response
FROM (SELECT id, comment_id, response,
       @num := IF(@group = comment_id, @num + 1, 1) AS row,
       @group := comment_id AS dummy
  FROM responses
  ORDER BY id DESC, comment_id) AS x
WHERE row <= 5

---

// Basic PHP implementation of the SQL statement above

$db = new PDO('mysql:host=localhost;dbname=database;charset=utf8', 'user', 'password');

try{
    $db->query("SET @num := 0, @group := '';");
    $query = "
        SELECT comment_id, comment, id, response
        FROM (SELECT r.comment_id, c.comment, r.id, r.response,
                @num := IF(@group = r.comment_id, @num + 1, 1) AS row,
                @group := r.comment_id AS dummy
            FROM responses AS r
            JOIN comments AS c ON c.id = r.comment_id
            ORDER by r.id, r.comment_id) as x
        WHERE row <= 5
    ";

    $current_comment = 0;
    foreach($db->query($query) as $row) {
        if($row['comment_id'] != $current_comment){
            echo '<b>' . $row['id'] .' '. $row['comment'] . '</b><br />';
            $current_comment = $row['comment_id'];
        }
        echo $row['id'] .' '. $row['response'] . "<br />";
    }

}catch(PDOException $ex){
    echo "An Error occured!";
}

Check the SQL Fiddle