問題描述

我正在構建自己的 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

這篇關于使用 MySQLi 進行 PHP 分頁的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！