This is what my Database looks like
id 1 - (primary key)
user_id 2
comment - I need help
id 2 - (primary key)
user_id
comment - I'm going to sleep now
id 3 - (primary key)
user_id 2
comment - Watching the news.
id 1
comment_id 1 (foreign key)
reply - what do you need help with?
id 2
comment_id 1 (foreign key)
reply - i can help you!
id 3
comment_id 1 (foreign key)
reply - i don't understand!
id 4
comment_id 2 (foreign key)
reply - OK have a good night.
comment - I need help
--- reply - what do you need help with?
---- i can help you!
----- i don't understand!
comment - I'm going to sleep now
--- reply - OK have a good night.
comment - Watching the news.
--- no reply
Not like this, which is what i'm getting at the moment.
comment - I need help
--- reply - what do you need help with?
comment - I need help
--- i can help you!
comment - I need help
---- i don't understand!
comment - I'm going to sleep now
--- reply - OK have a good night.
comment - Watching the news.
--- no reply
This is my Query
$query = $db->prepare("SELECT
comments.id,
comments.user_id,
comments.comment,
replies.id,
replies.comment_id,
replies.reply
FROM comments
LEFT JOIN replies
ON comments.id = replies.comment_id
WHERE comments.user_id = ? ORDER BY comments.id DESC");
try {
$query->execute();
while($row = $query->fetch(PDO::FETCH_ASSOC)) {
$comm_id = $row['id'];
$comm_user_id = $row['user_id'];
$comm = $row['comment'];
$re_id = $row['id'];
$re_comm_id = $row['comment_id'];
$re_reply = $row['reply'];
echo $comm." - ".$re_reply;
}
} catch (PDOException $e) {
echo $e->getMessage();
exit();
}
The issue is obviously in the query structure, but i'm not sure how to go about structuring it so i can get the desired result.
I hope i explained my self clearly enough, Thanks for any help in advance.
First change the query so that replies.id
has an alias so we can reference later:
SELECT comments.id, comments.user_id, comments.comment,
replies.id reply_id, replies.comment_id, replies.reply
FROM comments
LEFT JOIN replies
ON comments.id = replies.comment_id
WHERE comments.user_id = ? ORDER BY comments.id DESC
You can group the replies by comment_id first.
<?php
$comments = array();
$comm_replies = array();
while($row = $query->fetch(PDO::FETCH_ASSOC)) {
$comm_id = $row['id'];
$comm_user_id = $row['user_id'];
$comm = $row['comment'];
if (empty($row['reply_id'])) {
continue;
}
$comments[$comm_id] = $comm;
$comm_replies[$comm_id][] = $row;
}
foreach ($comments as $comm_id => $comm) {
echo "comment - $comm\n";
if (!isset($comm_replies[$comm_id])) {
continue;
}
$prefix = '---';
foreach ($comm_replies[$comm_id] as $reply_id => $row) {
echo "$prefix $row['reply_id'], $row['reply']\n";
$prefix .= '-';
}
}