How to build Threaded comments with a 1 or 2 queri

Question

Can anyone suggest a creative database structure + fetching algorithm for a threaded comments system, that would output x amount of threads per page (with unlimited replies for each)?

I can run a query to get the threads, and in each instance of a loop, run another query to echo out the replies.... but that's a bad idea.

Answer 1

If you need only 2 levels, here's a way with one query:

Your table - id, parent_id, comment columns

Code

$rows = mysql_query('
  select *
  FROM
    comments
  ORDER BY
    id DESC');

$threads = array();
foreach($rows as $row) {
  if($row['parent_id'] === '0') {
    $threads[$row['id']] = array(
      'comment' => $row['comment'],
      'replies' => array()
    );
  } else {
    $threads[$row['parent_id']]['replies'][] = $row['comment'];
  }
}

In $threads you will have all your main threads and $threads[$id]['replies'] holds all replies. The threads are sorted - latest = first, add some paging and you're good to go.

Answer 2

Add two columns to the comment table: parentCommentId and rootCommentId.

parentCommentId is the id of the parent comment, and rootCommentId is the id of the comment that started this thread.

To display N threads, you'll need two queries:

1. Get N rows from the comment table where rootCommentId = id
2. Get all comments for these N threads

(You can combine these two into a single GroupBy query.)

Answer 3

This is similar to something I am using now. The only tricky part is calculating the next reply path to insert when somebody replies to a comment.

The Example Data

ID | Comment                      | Path
---+------------------------------+----------
0  | Comment #1                   | 01
1  | Comment #1 reply             | 01_01
2  | Comment #1 reply reply       | 01_01_01
3  | Comment #1 reply reply       | 01_01_02
4  | Comment #2                   | 02
5  | Comment #3                   | 03
6  | Comment #3 reply             | 03_01

The Example SQL

SELECT * FROM comments ORDER BY path

The Example PHP

while ($result = mysql_fetch_assoc($query)) {
    $nesting_depth = count(explode("_", $result['path']));
    $branch = str_repeat("--", $nesting_depth);
    echo $branch {$result['comment']}";
}

The Example Result

Comment #1
-- Comment #1 reply
---- Comment #1 reply reply
---- Comment #1 reply reply
Comment #2
Comment #3
-- Comment #3 reply

To make a reply to 01_01

SELECT path FROM comments WHERE path LIKE '01\_01\___'

$last_path = $row[0];
$last_path_suffix = substr($last_path,strrpos($last_path,'_')+1);
$next_path_suffix = str_pad($last_path_suffix+1,2,'0',STR_PAD_LEFT);
$next_path = substr($last_path,0,strlen($last_path)-strlen($last_path_suffix)).$next_path_suffix;