Pagination With PHP MySQL – A Simple 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!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

REAL QUICK SLIDES

 

TABLE OF CONTENTS

Download & Notes Pagination Useful Bits & Links
The End

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code in a zip file – I have released it under the MIT License, so feel free to build on top of it if you want to.

 

QUICK NOTES

If you spot a bug, please feel free to comment below. I try to answer questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

 

PHP MYSQL PAGINATION

All right, let us now get started with the pagination example with PHP and MYSQL.

 

STEP 0) DUMMY USERS TABLE

0-dummy.sql
CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

ALTER TABLE `users`
  MODIFY `id` int(11) 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 with a dummy user table. Yep, this is but a simple one with 2 fields – The ID and name.

 

 

STEP 1) CONNECT TO THE DATABASE

1-paginate.php
<?php
// (A) DATABASE
// (A1) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_CHARSET', 'utf8');
define('DB_USER', 'root');
define('DB_PASSWORD', '');
define('PER_PAGE', 10); // ENTRIES PER PAGE
 
// (A2) CONNECT TO DATABASE
try {
  $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
    ]
  );
} catch (Exception $ex) { exit($ex->getMessage()); }

Yep, step 1 of the script is a very “Captain Obvious” – Connect to the database. This should be self-explanatory, just remember to change the database settings to your own.

 

STEP 2) GET TOTAL NUMBER OF PAGES

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

Moving on, we get the total number of pages $pageTotal – Which can be easily obtained with a single SQL query SELECT CEILING(COUNT(*) / ENTRIES-PER-PAGE) `pages` FROM `TABLE`.

 

 

STEP 3) GET ENTRIES FOR CURRENT PAGE

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

// (C2) SQL FETCH
$stmt = $pdo->prepare("SELECT * FROM `users` ORDER BY `id` LIMIT $limX, $limY");
$stmt->execute();
$users = $stmt->fetchAll();
  • Basically, the current page is set with $_GET['page']. We will assume page 1 if this is not set – $pageNow = isset($_GET['page']) ? $_GET['page'] : 1 ;
  • To get the entries for the current page, we need to calculate and append LIMIT X, Y to the SELECT SQL query.
    • On page 1, we fetch entries 0 to 9. That is $limX = 0 and $limY = 10.
    • On page 2, we fetch entries 10 to 19. That is $limX = 10 and $limY = 10.
    • On page 3, we fetch entries 20 to 29. That is $limX = 20 and $limY = 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
<h1>USERS</h1>
 
<!-- (D1) USER LIST -->
<ul><?php
foreach ($users as $u) {
  printf("<li>%u %s</li>", $u['id'], $u['name']);
}
?></ul>

<!-- (D2) PAGINATION -->
<div class="pagination" id="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++) { ... }

 

 

EXTRA) COSMETICS

2-paginate.css
/* (A) PAGINATION SQUARES */
.pagination a, .pagination div {
  display: inline-block;
  text-decoration: none;
  color: #555;
  padding: 10px;
  border: 1px solid #ccc;
  margin: 5px;
}
 
/* (B) ON HOVER */
.pagination a:hover, .pagination div:hover {
  cursor: pointer;
  color: #fff;
  background: #4680db;
}
 
/* (C) HIGHLIGHT CURRENT PAGE */
.pagination a.current, .pagination div.current {
  font-weight: bold;
  color: #000;
  background: #fffc9b;
}

Just some simple styles to make the pagination look better.

 

USEFUL BITS & LINKS

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” or “filter” 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']."%"]);

 

YOUTUBE TUTORIAL

 

INFOGRAPHIC CHEAT SHEET

Simple PHP MySQL Pagination (click to enlarge)

 

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!

1 thought on “Pagination With PHP MySQL – A Simple Example”

Leave a Comment

Your email address will not be published. Required fields are marked *