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 we don’t need to scroll the page at bottom. Using pagination we 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.

Recommended Reading: Creating Effective Pagination using JSF 2.

I will create everything what we 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.

Prerequisites

PHP 7.0.15, Apache 2.4, MySQL 5.6

database connection tutorial

Example with Source Code

Creating Project Directory

We need to create a project directory, where we will keep source code for the application.

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

Creating MySQL Table

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

Therefore we 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.

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;

The corresponding image for the above table with data is given below:

effective pagination example in PHP and MySQL

Inserting Data

In order to test our application we need to have some data into the table. So insert some dummy data to test the example.

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);

Creating Pagination Functions

Before proceeding next please look at the database connection tutorial

We want to create a PHP file called pagination.php that will contain all required functions for pagination in our application.

<?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 common.php
*/

Creating View File

We 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. You can see the following output in the browser.

effective pagination example in PHP and MySQL

Thanks for your reading.

Create an effective pagination in PHP and MySQL

Leave a Reply

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

Scroll to top