phpmysqlthreaded-comments

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

Solution

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