Multi Threaded Comments PHP?

Question

I have a script I wrote I while back for comments, but it is only single threaded. I would like it to be multi-threaded, but only as so a user can reply to a comment, not so a user can reply to a comment of a comment. So the threads would only be two deep.

Currently I store a comment_id against a user_id in my database.

The only way I can think of to do the multi threaded comments, is to have a parent field in the comments table. But if I do this then when I am selecting the comments with PHP, I will have to do another SELECT command to select the comments children (if any) for each comment. Seems like a lot of work on the database.

There has to be a better way. Any ideas on this? Or tutorials?

Without reading the question for context the question made absolutely no sense since "multi-threaded" and "comments" generally refer to some else. — Davy8, Dec 11 '11 at 22:01

The Nail · Accepted Answer · 2011-12-11T21:57:38.113

There are three (four) alternative possibilities:

A recursive query to select all comments based on their parent ids. This is supported by many database products and the syntax depends on the database type. Check the docs for more info (search for 'recursive').
If you store the article id in each (sub-)comment, you can just select all comments with the article id in one regular select query. You can use the parent ids to properly display the comments on the page under the right parent comment:
```
SELECT * FROM comments WHERE article_id = :article_id
```

If you only need two levels of comments, you can use an extended where to include both first level and second level comments:

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

It is also possible to use union all to combine two queries that have the same columns, but since I assume that all data are from the same table, there is probably no need for it (see the extended where-clause above):
```
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 required), efficient (1 query) and flexible (supports as many levels of comments as you like).

Please could you drop a real life example of the php code and result-set...i have been reading your answer over and over for hours and i cant really figure how to code it...thanks — jcobhams, Feb 19 '14 at 12:57
@VyrenMedia I assume you are trying to implement option 2. I will not provide you with a full implementation guide, but maybe this will help: http://blog.tcs.de/creating-trees-from-sql-queries-in-javascript/ . If you need more help please post a new SO question. — The Nail, Feb 22 '14 at 16:15

score 2 · Answer 2 · answered Dec 05 '11 at 20:16

2

1 query is enough for this, you just need to loop the data and store it into an array correctly, then you loop the array and display the comments.

answered Dec 05 '11 at 20:16

JanLikar

1,296
9
22

Which is more than one query! This is how I exaplain I would have done it anyway, I'm looking for better ways – Sean H Jenkins Dec 05 '11 at 20:22
You were talking about using another SELECT statement and I gave you a better way. – JanLikar Dec 05 '11 at 20:23
No your solution will still use multiple SELECT. Thanks for the answer. – Sean H Jenkins Dec 05 '11 at 20:26
1

If you store the article id for each comment, you need exactly **one** SELECT statement, namely: `SELECT * FROM comment WHERE article_id = :article_id` – The Nail Dec 05 '11 at 20:29
1

My solution uses 1 and no more than 1 queries. – JanLikar Dec 05 '11 at 20:30

score 2 · Answer 3 · edited May 23 '17 at 11:54

2

This is a common use for hierarchical, or tree-structure 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-structured data: Models for Hierarchical Data with SQL and PHP.

Another solution that is not included in my presentation is the way Slashdot does threaded comments. They use a parent column like you do, so each comment references the comment it replies to. But then they also include a root column so each comment knows the post it belongs to. There are seldom more than a few hundred comments on a given post, and usually you want to get the whole tree of comments for a given post starting at the top of the comment thread:

SELECT * FROM comments WHERE root = 1234;

Then as you fetch the comments, you can write PHP code to process them into arrays of arrays according to the parent columns (this is what @JanL's answer alluded to). I posted code to do this in my answer to another Stack Overflow question, Convert flat array to the multi-dimentional.

edited May 23 '17 at 11:54

Community

1
1

answered Dec 05 '11 at 20:59

Bill Karwin

538,548
86
673
828

I know this is an old post but I was wandering with the method you have described how this would work if you had a post with comments in the thousands, you obviously wouldnt select all rows, however if you used a LIMIT you would lose the tree structure... is there a way around this?... Kind regards J – jon Jun 16 '12 at 20:36
@jon, you could use LIMIT but order by the date of the comment. So you'd get the oldest comments threads. Then if the user wants to expand threads they could request it, but by default they'd only see a subset of the discussion. If you want more details, download the Slashdot source and see how they do it. http://slashcode.com/ – Bill Karwin Jun 17 '12 at 03:30
thanks for your response, however if I am understanding you correctly, this would only show the original comment threads and then if they wanted to expand a comment thread to see any replies you would then do a select on that comment thread, however I am really wanting to also show the replies of the comment threads in the first query, but also be able to LIMIT?.. is that possible using the adjacency hierarchy?.. thanks again J. – jon Jun 17 '12 at 12:51
@jon, no, it's not possible in adjacency hierarchy in one query if you use MySQL and you want to support arbitrary depth of comment reply threads. That's why people have come up with alternative ways of storing hierarchical data (see my presentation I linked to). Other databases support recursive queries with common table expressions, so it may be possible. – Bill Karwin Jun 17 '12 at 16:48
thanks for your continued help... I have taken a look at your article linked above, and of what I understood (I'm still learning!), although they are really clever ways of storing heirachical data, the adjacency methods seems like the best method for storing something like comments with replies... in that the other techniques would require very complex updates when a user posted a reply. I have also taken a closer look at facebook and youtube comments............. – jon Jun 17 '12 at 21:06
it appears that youtube doesn't display replies to comments in a tree format and facebook only let replies go to one depth... I guess if they are unable to display comment replies in a multi depth tree display, then it is probably not needed... and I guess it is partly becuase of the problems described above... thanks again for your help Bill.. regards J – jon Jun 17 '12 at 21:06

score 1 · Answer 4 · answered Dec 05 '11 at 20:15

1

This query might work for you (I did not know your structure, so I guessed at it):

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;

answered Dec 05 '11 at 20:15

Naftali

144,921
39
244
303

This seems about right. I assume the second LEFT JOIN and second AND are for joining 3rd level comment? – Sean H Jenkins Dec 05 '11 at 20:25
Note that this works for at most 3 levels of comments, and does not filter on the actual article, which I assume would be useful. – The Nail Dec 05 '11 at 20:30
Hmm. This appends to the end. I'm morelooking for a method of selecting them, but having them as a seperate row. – Sean H Jenkins Dec 05 '11 at 20:31
Then you need a UNION instead of a JOIN (or a recursive query) – The Nail Dec 05 '11 at 20:32

Multi Threaded Comments PHP?

4 Answers4