Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql structure for comments and comment replies

Tags:

People also ask

How do you store comments and replies in a database?

You could just use one table, which includes a ParentID field. If the record has no value, it is a comment, otherwise it is a reply (to either a comment or a reply). You could query the record's ParentID record (inspect it's ParentID) to see if this reply is to a comment or a reply.

How do I represent a comment in MySQL?

MySQL Server supports three comment styles: From a # character to the end of the line. From a -- sequence to the end of the line. In MySQL, the -- (double-dash) comment style requires the second dash to be followed by at least one whitespace or control character (such as a space, tab, newline, and so on).

How do I multi comment in MySQL?

Multi-line comments start with /* and end with */ . Any text between /* and */ will be ignored.

What is /* in MySQL?

This is a type of comment. The /* is the beginning of a comment and */ is the end of comment. MySQL will ignore the above comment.


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?