PHP Pagination MySQL Database Example Script with Previous Next like Google

Do you want to implement PHP pagination using core PHP with lightweight script? Jump in, this tutorial is all about it.

The pagination functionality is for stacking a list of items on many pages. It helps to fetch and display a huge volume of records in an effective way.

If you want to implement a simple PHP pagination, then it is very easy and straight forward.

There is just one thing that you should keep in mind when you work on pagination. Do not pull the complete result set and filter it to display to the users.

SELECT only records using LIMIT from database. It can be done via AJAX or on page submit depending on your use case. We will see more about it in the example below. 

There are scenarios to create pagination with AJAX. For example, an online quiz with a paginated question board.

What is inside?

1. About this example
2. Plugins to enable pagination feature
3. File structure
4. Database script
5. Design landing page with pagination controls
6. Php code to get paginated results
7. PHP AJAX pagination Output

About this example

In this example code, it has two types of control to navigate with the paginated results.

One is the pagination links to redirect us to the corresponding page. The other is an input to enter the page number. By submitting this page number we can jump into the target page from the current page.

I have implemented PHP pagination with a simple page refresh. We have already seen examples for achieving this with AJAX using jQuery.

I have enabled pagination for the list of database results displaying Animal names. The pagination links count varies based on the animal database record count. 

A SELECT query with a specification of start and end limit fetch the page result. The per-page result count is configurable as a Config class constant. 

Plugins to enable pagination feature

There are many plugins available to create pagination easily. Pagination.js and jqPaginator are examples of 3-party plugins available in the market.

The Datatables is one of the popular library used for managing tabular data. It has an in-built search, pagination, sort, and more features. In an old article, we had seen how to integrate Datatables to list records with pagination.

Having said all these, better to go with custom code. Because external plugins, libraries will slow down the application.

File structure

The working example code contains the following files in the below hierarchical order.

The index.php file is a landing page. It contains the HTML to display pagination links and the current page results.

Two CSS used in this example. The phppot-style.css has generic layout styles. The pagination.css has the exclusive styles created for this example.

Model/Pagination.php includes functions to fetch the MySQL data. It receives parameters to set the limit for the SELECT query.

The file structure includes a SQL script to set up the database for running this example.

Database script

--
-- Database: `pagination`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_animal`
--

CREATE TABLE `tbl_animal` (
  `id` int(11) UNSIGNED NOT NULL,
  `common_name` varchar(255) NOT NULL DEFAULT '',
  `scientific_name` varchar(255) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tbl_animal`
--

INSERT INTO `tbl_animal` (`id`, `common_name`, `scientific_name`) VALUES
(1, 'Bison', 'Bos gaurus\r\n'),
(2, 'Black buck', 'Antelope cervicapra'),
(3, 'Chinkara', 'Gazella bennettii'),
(4, 'Nilgai', 'Boselaphus tragocamelus'),
(5, 'Wolf', 'Canis lupus'),
(6, 'Lion', 'Panthera leo'),
(7, 'Elephant', 'Elephas maximus'),
(8, 'Wild Ass', 'Equus africanus asinus'),
(9, 'Panther', 'Panthera pardus'),
(10, 'Kashmir stag', 'Cervus canadensis hanglu'),
(11, 'Peacock', 'Pavo cristatus'),
(12, 'Siberian crane', 'Grus leucogeranus'),
(13, 'Fox', 'Vulpes vulpes'),
(14, 'Rhinoceros', 'Rhinoceros unicornis'),
(15, 'Tiger', 'Panthera Tigris'),
(16, 'Crocodile', 'Crocodylus palustris'),
(17, 'Gavial or Gharial', 'Gavialis gangeticus'),
(18, 'Horse', 'Equus caballus'),
(19, 'Zebra', 'Equus quagga'),
(20, 'Buffalow', 'Babalus bubalis'),
(21, 'Wild boar', 'Sus scrofa'),
(22, 'Arabian camel', 'Camelus dromedaries'),
(23, 'Giraffe', 'GiraffaÊcamelopardalis'),
(24, 'House wall Lizard', 'Hemidactylus flaviviridis'),
(25, 'Hippopotamus', 'Hippopotamus amphibius'),
(26, 'Rhesus monkey', 'Macaca mulatta'),
(27, 'Dog', 'Canis lupus familiaris'),
(28, 'Cat', 'Felis domesticus'),
(29, 'Cheetah', 'Acinonyx jubatus'),
(30, 'Black rat', 'Rattus rattus'),
(31, 'House mouse', 'Mus musculus'),
(32, 'Rabbit', 'Oryctolagus cuniculus'),
(33, 'Great horned owl', 'Bubo virginianus'),
(34, 'House sparrow', 'Passer domesticus'),
(35, 'House crow', 'Corvus splendens'),
(36, 'Common myna', 'Acridotheres tristis'),
(37, 'Indian parrot', 'Psittacula eupatria'),
(38, 'Bulbul', 'Molpastes cafer'),
(39, 'Koel', 'Eudynamis scolopaccus'),
(40, 'Pigeon', 'Columba livia'),
(41, 'Indian Cobra', 'Naja naja'),
(42, 'King cobra', 'Ophiophagus hannah'),
(43, 'Sea snake', 'Hydrophiinae'),
(44, 'Indian python', 'Python molurus'),
(45, 'Rat snake', 'Rat snake');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_animal`
--
ALTER TABLE `tbl_animal`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_animal`
--
ALTER TABLE `tbl_animal`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=46;

Designing landing page with pagination controls

The landing page shows the database results with a pagination option. The following HTML has the embedded PHP script to display paginated results.

It contains table rows created in a loop on iterating the data array. Below this table, it shows pagination links with previous next navigation. This navigation allows moving back and forth from the current page.

This example also contains a HMTL form to enter the target page number. If you enter a number within the number of pages, then it will get the appropriate page result.

<!doctype html>
<html lang="en">
<head>
<title>PHP Pagination MySQL Database Example Script with Previous Next like Google</title>
<link rel="stylesheet" type="text/css" href="assets/css/pagination.css">
<link rel="stylesheet" type="text/css"
    href="assets/css/phppot-style.css">
<script src="vendor/jquery/jquery-3.3.1.js"></script>
</head>
<body>
	<div class="phppot-container">
		<div class="phppot-form">
			<h1>Animal Names</h1>
			<table>
				<tr>
					<th>Id</th>
					<th>Common Name</th>
					<th>Scientific Name</th>
				</tr>
  <?php
if (! empty($pageResult)) {
    foreach ($pageResult as $page) {
        ?>
          <tr>
					<td><?php echo $page['id'];?></td>
					<td><?php echo $page['common_name'];?></td>
					<td><?php echo $page['scientific_name'];?></td>
				</tr>  
 <?php }} ?> 
  </table>

			<div class="pagination"> 			
 <?php
 if (($page > 1) && ($pn > 1)) {
    ?>
    <a class="previous-page" id="prev-page"
					href="<?php echo $queryString;?>page=<?php echo (($pn-1));?>"
					title="Previous Page"><span>❮ Previous</span></a>
            <?php }?>
<?php
if (($pn - 1) > 1) {
    ?>
    <a href='index.php?page=1'><div class='page-a-link'> 1 </div></a>
    <div class='page-before-after'>...</div>
<?php 
}

for ($i = ($pn - 1); $i <= ($pn + 1); $i ++) {
    if ($i < 1)
        continue;
    if ($i > $totalPages)
        break;
    if ($i == $pn) {
        $class = "active";
    } else {
        $class = "page-a-link";
    }
    ?>
    <a href='index.php?page=<?php echo $i; ?>'>  <div class='<?php echo $class; ?>'><?php echo $i; ?></div> </a>
    <?php 
}

if (($totalPages - ($pn + 1)) >= 1) {
    ?>
    <div class='page-before-after'>...</div>
<?php 
}
if (($totalPages - ($pn + 1)) > 0) {
    if ($pn == $totalPages) {
        $class = "active";
    } else {
        $class = "page-a-link";
    }
    ?>
    <a href='index.php?page=<?php echo $totalPages; ?>'><div class='<?php echo $class; ?>'><?php echo $totalPages; ?></div></a> 
    <?php 
}
?>
    <?php
    if (($page > 1) && ($pn < $totalPages)) {
        ?>
				<a class="next" id="next-page"
					href="<?php echo $queryString;?>page=<?php echo (($pn+1));?>"
					title="Next Page"><span>Next ❯</span></a> 
        <?php
    }
    ?>
    </div>
			<div class="goto-page">
				<form action="" method="GET" onsubmit="return pageValidation()">
					<input type="submit" class="goto-button" value="Go to">
                    <input type="text" class="enter-page-no" name="page" min="1"
						id="page-no">
<input type="hidden" id="total-page" value="<?php echo $totalPages;?>">
				</form>
			</div>
		</div>
	</div>
</body>
</html>

The pageValidation() is a JavaScript method to confirm the entered page number. If the page number input contains invalid data, then this script will return false. Then it stops form submit and thereby preventing the page-jump action.

<script>
function pageValidation()
{
	var valid=true;
	var pageNo = $('#page-no').val();
	var totalPage = $('#total-page').val();
	if(pageNo == ""|| pageNo < 1 || !pageNo.match(/\d+/) || pageNo > parseInt(totalPage)){
		$("#page-no").css("border-color","#ee0000").show();
		valid=false;
	}
	return valid;
}
</script>

The following styles are from the pagination.css. It defines the CSS for the table view and the pagination elements.

.pagination {
	display: inline-flex;
}

.pagination a {
	color: #505050;
	text-decoration: none;
}

.page-a-link {
	font-family: arial, verdana;
    font-size: 12px;
    border: 1px #afafaf solid;
    background-color: #fbfbfb;
    padding: 6px 12px 6px 12px;
    margin: 6px;
    text-decoration: none;
    border-radius: 3px;
}

.active {
	font-family: arial, verdana;
    font-size: 12px;
    padding: 8px 14px 6px 14px;
    margin: 3px;
    background-color: #404040;
    text-decoration: none;
    border-radius: 3px;
    margin: 6px;
    color: #FFF;
}

a.previous-page {
	margin: 10px 10px 10px 0px;
}

a.prev-next:hover {
	color: #03a9f4;
}

a.next {
	margin: 10px 0px 10px 10px;
}

input.enter-page-no {
	width: 42px !important;
	height: 28px !important;
	font-size: 12px;
	padding: 6px 12px 6px 12px !important;
	margin: 6px;
	border-radius: 3px !important;
	text-align: center !important;
}

input.goto-button {
	max-width: 80px;
	font-size: 12px;
	padding: 6px 12px 6px 12px !important;
	border: 1px solid #9a9a9a;
	border-radius: 3px !important;
	text-align: center !important;
	background: -webkit-gradient(linear, left top, left bottom, color-stop(0%, #dfdd99), color-stop(100%, #bcbd2b));
    background: -webkit-linear-gradient(top, #dfdc99, #b8bd2b);
    border: 1px solid #97a031;
    box-shadow: inset 0px 1px 0px rgb(255, 255, 211), 0px 1px 4px rgba(199, 199, 199, 0.9);
}

.goto-page {
	float: right;
}

.page-before-after {
	font-weight: bold;
	padding-top: 12px;
	text-decoration: none;
}

Php code to get paginated results

This PHP script is for reading the database results and get the record count, pages count. The pages count varies based on the record count and the LIMIT_PER_PAGE configuration.

If there are more pages, this example will show only limited pages as like as Google pagination. This feature will help to make the pagination block to fit into the viewport.

On successive page navigation, you will get more pagination links to browse further.

<?php
namespace Phppot;

require_once __DIR__ . '/Model/Pagination.php';

$paginationModel = new Pagination();
$pageResult = $paginationModel->getPage();
$queryString = "?";
if (isset($_GET["page"])) {
    $pn = $_GET["page"];
} else {
    $pn = 1;
}
$limit = Config::LIMIT_PER_PAGE;

$totalRecords = $paginationModel->getAllRecords();
$totalPages = ceil($totalRecords / $limit);

?>

PHP classes, libraries, configs used in this example

This is a common DataSource library used in most examples. Here, I have shown the functions that are only used in this example. 

You can get the full code by downloading the source added to this article.

Config.php

This is the config file added for this example. I kept the per-page record limit as an application constant. I used this for setting the SELECT query limit.

You can add more constants and fine-tune the PHP pagination script to work based on them. For example, create new constant SHOW_ALL_LINKS. Set it on/off to enable/disable the Google-like feature to show limited links. 

<?php
namespace Phppot;

class Config
{

    const LIMIT_PER_PAGE = '5';
}

PHP pagination Output with Previous Next Links

This screenshot shows the output of the PHP pagination example code. It shows five records per page. 

On the left, it shows usual pagination with previous next and separate page links. On the right side, it is a HMTL form to allow the user to enter the page number.

Refer, how to create Facebook-link infinite scroll pagination to know about scrolling based pagination.

Conclusion

We have discussed the third-party libraries that contain in-built pagination feature. It will be helpful to have a choice if are searching for such plugins.

In this custom PHP pagination example, we have provided an option to jump into a target page directly via a form. Sometimes, it itself is enough with previous next navigation.

Hope this article and the example help you to build a nice simple PHP pagination for your web application.

Download

Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

↑ Back to Top

• PHP
  • Learn PHP
  • PHP Introduction
  • PHP Basics
  • PHP Shopping Cart
  • Payment Gateway
  • Contact Form
  • PHP Login Registration
  • Comments System
  • Types, Variables & Operators
  • PHP Strings
  • PHP Arrays
  • PHP Functions
  • PHP OOPS
  • Event Management System
  • PHP Mail
  • PHP Forms
  • Advanced
  • PHP AJAX
  • RESTful API
  • PHP Databases
  • PHP Sessions and Cookies
  • Error and Exception Handling
  • File Upload
  • File Import Export
  • Files and Directories
  • PHP Date Time
  • PHP XML
  • PHP CSV
  • PHP JSON
  • PHP Code Samples
    • URI Shortener PHP Script
    • Create Shortener to Convert Long URL to Short in JavaScript
    • How to Add CSRF Token to AJAX Request
    • PHP MySQL Date Range Search with jQuery DatePicker
    • Advanced Search using PHP
    • PHP CRUD with Search and Pagination
    • Highlighting Keywords in Search Results with PHP
    • PHP Pagination MySQL Database Example Script with Previous Next like Google
    • Command Line PHP
    • How to Create Custom Image File Explorer in PHP
    • Web Scraping with PHP - Tutorial to Scrape Web Pages
    • User Login Session Timeout Logout in PHP
    • PHP Change Password Script
    • Object-Oriented Poll System using PHP
    • PHP cURL
    • Extract Content using PHP and Preview like Facebook
    • Show Image Thumbnail by YouTube Video URL using PHP
    • Star Rating with PHP and jQuery AJAX
    • Simple HTML 5 star rating code
    • How to Crop Image using jQuery and PHP
    • How to Generate Initial Avatar Image from Username using PHP Dynamically
    • Live search in PHP and MySQL with AJAX
  • Library
  • PHP Freelancer
• jQuery
• JavaScript
• WordPress
• MySQL
• CSS

• Share
• Tweet
• Share

1. Simple PHP Shopping Cart
2. Stripe Payment Gateway Integration using PHP
3. User Registration in PHP with Login: Form with MySQL and Code Download
4. PHP Contact Form
5. How to Create Dynamic Stacked Bar, Doughnut and Pie charts in PHP with Chart.js