PHP Pagination with MySQLi

Question

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.

And this is my query:

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

Answer 1

Build your query to have a LIMIT

End SQL Result;

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

For example;

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

Have a read of LIMIT

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

To paginate with PHP

Something like this should suffice

$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