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.

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.

Create MySQL table

USE `cdcol`;
/*Table structure for table `category` */
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;

 

effective pagination example in PHP and MySQL
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);

 

Before proceeding next please look at the database connection tutorial

Common pagination functions are written in pagination.php

<?php
/* * ************************
Paging Functions
* ************************* */
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
*/

 

index.php file where the pagination example is displayed

<?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>';
}
?>
</tbody>
</table>
<?php
} else {
echo 'No result found';
}
?>

 

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. Please do not forget to leave a comment.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials | TwitterFacebook Google PlusLinkedin | Reddit | Email Me

Leave a Reply

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