php 46 lines · 7 steps

Safe SQL pagination with PDO in PHP

A repository method turns untrusted page params into a bounded, parameterized query plus pagination metadata.

Explained by highlit
Intermediate pagination input-validation prepared-statements repository-pattern sql
1<?php
2 
3final class ProductRepository
4{
5 private const PER_PAGE = 20;
6 private const MAX_PER_PAGE = 100;
7 
8 public function __construct(private \PDO $db) {}
9 
10 public function paginate(array $params): array
11 {
12 $page = max(1, (int) ($params['page'] ?? 1));
13 $perPage = (int) ($params['per_page'] ?? self::PER_PAGE);
14 $perPage = min(self::MAX_PER_PAGE, max(1, $perPage));
15 $offset = ($page - 1) * $perPage;
16 
17 $total = (int) $this->db->query(
18 'SELECT COUNT(*) FROM products WHERE active = 1'
19 )->fetchColumn();
20 
21 $stmt = $this->db->prepare(
22 'SELECT id, name, price, created_at
23 FROM products
24 WHERE active = 1
25 ORDER BY created_at DESC
26 LIMIT :limit OFFSET :offset'
27 );
28 $stmt->bindValue(':limit', $perPage, \PDO::PARAM_INT);
29 $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
30 $stmt->execute();
31 
32 $items = $stmt->fetchAll(\PDO::FETCH_ASSOC);
33 $lastPage = (int) max(1, ceil($total / $perPage));
34 
35 return [
36 'data' => $items,
37 'meta' => [
38 'current_page' => $page,
39 'per_page' => $perPage,
40 'total' => $total,
41 'last_page' => $lastPage,
42 'has_more' => $page < $lastPage,
43 ],
44 ];
45 }
46}
01 / 01
STEP 01

Walkthrough

Space play ←→ step click any line
Three takeaways
  1. 1Clamp user-supplied paging input to sane bounds before it ever reaches the database.
  2. 2Bind LIMIT and OFFSET as typed integer parameters rather than interpolating them into SQL.
  3. 3Returning data alongside computed meta lets clients paginate without recalculating offsets.

Related explainers

php 
<?php
 
namespace App\Support;
 

Locale-aware formatting with PHP's intl extension

internationalization encapsulation constructor-injection
Intermediate 7 steps
javascript 
const express = require('express');
 
const v1 = express.Router();
 

Versioning an API with Express Routers

api versioning routing modularity
Intermediate 10 steps
php 
<?php
 
namespace App\Support;
 

Merging query params onto a URL in PHP

url-parsing query-strings immutability
Intermediate 8 steps
python 
import csv
import io
from datetime import datetime
 

Streaming a CSV export in Flask

streaming generators csv
Intermediate 9 steps
php 
<?php
 
class ImageUploadService
{

Validating file uploads safely in PHP

file-upload input-validation security
Intermediate 8 steps
php 
<?php
 
namespace App\View;
 

Building a safe HTML escaper in PHP

security xss escaping
Intermediate 6 steps

Share this explainer

Here's the card — post it anywhere.

Safe SQL pagination with PDO in PHP — share card
Made with highlit — turn any snippet into a walkthrough like this in about a minute.
Explain your code