Posted in PHP

Create An Effective Pagination In PHP And MySQL

This tutorial shows how to create effective pagination in PHP and MySQL. When there are lots of data need to be shown on a page then the data grows at the bottom of the page hence the scroll appears. Most of the times it becomes very irritating to go to the bottom so pagination comes to rescue.

Using pagination the navigation becomes effective and you don’t need to scroll the page at bottom. Using pagination you can go to the first page, nest page, previous page, last page and at a particular page clicking on the links. So this tutorial shows how to create effective pagination in PHP, MySQL. This pagination has first, previous, number of paging links, next and last links.

Related Posts:

I will write code for the pagination implementation what you need for the pagination. I will not explain each file but you can easily understand what I want to do in each file. If you have any question please leave a comment in the comment section.

Prerequisites

PHP 7.0.15 – 7.4.22, Apache 2.4, MySQL 5.6 – 8.0.26

database connection tutorial

Project Directory

As a first step I need to create a project directory, where I will keep source code for the application.

Create a directory called php-mysql-pagination. This is the project root directory that will contain your whole source code required for the application.

I may not mention the project root directory in subsequent sections and I will assume that I am talking with respect to the project root directory.

php mysql pagination

MySQL Table

I want to show data from database and depending upon the number of records in the table, the pagination will be built dynamically.

Therefore I need to create table in MySQL server.

Create a table called category. This table holds data for category. You can have any table and accordingly you need to customize your source code.

MySQL 5.x

For MySQL 5 version you can use the following table structure:

DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
	`category_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`category_name` varchar(50) COLLATE latin1_general_ci NOT NULL,
	`category_link` varchar(255) COLLATE latin1_general_ci NOT NULL,
	`parent_id` int(10) unsigned NOT NULL DEFAULT '0',
	`sort_order` int(11) NOT NULL DEFAULT '0',
	PRIMARY KEY (`category_id`),
	UNIQUE KEY `unique` (`category_name`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

MySQL 8.x

For MySQL 8 version you can use the following structure of the table, though the above table structure can be used with some ignorable warnings.

CREATE TABLE `category` (
	`category_id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
	`category_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
	`category_link` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
	`parent_id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
	`sort_order` int COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
	PRIMARY KEY (`category_id`),
	UNIQUE KEY `unique` (`category_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Inserting Data

In order to test the application right away you need to have some data into the table. So insert some dummy data to test the pagination application.

insert into `category`(`category_id`,`category_name`,`category_link`,`parent_id`,`sort_order`) values (1,'Home','home',0,0),
(2,'Tutorials','tutorials',0,1),
(3,'Java','java',2,1),
(4,'Liferay','liferay',2,1),
(5,'Frameworks','frameworks',0,2),
(6,'JSF','jsf',5,2),
(7,'Struts','struts',5,2),
(8,'Spring','spring',5,2),
(9,'Hibernate','hibernate',5,2),
(10,'Webservices','webservices',0,3),
(11,'REST','rest',10,3),
(12,'SOAP','soap',10,3),
(13,'Contact','contact',0,4),
(14,'About','about',0,5);

Pagination Functions

Before proceeding next please look at the database connection tutorial

I want to create a PHP file called pagination.php that will contain all required functions for pagination in our application. The current page number is retrieved from the query string of the URL. Everytime the database is queried for the page number to fetch the results on the page.

<?php

/* * ************************
Paging Functions
* ************************* */

/*
get page number from query string
*/
function getPagingQuery($sql, $itemPerPage = 10) {
	if (isset($_GET['page']) && (int) $_GET['page'] > 0) {
		$page = (int) $_GET['page'];
	} else {
		$page = 1;
	}
	
	// start fetching from this row number
	$offset = ($page - 1) * $itemPerPage;
	
	return $sql . " LIMIT $offset, $itemPerPage";
}

/*
Get the links to navigate between one result page to another.
Supply a value for $strGet if the page url already contain some
GET values for example if the original page url is like this :
http://localhost/index.php?page=2
use "c=12" as the value for $strGet. But if the url is like this :
http://localhost/index.php
then there's no need to set a value for $strGet
*/	
function getPagingLink($sql, $itemPerPage = 10, $strGet = '') {
	$result = dbQuery($sql);
	$pagingLink = '';
	$totalResults = dbNumRows($result);
	$totalPages = ceil($totalResults / $itemPerPage);
	
	// how many link pages to show
	$numLinks = 10;
	
	// create the paging links only if we have more than one page of results
	if ($totalPages > 1) {
		$self = 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'];
		
		if (isset($_GET['page']) && (int) $_GET['page'] > 0) {
			$pageNumber = (int) $_GET['page'];
		} else {
			$pageNumber = 1;
		}
		
		// print 'previous' link only if we're not
		// on page one
		if ($pageNumber > 1) {
			$page = $pageNumber - 1;
			if ($page > 1) {
				if ($strGet == '') {
					$prev = " <a href=\"$self?page=$page\">‹ Prev </a> ";
				} else {
					$prev = " <a href=\"$self?page=$page&$strGet\">‹ Prev </a> ";
				}
			} else {
				if ($strGet != '') {
					$prev = " <a href=\"$self?$strGet\">‹ Prev </a> ";
				} else {
					$prev = " <a href=\"$self\">‹ Prev </a> ";
				}
			}
			
			if ($strGet == '') {
				$first = " <a href=\"$self\">« First </a> ";
			} else {
				$first = " <a href=\"$self?$strGet\">« First </a> ";
			}
		} else {
			$prev = ''; // we're on page one, don't show 'previous' link
			$first = ''; // nor 'first page' link
		}
		
		// print 'next' link only if we're not
		// on the last page
		if ($pageNumber < $totalPages) {
			$page = $pageNumber + 1;
			if ($strGet == '') {
				$next = " <a href=\"$self?page=$page\"> Next ›</a> ";
				$last = " <a href=\"$self?page=$totalPages\"> Last »</a> ";
			} else {
				$next = " <a href=\"$self?page=$page&$strGet\"> Next ›</a> ";
				$last = " <a href=\"$self?page=$totalPages&$strGet\"> Last »</a> ";
			}
		} else {
			$next = ''; // we're on the last page, don't show 'next' link
			$last = ''; // nor 'last page' link
		}

		$start = $pageNumber - ($pageNumber % $numLinks) + 1;
		$end = $start + $numLinks - 1;
		$end = min($totalPages, $end);
		$pagingLink = array();
		
		for ($page = $start; $page <= $end; $page++) {
			if ($page == $pageNumber) {
				$pagingLink[] = "<span class='selected'> $page </span>"; // no need to create a link to current page
			} else {
				if ($page == 1) {
					if ($strGet != "") {
						$pagingLink[] = " <a href=\"$self?$strGet\">$page</a> ";
					} else {
						$pagingLink[] = " <a href=\"$self\">$page</a> ";
					}
				} else {
					if ($strGet != "") {
						$pagingLink[] = " <a href=\"$self?page=$page&$strGet\">$page</a> ";
					} else {
						$pagingLink[] = " <a href=\"$self?page=$page\">$page</a> ";
					}
				}
			}
		}
		
		$pagingLink = implode('<span class="seperator"> | </span>', $pagingLink);
		
		// return the page navigation link
		$pagingLink = $first . $prev . $pagingLink . $next . $last;
	}
	
	return $pagingLink;
}

/*
Join up the key value pairs in $_GET
into a single query string
*/
function queryString() {
	$qString = array();
	foreach ($_GET as $key => $value) {
		if (trim($value) != '') {
			$qString[] = $key . '=' . trim($value);
		} else {
			$qString[] = $key;
		}
	}
	
	$qString = implode('&', $qString);
	return $qString;
}

/*
* End of file pagination.php
*/

View File

I will create a PHP file called index.php, where the pagination example is displayed.

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Effective Pagination Example in PHP and MySQL</title>
    </head>
    <body>
		<?php
			require_once 'config.php';
		?>
		<table>
			<thead>
				<tr>
					<th>Category ID</th>
					<th>Category Name</th>
					<th>Category Link</th>
					<th>Parent ID</th>
					<th>Sort Order</th>
				</tr>
			</thead>
			<?php
				$sql = "SELECT * FROM category";
				$itemPerPage = 5;
				$result = dbQuery(getPagingQuery($sql, $itemPerPage));
				$pagingLink = getPagingLink($sql, $itemPerPage);
			?>
			<tbody>
				<?php
					if (dbNumRows($result) > 0) {
						while ($row = dbFetchAssoc($result)) {
							extract($row);
							echo '<tr>';
							echo '<td>' . $row['category_id'] . '</td>';
							echo '<td>' . $row['category_name'] . '</td>';
							echo '<td>' . $row['category_link'] . '</td>';
							echo '<td>' . $row['parent_id'] . '</td>';
							echo '<td>' . $row['sort_order'] . '</td>';
							echo '</tr>';
						}
						if ($pagingLink) {
							echo '<div>' . $pagingLink . '</div>';
						}
					} else {
						echo 'No result found';
					}
				?>
			</tbody>
		</table>
	</body>
</html>

Testing the Application

Once you run the index.php using http://localhost/php-mysql-pagination/index.php or http://localhost/php-mysql-pagination/. You can see the following output in the browser.

effective pagination example in PHP and MySQL

If you click on the Last>> link, then you will see the following page. Now first and prev links are displayed.

effective pagination example in PHP and MySQL

You can also click on any numeric link, for example, on 2:

php mysql pagination

Now you would be able to navigate different pages using First, Prev, Next, Last and numeric value links.

Source Code

Download

1 thought on “Create An Effective Pagination In PHP And MySQL”

Leave a Reply

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