Scalable Solution For Threaded Comments

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 :)

I'm sure you'll find the article Managing Hierarchical Data in MySQL helpful. Those two tables can be easily merged into one

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.

I would choose Adjacency List Model over Nested Set Model because with Nested Set Model, I would have to build the whole tree structure on every INSERT and DELETE operation done on the table and if I have 1000′s of records, for a single INSERT or DELETE operation all the records has to be updated taking more time to execute. My database structure would be:

Also I have written a small threaded comment system with php mysql jquery and ajax. Take a look at http://pradipchitrakar.com.np/programming/threaded-comment-system/