Not every project gets a framework. Plenty of our client work is still plain PHP talking directly to MySQL, and pagination comes up constantly, a blog listing, a product catalog, a searchable directory of some kind. We've written it enough times now that it's worth writing down properly instead of reinventing it slightly differently on every project.
The pattern
The pattern we keep coming back to: count total rows with a separate COUNT(*) query, calculate total pages with ceil(), then LIMIT the main query using ($page - 1) * $perPage as the offset. Sanitize $page as an integer before it touches the query, obviously, since anything coming from $_GET is untrusted by definition.
Roughly:
- Read $page from the query string, cast it to an int, and clamp it to a minimum of 1.
- Run a COUNT(*) query with the same WHERE clause as the main query, so the total reflects any filtering that's applied.
- Calculate $totalPages = ceil($totalRows / $perPage).
- Run the main query with LIMIT $perPage OFFSET (($page - 1) * $perPage).
- Clamp $page to $totalPages as well, so requesting page 9000 on a three-page result set doesn't produce an empty page or a query error.
Why the separate COUNT query matters
The temptation is to fetch everything and count the PHP array, which works fine on fifty rows and falls over badly once a table has real content. Pulling ten thousand rows into PHP just to call count() on the array and then throw away everything except fifteen of them is wasteful in a way that doesn't show up in local testing but absolutely shows up in a slow page load once a client's catalog has grown.
Running COUNT(*) as its own query lets MySQL do that counting work without ever handing the actual row data back to PHP, and it's cheap as long as the WHERE clause is reasonably indexed. We learned that lesson the hard way on an earlier project where a similarly styled full-scan issue showed up on a completely different query, which is part of why we're paranoid about EXPLAIN-ing anything doing filtering at scale now.
Building the page links
Once you've got $page and $totalPages, generating the actual page number links is the part that varies most by project, some clients want every page number shown, some want a windowed view of a few pages around the current one plus first/last links. Our default is a windowed approach, showing the current page plus two on either side, with ellipses when the range doesn't reach the start or end.
- Always link using GET parameters that preserve any existing filters, sort order, and search terms, not just the page number in isolation.
- Disable, rather than hide, the previous link on page one and the next link on the last page, so the layout doesn't shift depending on which page you're viewing.
- Make sure the "current page" link isn't actually a clickable link to itself, styled differently instead, which sounds obvious but is easy to forget when copy-pasting the loop that generates the other links.
Sanitizing the input properly
Casting $page with (int) handles most of the injection risk on its own, since PHP's int cast simply can't produce anything but a number, but we've also started explicitly checking that it's a positive number rather than trusting that a negative offset just naturally fails safely. In one early version of this pattern, a page value of 0 produced a negative OFFSET that MySQL rejected with an error rather than silently doing something sensible, which meant a stray link or a manually edited URL could break the page for a visitor. Clamping to a minimum of 1 before it ever reaches the query avoids that entirely.
$perPage, when it's configurable at all (a "show 25/50/100 per page" dropdown, say), gets the same treatment, cast to an int and checked against a small allowed list rather than trusted directly, since an unbounded $perPage value is an easy way for someone to request an enormous result set in one go.
Edge cases worth testing deliberately
A few scenarios we now explicitly click through before calling a paginated feature done, since each has bitten us at least once:
- An empty result set, zero rows matching the filter, which should show a sensible "no results" message rather than a divide-by-zero error from calculating $totalPages against zero rows.
- Exactly one full page of results, where there shouldn't be a second, empty page shown as an option.
- A filter or search term applied while sitting on page three of an unfiltered list, which should reset back to page one rather than silently showing an empty page three of the new, shorter filtered result set.
- Someone bookmarking a specific page URL and returning to it after the underlying data has changed enough that the page count shrank, which should clamp gracefully rather than error.
None of these are exotic, they're the kind of thing a client will stumble into within the first week of real use, typing something into a search box while sitting on page four of the full list being the single most common one we've seen.
Would this survive a move to a framework
We've had a couple of internal conversations about whether it's worth adopting one of the PHP frameworks getting attention this year for future projects, and pagination is one of the smaller reasons in favor. Most frameworks bundle something like this pattern as a helper or a query builder method, which removes the chance of subtly getting the offset math wrong project to project. For now, plain PHP and MySQL is still where most of our client budget sits, so this pattern earns its keep, but it's a good example of the kind of small, easy-to-get-slightly-wrong logic that framework tooling exists to standardize.
Where we've reused this
This pattern has shown up nearly identically in a blog archive, a staff directory, a searchable product list, and a simple admin table of form submissions, across at least four different client projects this year alone. Having it written down as a small reusable snippet, rather than rebuilding the offset math from memory each time, has already saved us from repeating the off-by-one page count error we made on the very first version of this, where ceil() versus floor() confusion meant the last page was sometimes silently unreachable.
It's not glamorous but it's predictable, and predictable is what you want when you're the only one maintaining a client's site six months from now and need to remember how the page numbers actually get calculated without re-deriving the whole thing from scratch. We're keeping this as a small internal snippet file rather than pulling in a pagination library, mainly because the actual logic is short enough that a library would mostly just be documentation and configuration options wrapped around the same few lines shown above.
Combining pagination with a search filter
The one detail that trips people up combining search and pagination is making sure the exact same WHERE clause, same bound parameters and all, is used for both the COUNT(*) query and the main SELECT. It sounds obvious written out, but it's an easy mistake to make when a feature grows organically, someone adds a new filter dropdown to the main query and forgets the count query needs the identical condition, and the result is a page count that doesn't match the actual number of rows a visitor sees, which surfaces as a confusing "page 4 of 4" that's actually empty.
Our fix for this in practice is building the WHERE clause and its parameter array once, as a single reusable chunk, and passing that same chunk into both queries rather than writing each one out separately. It's a small refactor but it's removed an entire category of bug that used to show up whenever pagination and filtering lived in the same feature.
We've started treating this shared-WHERE-clause approach as a small internal rule rather than a one-off fix, since it's the kind of subtle bug that passes casual testing, click page one, click page two, looks fine, and only shows up once a real visitor applies a filter and then pages through results, which is exactly the kind of interaction a developer testing their own feature rarely bothers to reproduce. Worth the extra discipline for how rarely anyone would think to test that specific combination otherwise.