PHP/MySql Select Comments Grouped with Replies

Question

I'm writing a script to handle comments similar to facebook's status/replies. Basically, an item can have comments and users may reply to that comment (one level deep maximum).

My tables are set up as follows:

Comments: [ Comment_ID | User_ID | Content | etc...]
Comments_Reply: [ Comment_ID | Is_Reply_To ]

So if a user replies to comment #555 it appears in the comments table like a regular comment, then also gets a row in Comments_Reply with [ 555 | New Comment ID ]

How can I select the comments such that they are in the following order:

[ 555 | ....
[ New Comment that replies to 555
[556 | ....
[557 | ....

Etc... where the replies appear sequentially after the comment the reply to. I am using PHP & MySql.

Kevin Peno · Accepted Answer · 2011-03-07 17:50:14Z

7

Ok, I think I got what you want now. I don't have a way to test the MYSQL version of this function quickly, but the SQLite version works great and, according to the documentation, the MySQL should work just as great. Might be a better way to do it on MySQL though.

SQLite:

SELECT a.*, IFNULL( b.Is_Reply_To, a.Comment_ID ) as Is_Reply_To FROM Comments a LEFT JOIN Comments_Reply b HAVING(Comment_ID) ORDER BY Is_Reply_To ASC, a.Comment_ID ASC

MySQL

SELECT a.*, IF( IS NULL(b.Is_Reply_To), a.Comment_ID, b.Is_Reply_To ) as Is_Reply_To FROM Comments a LEFT JOIN Comments_Reply b HAVING(Comment_ID) ORDER BY Is_Reply_To ASC, a.Comment_ID ASC

This produces these results for my on SQLite.

Comment_ID Is_Reply_to
1          1
10         1
11         1
2          2
12         2
3          3
13         3
14         3
4          4
5          5
6          6
7          7
8          8
9          9
15         15

edited Mar 7, 2011 at 17:50

answered Mar 7, 2011 at 17:23

Kevin Peno

9,2261 gold badge36 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kornesh Over a year ago

how did this worked as the LEFT JOIN does not have "ON" statement

Kevin Peno Over a year ago

@kornesh HAVING works if the columns to match in both tables have the same name.

Awais Qarni · Accepted Answer · 2011-03-07 17:44:33Z

0

hi you can use following query

$q="select * from comments";
$rs=mysql_query($q);
if($rs && mysql_num_rows($rs))
{
 while($rd=mysql_fetch_object($rs))
{
  echo($rd->comment_id);
  $q1="select * from comments_reply where comment_id=".$rd->comment_id;
  $rs1=mysql_query($q1);
  if($rs1 && mysql_num_rows($rs1))
   { 
     while($rd1=mysql_fetch_object($rs1))
     {
       echo($rd1->comments_id);
     }
  }
 }
}

answered Mar 7, 2011 at 17:44

Awais Qarni

18k25 gold badges80 silver badges145 bronze badges

1 Comment

TheCarver Over a year ago

With 10,000 comments, your query would cry for help.

Collectives™ on Stack Overflow

PHP/MySql Select Comments Grouped with Replies

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related