I'm trying to loop through my MySQL database and retrieve five responses for each comment. The output should be as following
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.
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