Hi, many websites allow readers to comment on the articles or content of the site. Lots of these sites just return each comment to the screen in the order they were created which is easy enough to implement.
However some sites allow readers to comment on other readers comments. These 'reply' comments as I will call them will appear under the comment they are replying to and are indented a bit to make the structure more clear. For example the structure might appear like so:
ARTICLE CONTENT HERE
This article has 9 comments:
Leave a comment (form displayed)
For a real example see here (scroll down to the user comments). You will see a parent comment followed by indented replies to the parent comment if there are any. I was wondering how to achive this effect as I'm not sure how the SQL would work.
On my comment table I have the fields comment_id (Primary Key) and a replyTo_ID. When a user replies to say comment one which has a PK of 1, that number is inserted into the replyTo_ID field. I've tried sorting the results by comment_id and then replyTo_ID but it does not work.
Reluctantly I think I can do it with two queries, the first query gets the comment_id and in in the loop another query is made that fetches any replyTo_ID that matches the comment_id returned in the first query. I know this can work as I've done something similar before:
FORM QUERY : SELECT parent_comments FROM comment_table
while parent_comments are found
define variable : parent_id
define variable : parent_comment
FORM QUERY : SELECT child_comments FROM comment_table WHERE reply_to_id=parent_id
IF rows found
while child comments are found
define variable : child_id
define variable : child_comment
Obviously this is making multiple calls (and pointless ones if there are no reply comments to retrieve) to the database which is something I would like to avoid. Therefore could it be done with just one query?