Simple Pagination In PHP MySQL (Step-By-Step Example)

Welcome to a tutorial on how to create simple pagination with PHP and MySQL. Yes, there are a lot of frameworks out there that deal with pagination, but some left people scratching their heads with an insanely long “how to use bible”. So here it is, let us walk through a simple “DIY pagination” example that does not require any third-party frameworks – Read on!

 

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

Here is the download link to the example code, so you don’t have to copy-paste everything.

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

PHP MYSQL PAGINATION

All right, let us now get started with the pagination example with a dummy user table.

 

TUTORIAL VIDEO

 

STEP 0) DUMMY USERS TABLE

x-dummy.sql
CREATE TABLE `users` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD KEY `name` (`name`);

ALTER TABLE `users`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
 
INSERT INTO `users` (`id`, `name`) VALUES
(1, 'John Doe'),
(2, 'Jane Doe'),
(3, 'Rusty Terry'),
(4, 'Peers Sera'),
(5, 'Jaslyn Keely'),
... ;

Before we go into the scripts, let us begin by creating a dummy user table to work with. This is but a simple one with 2 fields – The ID and name.

 

 

STEP 1) CONNECT TO THE DATABASE

1-paginate.php
<?php
// (A) SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");
define("PER_PAGE", "10"); // entries per page
 
// (B) CONNECT TO DATABASE
$pdo = new PDO(
  "mysql:host=". DB_HOST .";dbname=". DB_NAME .";charset=". DB_CHARSET,
  DB_USER, DB_PASSWORD, [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);

Yep, step 1 of the script should be very “Captain Obvious”. Just a couple of settings and connecting to the database itself.

 

STEP 2) GET THE TOTAL NUMBER OF PAGES

1-paginate.php
// (C) TOTAL NUMBER OF PAGES
$stmt = $pdo->prepare("SELECT CEILING(COUNT(*) / ".PER_PAGE.") `pages` FROM `users`");
$stmt->execute();
$pageTotal = $stmt->fetchColumn();

Moving on, we need to calculate the total number of pages.

  • In the previous step, we have set the pagination to show 10 entries per page – define("PER_PAGE", "10")
  • So, the total number of pages can then be easily obtained with a single SQL query – SELECT CEILING(COUNT(*) / PER_PAGE) `pages` FROM `users`.

 

 

STEP 3) GET ENTRIES FOR THE CURRENT PAGE

1-paginate.php
<?php
// (D) GET ENTRIES FOR CURRENT PAGE
// (D1) LIMIT (X, Y) FOR SQL QUERY
$pageNow = isset($_GET["page"]) ? $_GET["page"] : 1 ;
$limX = ($pageNow - 1) * PER_PAGE;
$limY = PER_PAGE;

// (D2) SQL FETCH
$stmt = $pdo->prepare("SELECT * FROM `users` ORDER BY `id` LIMIT $limX, $limY");
$stmt->execute();
$users = $stmt->fetchAll();

With that, we can now move forward to get the entries for the current page.

  • For this demo, we will use $_GET["page"] (http://site.com/1-paginate.php?page=N) to switch between the pages. If $_GET["page"] is not set, we will assume that it is page 1.
  • To get the entries for the current page, we need to run a "SELECT * FROM `users` LIMIT X, Y query.
    • If you are new – X is the starting row, and Y is the number of rows to extract.
    • On page 1, we fetch entries 1 to 10. That is X = 0 and Y = 10.
    • On page 2, we fetch entries 11 to 20. That is X = 10 and Y = 10.
    • On page 3, we fetch entries 21 to 30. That is X = 20 and Y = 10, and so on.
  • Putting that into the mathematical form:
    • $limX = ($pageNow - 1) * PER_PAGE;
    • $limY = PER_PAGE;

 

 

STEP 4) OUTPUT HTML

1-paginate.php
<!-- (E1) USER LIST -->
<ul class="demo"><?php
foreach ($users as $u) {
  printf("<li>%u %s</li>", $u["id"], $u["name"]);
}
?></ul>

<!-- (E2) PAGINATION -->
<div class="pagination"><?php
for ($i=1; $i<=$pageTotal; $i++) {
  printf("<a %shref='1-paginate.php?page=%u'>%u</a>", 
    $i==$pageNow ? "class='current' " : "", $i, $i
  );
}
?></div>

With all the calculations done, all that’s left is to generate the HTML.

  • Output the user entries –  foreach ($users as $u) { ... }
  • Draw the pagination squares – for ($i=1; $i<=$pageTotal; $i++) { ... }

Of course, feel free to use your own favorite library to create a nice list and pagination – Bootstrap, Material Design, etc…

 

 

EXTRAS

That’s all for this guide, and here is a small section on some extras and links that may be useful to you.

 

ADDING SEARCH & FILTER

To add a “search box with pagination”, we simply need to add an HTML form.

1-paginate.php
<form method="get" action="1-paginate.php">
  <input type="hidden" name="page" value="1">
  <input type="text" name="search" id="search">
  <input type="submit" value="Go!">
</form>

Then change the SQL accordingly to also include the search term/restrictions:

1-paginate.php
// TOTAL NUMBER OF PAGES
$stmt = $pdo->prepare("SELECT CEILING(COUNT(*) / ".PER_PAGE.") `pages` FROM `users` WHERE `name` LIKE ?");
$stmt->execute(["%".$_GET["search"]."%"]);

// GET USERS
$stmt = $pdo->prepare("SELECT * FROM `users` WHERE `name` LIKE ? LIMIT $limX, $limY");
$stmt->execute(["%".$_GET["search"]."%"]);

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end of this guide. I hope that it has helped you with your project, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

6 thoughts on “Simple Pagination In PHP MySQL (Step-By-Step Example)”

  1. How would you use this pagination with a non DB array? For example your tutorial on generating a gallery from images found in a directory.

    1. The basic calculations are the same?

      1) START = (PAGE NOW – 1) * PER PAGE
      2) END = START + PER PAGE
      3) FOR (I=START; I<END; I++) { DRAW IMAGES }

      4) TOTAL PAGES = CEIL(NUMBER OF IMAGES / PER PAGE)
      5) FOR (I=1; I<=TOTAL PAGES; I++) { DRAW PAGINATION }

  2. How would we do this with binding the params instead of adding the $limX, $limY in the query directly (for security)?

  3. Thank you for the post. It was very helpful. Hope in your next update to this post you will show us to do some paging like in DataTables.
    with numbers and dots or page ranges eg:
    ————–
    On page 1
    Previous 1 2 3 4 5 … 25 Next , Previous is disabled
    On page 5
    Previous 1 … 4 5 6 … 25 Next , All enabled
    Fifth from last
    Previous 1 … 20 21 22 … 25 Next , All enabled
    Last page
    Previous 1 … 21 22 23 24 25 Next , Next disabled
    ————–
    Thank you.

Comments are closed.