php + MySQL 实现评论

因为某些地方要用到评论,于是就有了这篇文章

注:评论出库机制参考了 WordPress

不得不说 WordPress 的结构太乱了,弄得我不想翻它代码,索性直接从 sql 语句记录中获取:

1 Query	SELECT wp_comments.* FROM wp_comments WHERE comment_ID IN (2,6)
1 Query	SELECT  wp_comments.comment_ID FROM wp_comments  WHERE ( ( comment_approved = '1' ) OR ( user_id = 1 AND comment_approved = '0' ) ) AND comment_post_ID = 21 AND comment_parent IN ( 2,6 )  ORDER BY wp_comments.comment_date_gmt ASC, wp_comments.comment_ID ASC
1 Query	SELECT wp_comments.* FROM wp_comments WHERE comment_ID IN (7,8,9)
1 Query	SELECT  wp_comments.comment_ID FROM wp_comments  WHERE ( ( comment_approved = '1' ) OR ( user_id = 1 AND comment_approved = '0' ) ) AND comment_post_ID = 21 AND comment_parent IN ( 7,8,9 )  ORDER BY wp_comments.comment_date_gmt ASC, wp_comments.comment_ID ASC
1 Query	SELECT wp_comments.* FROM wp_comments WHERE comment_ID IN (11,12)

经过观察,WordPress 的大致思路是很暴力将所有有关该文章的评论全部取出来,并对其进行整理,形成嵌套关系

MySQL 结构设计

CREATE TABLE `comments` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT , `uid` BIGINT(20) NOT NULL , `post_id` BIGINT(20) NOT NULL , `date` DATETIME NOT NULL , `content` LONGTEXT NOT NULL , `status` VARCHAR(20) NOT NULL DEFAULT 'publish' , `parent` BIGINT(20) NOT NULL DEFAULT '0' , PRIMARY KEY (`id`), INDEX (`uid`), INDEX (`post_id`), INDEX (`date`), INDEX (`status`), INDEX (`parent`)) ENGINE = InnoDB;
表名解释
id评论 id(这是一个自增项)
uid用户 id
post_id文章 id
date评论日期
content评论内容
status评论状态(发布,未发布等等)
parent父评论 id

仿照 WordPress 读评论的方式,我们很自然地写出下面这串代码

$post_id  = 0; # 文章 id
$ids      = '0'; # 不管它
$page     = 1; # 页码(仿 wp)
$max_show = 2; # 每页最大显示个数(父评论,非回复)
do {
    $re = mysqli_query($con, "SELECT * FROM `comments` WHERE `status` = 'publish' AND `post_id` = '{$post_id}' AND `parent` IN ({$ids}) ORDER BY `date`, `id`" . ($ids == '0' ? " LIMIT " . ($page - 1) * $max_show . ', ' . $max_show : "") . ";");

    $ids = '';
    while ($data = mysqli_fetch_array($re, MYSQLI_ASSOC)) {
        print_r($data);
        $ids = $ids . ($ids == '' ? '' : ', ') . $data['id'];
    }
} while (mysqli_num_rows($re));

输出的内容是类似于这样的

Array
(
    [id] => 1
    [uid] => 1
    [post_id] => 0
    [date] => 2019-12-07 06:06:43
    [content] => 1
    [status] => publish
    [parent] => 0
)
Array
(
    [id] => 2
    [uid] => 1
    [post_id] => 0
    [date] => 2019-12-07 06:06:44
    [content] => 2
    [status] => publish
    [parent] => 0
)
Array
(
    [id] => 3
    [uid] => 1
    [post_id] => 0
    [date] => 2019-12-07 06:06:51
    [content] => 1
    [status] => publish
    [parent] => 1
)
Array
(
    [id] => 4
    [uid] => 1
    [post_id] => 0
    [date] => 2019-12-07 06:06:52
    [content] => 2
    [status] => publish
    [parent] => 1
)

我很好奇为什么 WordPress 这么设计

但在实际环境中肯定不是这个样子,至少需要一个逻辑关系,就好像

比如我想实现嵌套(就像上面这张图提到的这种),很明显这里要用到算法,第一时间想到的肯定是递归,但是递归有局限性,容易爆栈,而且速度不怎么样

那就用

public function getcomments($post_id, $page, $max_show)
    {
        $ids    = '0';
        $tree   = array();
        $points = array();
        do {
            $re  = mysqli_query(self::consql(), "SELECT * FROM `comments` WHERE `status` = 'publish' AND `post_id` = '{$post_id}' AND `parent` IN ({$ids}) ORDER BY `date`, `id`" . ($ids == '0' ? " LIMIT " . ($page - 1) * $max_show . ', ' . $max_show : "") . ";");
            $ids = '';
            while ($data = mysqli_fetch_array($re, MYSQLI_ASSOC)) {
                if ($data['parent'] != '0') {
                    $t = &$points[$data['parent']]['children'][];
                    $t = $data;

                    $points[$data['id']] = &$t;
                } else {
                    $tree[$data['id']] = $data;

                    $points[$data['id']] = &$tree[$data['id']];
                }

                $ids = $ids . ($ids == '' ? '' : ', ') . $data['id'];
            }
        } while (mysqli_num_rows($re));
        return $tree;
    }

输出来的效果应该是这样的

Array
(
    [1] => Array
        (
            [id] => 1
            [uid] => 1
            [post_id] => 0
            [date] => 2019-12-07 06:06:43
            [content] => 1
            [status] => publish
            [parent] => 0
            [children] => Array
                (
                    [0] => Array
                        (
                            [id] => 3
                            [uid] => 1
                            [post_id] => 0
                            [date] => 2019-12-07 06:06:51
                            [content] => 1
                            [status] => publish
                            [parent] => 1
                        )

                    [1] => Array
                        (
                            [id] => 4
                            [uid] => 1
                            [post_id] => 0
                            [date] => 2019-12-07 06:06:52
                            [content] => 2
                            [status] => publish
                            [parent] => 1
                        )
                )

        )

    [2] => Array
        (
            [id] => 2
            [uid] => 1
            [post_id] => 0
            [date] => 2019-12-07 06:06:44
            [content] => 2
            [status] => publish
            [parent] => 0
        )

)

在使用时遍历一下就可以了,复杂度应该蛮低了


当然,你也可以不用这种方法,你可以用排序等其他方法

总感觉不是那么完美

本站遵循「CC BY 4.0」创作共享协议,转载请注明出处