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

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

表结构:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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='注册用户表';

 表数据截图:

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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

 截图:

 

最终运行结果:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160

cmf\paginator\Bootstrap Object (     [simple:protected] =>     [items:protected] => think\Collection Object         (             [items:protected] => Array                 (                     [0] => Array                         (                             [id] => 1                             [email] => 1@163.com                             [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] => 1121@163.com                             [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] => 1112312@163.com                             [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] => 1@163.com                     [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] => 1121@163.com                     [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] => 1112312@163.com                     [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个参数里可以配置请求的页码, 即请求指定页数据, 示例如下:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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