评论和评论回复的mysql结构

2021-12-17 00:00:00 join 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 ;

这是我当前的查询:

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;
}

相关文章