Scalable Solution For Threaded Comments

Question

I'm not sure how to create a threaded comments system in PHP and MySQL which can handle hundreds of comments at a time.

Something like this is the only thing I can come up with

$query = execute_query...('SELECT * FROM `comments` WHERE `post` = "1"');
foreach($query as $comment){
    $replies = execute_query...('SELECT * FROM `comment_replies` WHERE `comment` = "' . $comment['id'] . '"');
    if($replies){
        echo $comment['body']; //....
        foreach($replies as $reply){ /*....*/ }
    }
    else{
        echo $comment['body'];
    }
}

So I need tips on database structure and how I can retrive the all the threaded comments with performance in mind please :)

Answer 1

Why not join the comments and the comment_replies table?

then just add a extra generated field that shows if its a comment or a comment_reply. And select them with a if in a foreach like:

if($type == 'comment') 
{
      //do something with the comment
}
elseif($type == 'comment_reply')
{
      //do something with the comment reply
}

Also check if the comment id changes so you can seperate them.

Answer 2

I'm sure you'll find this article helpful. Those two tables can be easily merged into one