使用 MySQLi 进行 PHP 分页

2021-12-25 00:00:00 php mysql mysqli pagination

我正在构建自己的 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 您的主键，因为在没有 ORDER BY 子句的情况下，依赖 MySQL 给出的顺序在分页方面并不安全"(因为你可能会得到重复的行(在不同的页面上))

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

相关文章