How is pagination done in PHP with SQL

Question

I have a small website in PHP that requires some pagination as i have a lot of records in the database and so i need to create pages.

Answer 1

Hint: You can use LIMIT keyword to specify the limit and offset.

Suppose you want to fetch the 5th page of 10 items.

SELECT * FROM `tbl` LIMIT 50, 10

A simple PHP solution can be like this

$page = (int)$_GET['page'] ?? 1;
$pageLength = 10;

$pdo = new \PDO(...connection details);

$stmt = $pdo->prepare("SELECT * FROM `tbl` LIMIT :offset, :length");
$stmt->execute([
'offset' => $page * 10,
'length' => $pageLength,
]);
$items = $stmt->fetchAll();