Simple Pagination With PHP & MYSQL

Data Pagination is one of the common tasks we have in web applications. When we get huge data from the database instead of showing all of it at once we will choose different ways to display that data. One such technique is PHP pagination.
In this tutorial, we will use PHP and MySQL to create simple pagination script. Pagination lets you to paging your data into a number of pages. I simplified the code as much as possible. In the below example I have taken Country database with its country code. Please check on below link to check the demo. The countries database is included in countries.sql in the download file.

Simple Pagination With PHP & MYSQL


demo

download

We will create these pages. -config.php (Database Configuration) -index.php -paginate.php Let's First create database table which we are going to use in our example.

DataBase

CREATE TABLE IF NOT EXISTS `countries` (  
`ccode` varchar(2) NOT NULL DEFAULT '', 
`country` varchar(200) NOT NULL DEFAULT '', 
PRIMARY KEY (`ccode`)  
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

The above is simple countries table. Just execute the above command in your MySQL command prompt or phpmyadmin area.

config.php

$mysql_hostname = "host"; 
$mysql_user = "usr"; 
$mysql_password = "pass"; 
$mysql_database = "db"; 
$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die("Opps some thing went wrong"); 
mysql_select_db($mysql_database, $bd) or die("Error on database connection"); 

This is the configuration file. Here you need to setup your database details.

index.php

<?php  
include('config.php'); //include of db config file  
include ('paginate.php'); //include of paginat page  
$per_page = 5; // number of results to show per page 
$result = mysql_query("SELECT * FROM countries"); 
$total_results = mysql_num_rows($result); 
$total_pages = ceil($total_results / $per_page);//total pages we going to have 
//-------------if page is setcheck------------------// 
if (isset($_GET['page'])) {  
$show_page = $_GET['page']; //current page 
if ($show_page > 0 && $show_page <= $total_pages) {  
$start = ($show_page - 1) * $per_page; 
$end = $start + $per_page; 
} else { 
// error - show first set of results 
$start = 0; 
$end = $per_page; 
} 
} else { 
// if page isn't set, show first set of results 
$start = 0; 
$end = $per_page; 
} 
// display pagination 
$page = intval($_GET['page']); 
$tpages=$total_pages; 
if ($page <= 0)  
$page = 1; 

?> 

Here we included two files config.php and paginate.php in first two lines. we can set $perpage_ to how many records you want to show on a page. We can calculate total pages depending on total results and perpage. Depending on the _$GET['page']_ we can have start and end records.

index.php(Displaying Data)

<?php  
$reload = $_SERVER['PHP_SELF'] . "?tpages=" . $tpages; 
echo '<div class="pagination"><ul>';  
if ($total_pages > 1) {  
echo paginate($reload, $show_page, $total_pages);  
} 
echo "</ul></div>";  
// display data in table 
echo "<table class='table table-bordered'>";  
echo "<thead><tr><th>country code</th> <th>Country Name</th></tr></thead>";  
// loop through results of database query, displaying them in the table 
for ($i = $start; $i < $end; $i++) {  
// make sure that PHP doesn't try to show results that don't exist 
if ($i == $total_results) {  
break;  
} 
// echo out the contents of each row into a table 
echo "<tr " . $cls . ">";  
echo '<td>' . mysql_result($result, $i, 'ccode') . '</td>';  
echo '<td>' . mysql_result($result, $i, 'country') . '</td>';  
echo "</tr>";  
} 
// close table> 
echo "</table>";  
// pagination 
?> 

This part will loop from $start to $end variable. The records will be displayed on the page.

paginate.php

function paginate($reload, $page, $tpages) {  
$adjacents = 2; 
$prevlabel = "&lsaquo; Prev"; 
$nextlabel = "Next &rsaquo;"; 
$out = ""; 
// previous 
if ($page == 1) {  
$out.= "<span>".$prevlabel."</span>\n"; 
} elseif ($page == 2) { 
$out.="<li><a href=\"".$reload."\">".$prevlabel."</a>\n</li>"; 
} else { 
$out.="<li><a href=\"".$reload."&page=".($page - 1)."\">".$prevlabel."</a>\n</li>"; 
} 
$pmin=($page>$adjacents)?($page - $adjacents):1; 
$pmax=($page<($tpages - $adjacents))?($page + $adjacents):$tpages; 
for ($i = $pmin; $i <= $pmax; $i++) {  
if ($i == $page) {  
$out.= "<li class=\"active\"><a href=''>".$i."</a></li>\n"; 
} elseif ($i == 1) { 
$out.= "<li><a href=\"".$reload."\">".$i."</a>\n</li>"; 
} else { 
$out.= "<li><a href=\"".$reload. "&page=".$i."\">".$i. "</a>\n</li>"; 
} 
} 

if ($page<($tpages - $adjacents)) {  
$out.= "<a style='font-size:11px' href=\"" . $reload."&page=".$tpages."\">" .$tpages."</a>\n"; 
} 
// next 
if ($page < $tpages) {  
$out.= "<li><a href=\"".$reload."&page=".($page + 1)."\">".$nextlabel."</a>\n</li>"; 
} else { 
$out.= "<span style='font-size:11px'>".$nextlabel."</span>\n"; 
} 
$out.= ""; 
return $out;  
} 

php This is our main code which created the pagination HTML code and this code will be called from our index.php. In this first, we need to set the $adjacents which is for how many li u want to display beside your current selected page. The remaining code will generate the li of the links.

Get posts by email

Like what you're reading? Get these posts delivered to your inbox.