Pagination using PHP and MYSQL is a very important concept which is widely used while developing web applications in PHP. Lets suppose that if we want to list the records of 1000 students then it may not be possible to display all the records in a single page. If we try to display all the records in a single page then it will take a lot of time to load all the records. Hence, we can display 10, 20, 30 or any number of records in a page using the technique of pagination. For e.g. if we display 100 records in a page then it will display total a total of 10 pages for 1000 records. Therefore the technique of pagination is very useful and widely used while developing applications.
Lets see the code for implementing pagination in PHP.
First create a database named php and run the mysql script given below using phpmyadmin.
CREATE TABLE IF NOT EXISTS `tblcountry` (
`id` int(4) unsigned NOT NULL AUTO_INCREMENT,
`countryname` varchar(255) DEFAULT NULL,
`abbreviation` varchar(3) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=65 ;
INSERT INTO `tblcountry` (`id`, `countryname`, `abbreviation`) VALUES
(1, 'AALBORG', 'AAL'),
(2, 'AARHUS', 'AAR'),
(3, 'ABERDEEN SD', 'ABR'),
(4, 'ABERDEEN UK', 'ABZ'),
(5, 'ABIDJAN', 'ABJ'),
(6, 'ABILENE', 'ABI'),
(7, 'ABU DHABI', 'AUH'),
(8, 'ABU SIMEEL', 'ABS'),
(9, 'ABUJA', 'ABV'),
(10, 'ACAPULCO', 'ACA'),
(11, 'ACCRA', 'ACC'),
(12, 'ADANA', 'ADA'),
(13, 'ADDIS ABABA', 'ADD'),
(14, 'ADELAIDE', 'ADL'),
(15, 'ADEN', 'ADE');
Now lets explore the php code to display the above records in multiple pages using the pagination technique. You can save the below file as pagination.php
<?php
mysql_connect('localhost','root','');
mysql_select_db('php');
$res = mysql_query("select * from tblcountry");
$totrec = mysql_num_rows($res);
$pagesize = 5;
$totalpages = ceil($totrec/$pagesize);
if(isset($_GET['resultpage']) && $_GET['resultpage'] > 1 ) {
$offset = $_GET['resultpage'] * $pagesize - $pagesize;
}else {
$offset = 0;}
$sql = "select * from tblcountry limit ". $offset . ",". $pagesize;
$result = mysql_query($sql);?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Pagination in PHP</title></head>
<body>
<table border="1">
<?php while($data=mysql_fetch_array($result)) {?>
<tr>
<td><?=$data['id'];?></td>
<td><?=$data['countryname'];?></td>
</tr>
<?php }//end of while ?>
</table>
<?php
if($totalpages > 1) {
for($i=1; $i<=$totalpages; $i++) {
?>
<a href="pagination.php?resultpage=<?=$i;?>"><?=$i;?></a>
<?php
}//end of for loop
}//end of if
?></body>
</html>
The variable $totrec holds total number of records that we have in the table.
The variable $pagesize holds the page size i.e. total number of records that we are going to display in a page. It can be changed easily as per our requirement without affecting the code.
The variable $totalpages holds the total number of pages that is required to display the records. Total Pages is obtained by dividing total records with page size.
If we are in the first page then the value of offset will be 0 (zero), Hence first five records will be shown in the first page.
When the user clicks on the page number 2, the value of offset will be 2 (page number) * 5 (pagesize) – 5 (pagesize), Hence records 6 – 10 will be displayed as per the query.
Download the complete code for pagination using php and mysql here: Pagination using PHP/MYSQL (178)
