MySQLi的PHP分页

本文介绍了MySQLi的PHP分页的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在构建自己的CMS.我已经建立了一个管理系统，可以用它在数据库中插入帖子，显示帖子不是问题，但是我不知道如何进行分页.

I'm building my own CMS. I have an administration system made and I can insert posts in the database with it, showing posts isn't a problem, but I have no idea on how to do the pagination.

这是我的查询:

SELECT * FROM `posts` WHERE `status` != 'draft'

推荐答案

构建查询以使其具有LIMIT

结束SQL结果；

SELECT * FROM posts WHERE status != 'draft' ORDER BY id ASC LIMIT <<offset>>, <<amount>>

例如；

SELECT * FROM posts WHERE status != 'draft' ORDER BY id ASC LIMIT 0, 10 #Fetch first 10
SELECT * FROM posts WHERE status != 'draft' ORDER BY id ASC LIMIT 10, 10 #Fetch next 10

已阅读 LIMIT

您需要ORDER BY您的主键，因为在分页方面依赖MySQL没有ORDER BY子句给出的顺序是安全的"(因为您可能会得到重复的行(在不同的页面上) )

You will need to ORDER BY your primary key, as it's not "safe" to rely on the order MySQL gives without the ORDER BY clause, in terms of pagination (as you may get duplicate rows (on different pages))

这样的东西就足够了

$intTotalPerPage = 10;
$intPage = isset($_GET['page']) && ctype_digit($_GET['page']) ? (int) $_GET['page'] : 0;

$strSqlQuery = "SELECT * FROM posts WHERE status != ? ORDER BY `id` ASC LIMIT ?, ?";
$strStatus = 'draft';
$intStart = ($intPage * $intTotalPerPage);
$intLimit = $intTotalPerPage;
$objDbLink = mysqli_connect("...");
$objGetResults = mysqli_prepare($objDbLink, $strSqlQuery);
mysqli_stmt_bind_param($objGetResults, 'sii',  $strStatus, $intStart, $intLimit);
//Execute query and fetch
//Display results

$objTotalRows = mysqli_query("SELECT COUNT(id) AS total FROM posts WHERE status != 'draft'");
$arrTotalRows = mysqli_fetch_assoc($objTotalRows);

$intTotalPages = ceil($arrTotalRows['total'] / $intTotalPerPage);

for ($i = 0; $i <= $intTotalPages; $i++) {
    echo "<a href='?page=" . $i . "'>[" . $i . "]</a>&bsp;";
}

正如注释中所建议的，通过绑定参数

As suggested in the comments it's good practice to use prepare statements, by binding parameters

这篇关于MySQLi的PHP分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

Order