PHP multithreaded comments?

Question

I have a script I wrote I when I return to the comments, but it is only single-threaded. I would like it to be multi-threaded, but only so that the user can respond to the comment, and not so that the user can respond to the comment of the comment. Thus, the flows will be only two deep.

I am currently saving comment_id compared to user_id in my database.

The only way I can come up with multi-threaded comments is to have a parent field in the comments table. But if I do, then when I select comments using PHP, I will need to do another SELECT command to select comments for children (if any) for each comment. It seems like most of the work with the database.

There must be a better way. Any ideas on this? Or textbooks?

Answer 1

There are three (four) alternative options:

• A recursive query to select all comments based on their parent identifiers. This is supported by many database products, and the syntax depends on the type of database. Check documents for more information (search for "recursive").

• If you store the article id in each (sub) comment, you can simply select all comments with the article id in one regular selection request. You can use parent identifiers to display comments correctly on the page under the correct parent comment:

   SELECT * FROM comments WHERE article_id = :article_id 

• If you need only two levels of comments, you can use advanced where , to include comments of the first level and second level:

   SELECT * FROM comments WHERE parent_id = :article_id OR parent_id IN (SELECT id FROM comments WHERE parent_id = :article_id) 

• You can also use union all to combine two queries with the same columns, but since I assume that all the data is taken from the same table, there is probably no need (see the extended where clause):

   SELECT * FROM comments WHERE parent_id = :article_id UNION ALL SELECT * FROM comments WHERE parent_id IN (SELECT id FROM comments WHERE parent_id = :article_id) 

Personally, I would go for option 2 because it is simple (no exotic SQL construct is required), efficient (1 query) and flexible (supports as many levels of comments as you like).

Answer 2

1 is enough for this, you just need to encode the data and save it in the array correctly, then you loop the array and display the comments.

Answer 3

This is a common use for hierarchical or tree data. I wrote a popular answer to this stack overflow question: What is the most efficient / elegant way to parse a flat table into a tree?

I also wrote a presentation describing alternatives for tree data: Models for hierarchical data with SQL and PHP .

Another solution that is not included in my presentation is the way Slashdot executes comment threads. They use the parent column, just like you do, so each comment refers to a comment to which it responds. But then they also include the root column, so every comment knows which message it belongs to. There are rarely hundreds of comments in this post, and usually you want to get the entire comment tree for this post, starting at the top of the comment stream:

 SELECT * FROM comments WHERE root = 1234; 

Then, when you extract the comments, you can write the PHP code to process them into arrays of arrays according to the parent columns (this is what the @ JanL answer refers to). I wrote code to do this in answer to another question, Convert a flat array to multidimensional .

Answer 4

This query may work for you (I did not know your structure, so I figured it out):

 SELECT * FROM comments a LEFT JOIN comments b ON a.comment_id = b.parent_coment_id LEFT JOIN comments c ON b.comment_id = c.parent_coment_id WHERE a.comment_id <> b.comment_id AND a.comment_id <> c.comment_id AND b.comment_id <> c.comment_id;