title: mysql structure for comments and comment replies
body: <p>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.</p>
  
  <p>This is my currently comment table (doesn't say much but its a start):</p>
  
  <pre><code>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 ;
  </code></pre>
  
  <p>and here is my current query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>I would love to get some advice on how to deal with this.</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>Following the below answers about adding parent_comment_id result in this kind of array from 1 comment and 2 replies:</p>
  
  <pre><code>array(2) {
    [0]=&gt;
    object(stdClass)#17 (7) {
      ["id"]=&gt;
      string(2) "26"
      ["comment"]=&gt;
      string(36) "adding a comment from the admin page"
      ["user_id"]=&gt;
      string(2) "16"
      ["ts"]=&gt;
      string(10) "1249869350"
      ["username"]=&gt;
      string(5) "Admin"
      ["photo"]=&gt;
      string(13) "gravatar2.png"
      ["reply"]=&gt;
      string(23) "There is no admin page!"
    }
    [1]=&gt;
    object(stdClass)#18 (7) {
      ["id"]=&gt;
      string(2) "26"
      ["comment"]=&gt;
      string(36) "adding a comment from the admin page"
      ["user_id"]=&gt;
      string(2) "16"
      ["ts"]=&gt;
      string(10) "1249869350"
      ["username"]=&gt;
      string(5) "Admin"
      ["photo"]=&gt;
      string(13) "gravatar2.png"
      ["reply"]=&gt;
      string(13) "Yes there is!"
    }
  }
  </code></pre>
  
  <p>How should I process this array to work with it, Is it possible to separate the comment from the replies?</p>