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/
精彩评论