对mysql评论,用于评论和评论回复的mysql结构

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?

解决方案

I decided to add the parent_id column in the database and instead of left joining the replies I just selected all the comments at once to later on sort the comments and replies with server-side code, heres the query:

SELECT c.*, u.username, u.photo

FROM (comments c)

JOIN users u ON c.user_id = u.id

WHERE c.topic_id = 9

ORDER BY c.id ASC

Now I pass the query result to the below function so that every reply will be added as an array inside the comment array, so basically it returns a multidimensional array.

function sort_comments($ar)

{

$comments = array();

foreach($ar as $item)

{

if(is_null($item['parent_id'])) $comments[] = $item;

else

{

$parent_array = array_search_key($item['parent_id'],$comments,'id');

if($parent_array !== false) $comments[$parent_array]['replies'][] = $item;

}

}

return $comments;

}