join - mysql structure for comments and comment replies

Question

I've been thinking about this one for quite some time now, I need a way to add replies to comments in the database but I'm not sure how to proceed.

This is my currently comment table (doesn't say much but its a start):

CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `comment` text,
  `user_id` int(12) DEFAULT NULL,
  `topic_id` int(12) NOT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `topic_id` (`topic_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=27 ;

and here is my current query:

SELECT c.id, c.comment, c.user_id, u.username, u.photo
FROM (comments c)
JOIN users u ON c.user_id = u.id
WHERE c.topic_id = 9

One option would be to create a new table called "comment_replies" but I'm not sure If I'm able to select all the comments and comment replies in one query, and If I add a new column called "reply" I'm not sure how to sort them to get each comment with each reply.

I would love to get some advice on how to deal with this.

Edit:

Following the below answers about adding parent_comment_id result in this kind of array from 1 comment and 2 replies:

array(2) {
  [0]=>
  object(stdClass)#17 (7) {
    ["id"]=>
    string(2) "26"
    ["comment"]=>
    string(36) "adding a comment from the admin page"
    ["user_id"]=>
    string(2) "16"
    ["ts"]=>
    string(10) "1249869350"
    ["username"]=>
    string(5) "Admin"
    ["photo"]=>
    string(13) "gravatar2.png"
    ["reply"]=>
    string(23) "There is no admin page!"
  }
  [1]=>
  object(stdClass)#18 (7) {
    ["id"]=>
    string(2) "26"
    ["comment"]=>
    string(36) "adding a comment from the admin page"
    ["user_id"]=>
    string(2) "16"
    ["ts"]=>
    string(10) "1249869350"
    ["username"]=>
    string(5) "Admin"
    ["photo"]=>
    string(13) "gravatar2.png"
    ["reply"]=>
    string(13) "Yes there is!"
  }
}

How should I process this array to work with it, Is it possible to separate the comment from the replies?

Answer 1

If you want people to be able to reply to the replies (i.e. have a hierarchy of replies such as you would see in, say, an online message forum), then I would add an optional parent_comment_id field to the comments table.

Your table would look like this

`CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `parent_comment_id` int(12) NULL,
  `comment` text,
  `user_id` int(12) DEFAULT NULL,
  `topic_id` int(12) NOT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `topic_id` (`topic_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=27 ;`

Your query showing all comments and replies would be something like:

SELECT c.id, c.comment, r.comment as reply, c.user_id, u.username, u.photo
FROM (comments c)
JOIN users u ON c.user_id = u.id
LEFT JOIN comments r ON c.id = r.parent_comment_id
WHERE c.topic_id = 9

Note however that with this query your replies would also show up not only in the 'reply' column, but also in the 'comment' column as additional rows each with zero or more replies.

To show the username of the users who replied to a comment, you will need to join twice to the users table (first for the user who posted the original comment, and again for the user(s) who replied). Try this query to show the usernames of the users who replied:

SELECT c.id, c.comment, c.user_id, u.username, u.photo, r.comment as reply, r.user_id as reply_user_id, 
u2.username as reply_username, u2.photo as reply_photo
FROM (comment c)
JOIN users u ON c.user_id = u.id
LEFT JOIN comments r ON c.id = r.parent_comment_id
JOIN users u2 ON r.user_id = u2.id
WHERE c.topic_id = 9