phpmysqlmultithreadingnestedthreaded-comments

PHP MYSQL - Displaying multi threaded comments not working


This is what my Database looks like

comments

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. 

Replies

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.


The issue is, i want the comments to display nested like the following:

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.


Solution

  • 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 .= '-';
        }
    }