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

score 6 · Accepted Answer · edited Aug 21 '11 at 22:34

6

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

edited Aug 21 '11 at 22:34

Paŭlo Ebermann

73,284
20
146
210

answered Jan 27 '10 at 22:19

dev-null-dweller

29,274
3
65
85

score 1 · Answer 2 · answered Aug 08 '12 at 09:45

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: enter image description here

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/

score 1 · Answer 3 · answered Jan 27 '10 at 21:48

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.

Scalable Solution For Threaded Comments

3 Answers3