mysql结构,用于注释和注释回复 [英] mysql structure for comments and comment replies
问题描述
我已经考虑了很长时间了,我需要一种对数据库中的注释添加答复的方法,但是我不确定如何进行.
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 ;
这是我当前的查询:
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
一个选项是创建一个名为"comment_replies"的新表,但是我不确定是否能够在一个查询中选择所有评论和评论回复,以及是否添加一个名为"reply"的新列我不确定如何对它们进行排序,以使每个回复都得到每个评论.
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.
以下关于添加parent_comment_id的答案来自1条评论和2条回复的这种数组:
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?
推荐答案
我决定在数据库中添加parent_id列,而不是退出加入回复,而是立即选择了所有评论,以便稍后对评论和回复进行排序使用服务器端代码,查询如下:
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;
}
这篇关于mysql结构,用于注释和注释回复的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!