thinkphp5 数据库查询之paginate: 同时获取记录总数和分页数据

thinkphp5中要想同时获得查询记录的总数量以及分页的数据, 可以用paginate(), 真的非常方便!

表结构:

CREATE TABLE `t_users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(128) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '登录账号',
  `passwd` varchar(128) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `nickname` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '昵称',
  `company` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '公司',
  `contact` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '联系人',
  `address` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '详细地址',
  `city` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '城市',
  `country` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '国家',
  `province` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '省/州',
  `zip_code` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '邮政编码',
  `phone` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '电话',
  `token` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '登录唯一凭证, 登录时更新',
  `check` tinyint(1) DEFAULT '0' COMMENT '0-未通过, 1-通过',
  `check_time` timestamp NULL DEFAULT NULL COMMENT '审核时间',
  `time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='注册用户表';

表数据截图:

获取记录总数和分页数据:

public function test()
    {
        $r = db(self::TABLE)->paginate(3, false);
//        $r = model(self::TABLE)->paginate(3, false);
        print_r($r);

        $data = [
            'total'     => $r->total(),         // 总记录数
            'cur'       => $r->currentPage(),   // 当前页码
            'size'      => $r->listRows(),      // 每页记录数
            'list'      => $r->items()          // 分页数据
        ];

        print_r($data);
    }

截图:

最终运行结果:

cmf\paginator\Bootstrap Object
(
    [simple:protected] =>
    [items:protected] => think\Collection Object
        (
            [items:protected] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [email] => [e-mail]
                            [passwd] => e10adc3949ba59abbe56e057f20f883e
                            [nickname] =>
                            [company] =>
                            [contact] =>
                            [address] =>
                            [city] =>
                            [country] => United Kingdom
                            [province] =>
                            [zip_code] =>
                            [phone] =>
                            [token] => ae4e61fa4ec3b7fc144603e4ca8e1f83
                            [check] => 1
                            [check_time] => 2019-08-21 22:23:16
                            [time] => 2019-08-19 10:25:18
                        )

                    [1] => Array
                        (
                            [id] => 4
                            [email] => [e-mail]
                            [passwd] => e10adc3949ba59abbe56e057f20f883e
                            [nickname] =>
                            [company] =>
                            [contact] =>
                            [address] =>
                            [city] =>
                            [country] => 日本
                            [province] =>
                            [zip_code] =>
                            [phone] =>
                            [token] => f1267ace3614544c7eda97e8b831f5ac
                            [check] => 1
                            [check_time] =>
                            [time] => 2019-08-19 12:32:25
                        )

                    [2] => Array
                        (
                            [id] => 7
                            [email] => [e-mail]
                            [passwd] => e10adc3949ba59abbe56e057f20f883e
                            [nickname] =>
                            [company] =>
                            [contact] =>
                            [address] =>
                            [city] =>
                            [country] => 日本
                            [province] =>
                            [zip_code] =>
                            [phone] =>
                            [token] => ae0ffcc37d1f6f564e6045892f04a5ea
                            [check] => 0
                            [check_time] =>
                            [time] => 2019-08-19 16:43:13
                        )

                )

        )

    [currentPage:protected] => 1
    [lastPage:protected] => 2
    [total:protected] => 6
    [listRows:protected] => 3
    [hasMore:protected] => 1
    [options:protected] => Array
        (
            [var_page] => page
            [path] => /admin/users/test
            [query] => Array
                (
                )

            [fragment] =>
            [type] => \cmf\paginator\Bootstrap
            [list_rows] => 15
        )

)
Array
(
    [total] => 6
    [cur] => 1
    [size] => 3
    [list] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [email] => [e-mail]
                    [passwd] => e10adc3949ba59abbe56e057f20f883e
                    [nickname] =>
                    [company] =>
                    [contact] =>
                    [address] =>
                    [city] =>
                    [country] => United Kingdom
                    [province] =>
                    [zip_code] =>
                    [phone] =>
                    [token] => ae4e61fa4ec3b7fc144603e4ca8e1f83
                    [check] => 1
                    [check_time] => 2019-08-21 22:23:16
                    [time] => 2019-08-19 10:25:18
                )

            [1] => Array
                (
                    [id] => 4
                    [email] => [e-mail]
                    [passwd] => e10adc3949ba59abbe56e057f20f883e
                    [nickname] =>
                    [company] =>
                    [contact] =>
                    [address] =>
                    [city] =>
                    [country] => 日本
                    [province] =>
                    [zip_code] =>
                    [phone] =>
                    [token] => f1267ace3614544c7eda97e8b831f5ac
                    [check] => 1
                    [check_time] =>
                    [time] => 2019-08-19 12:32:25
                )

            [2] => Array
                (
                    [id] => 7
                    [email] => [e-mail]
                    [passwd] => e10adc3949ba59abbe56e057f20f883e
                    [nickname] =>
                    [company] =>
                    [contact] =>
                    [address] =>
                    [city] =>
                    [country] => 日本
                    [province] =>
                    [zip_code] =>
                    [phone] =>
                    [token] => ae0ffcc37d1f6f564e6045892f04a5ea
                    [check] => 0
                    [check_time] =>
                    [time] => 2019-08-19 16:43:13
                )

        )

)

===============================================

另外, paginate中第3个参数里可以配置请求的页码, 即请求指定页数据, 示例如下:

public function test()
    {
        $r = db(self::TABLE)->alias('a')
            ->join('users_good b', 'b.uid = a.id')
            ->field('uid, a.check,b.chinese,b.math')
            ->paginate(3, false, [
                'page'  => 2                    // 指定请求的页码
            ]);
        print_r($r);

        $data = [
            'total'     => $r->total(),         // 总记录数
            'cur'       => $r->currentPage(),   // 当前页码
            'size'      => $r->listRows(),      // 每页记录数
            'list'      => $r->items()          // 分页数据
        ];

        print_r($data);
    }