
Pagination is a very important and useful section of my website. So, In this article, I am going to show you create a pagination with PHP and MySql. You have seen that every website must have a pagination concept.
Contents
What is Pagination?
Pagination is the process of separating content into discrete pages. That’s mean an entire page divided into several parts. Then pagination provides high speed and low risk of web page loading. Let’s start with this example to solve the pagination problem.
Project Files
First of all, you need to connect to the MySQL database with your PHP.
Config File
Connecting to Database is often a required task for showing data in any PHP file.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "mydatabase";
// Create connection
$connection = mysqli_connect($servername, $username, $password, $database);
// Check connection
if (!$connection) {
die("Connection failed: " . $connection->connect_error);
}
function close_db(){
mysqli_close($connection);
}
?>
Now, you need some more information for pagination like how many records you need in a page, pagination style, etc. For pagination style we need to create some logic where you must pass these values:
- request_url
- limit
- total_page
- current_page
Pagination Method
function create_pagination($request_url, $limit, $total_page, $current_page){
if($total_page > 1 && $current_page <= $total_page){
if($current_page <= $total_page && $current_page > 1)
$prv_page = $current_page-1;
else
$prv_page = 1;
$url = setURL($request_url,'page',$prv_page);
$pagination .= '<a href="'.$url.'">‹</a>';
if($current_page<=4)
$from = 1;
if($current_page>=4){
$from = $current_page-2;
$url = setURL($request_url,'page',1);
$pagination .= '<a href="'.$url.'">1</a>';
if($from >= 3)
$pagination .= '... ';
}
if($current_page == $total_page || $total_page <= 4)
$to = $total_page;
else{
$to = $from+4;
if($current_page==$total_page-1)
$to--;
}
for($i = $from; $i <= $to; $i++){
$url = setURL($request_url,'page',$i);
$pagination .= '<a href="'.$url.'"';
if($i==$current_page)
$pagination .= ' class="current">';
else
$pagination .= ' >';
$pagination .= $i.'</a>';
}
if($to < $total_page-1)
$pagination .= '... ';
if($to != $total_page){
$url = setURL($request_url,'page',$total_page);
$pagination .= '<a href="'.$url.'">'.$total_page.'</a>';
}
if($current_page < $total_page && $current_page >= 0)
$next_page = $current_page+1;
else
$next_page = $total_page;
$url = setURL($request_url,'page',$next_page);
$pagination .= '<a href="'.$url.'">›</a>';
}
return $pagination;
}
function setURL($url, $key, $value) {
$url = removeqsvar($url, $key);
$separator = (parse_url($url, PHP_URL_QUERY) == NULL) ? '' : '&';
$query = $key."=".$value;
$url .= $separator . $query;
return $url;
}
function removeqsvar($url, $varname) {
list($urlpart, $qspart) = array_pad(explode('?', $url), 2, '');
parse_str($qspart, $qsvars);
unset($qsvars[$varname]);
$newqs = http_build_query($qsvars);
return $urlpart . '?' . $newqs;
}
In next step make your MySQL query to fetch your total no of records and calculate total no of pages according to your page per item limit.
$mysql_query = mysqli_query($connection, "Select id from country");
$total_rows = mysqli_num_rows($mysql_query);
$limit = 10;
$total_page = ceil($total_rows/$limit);
Fetching records from MySQL
Now you create your MySQL record query like this:
<?php
$mysql_query = mysqli_query($connection, "Select * from country Limit $offset, $limit");
echo "<tr style='background-color:#999999'>";
while($mysql_query_fields = mysqli_fetch_field($mysql_query)){
$mysql_fields[] = $mysql_query_fields->name;
echo "<th align='left'>".ucfirst($mysql_query_fields->name)."</th>";
}
echo "</tr>";
while($mysql_rows = mysqli_fetch_array($mysql_query)){
echo "<tr>";
foreach($mysql_fields as $fields){
echo "<td>".$mysql_rows[$fields]."</td>";
}
echo "</tr>";
}
?>
CSS file
For attractive pagination with PHP and MySql link you need some stylesheet for CSS:
@charset "utf-8";
/* CSS Document */
.navigation a {
font-family: 'Droid Sans', verdana;
font-weight: bold;
font-size: 18px;
padding-right: 10px;
color: #494949;
text-decoration: none;
cursor: pointer;
}
.navigation .current {
color: #FF0000;
}
Example
Finally, your last page should be like this:
<!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">
<?php
require_once('config.php');
function create_pagination($request_url, $limit, $total_page, $current_page){
if($total_page > 1 && $current_page <= $total_page){
if($current_page <= $total_page && $current_page > 1)
$prv_page = $current_page-1;
else
$prv_page = 1;
$url = setURL($request_url,'page',$prv_page);
$pagination .= '<a href="'.$url.'">‹</a>';
if($current_page<=4)
$from = 1;
if($current_page>=4){
$from = $current_page-2;
$url = setURL($request_url,'page',1);
$pagination .= '<a href="'.$url.'">1</a>';
if($from >= 3)
$pagination .= '... ';
}
if($current_page == $total_page || $total_page <= 4)
$to = $total_page;
else{
$to = $from+4;
if($current_page==$total_page-1)
$to--;
}
for($i = $from; $i <= $to; $i++){
$url = setURL($request_url,'page',$i);
$pagination .= '<a href="'.$url.'"';
if($i==$current_page)
$pagination .= ' class="current">';
else
$pagination .= ' >';
$pagination .= $i.'</a>';
}
if($to < $total_page-1)
$pagination .= '... ';
if($to != $total_page){
$url = setURL($request_url,'page',$total_page);
$pagination .= '<a href="'.$url.'">'.$total_page.'</a>';
}
if($current_page < $total_page && $current_page >= 0)
$next_page = $current_page+1;
else
$next_page = $total_page;
$url = setURL($request_url,'page',$next_page);
$pagination .= '<a href="'.$url.'">›</a>';
}
return $pagination;
}
function setURL($url, $key, $value) {
$url = removeqsvar($url, $key);
$separator = (parse_url($url, PHP_URL_QUERY) == NULL) ? '' : '&';
$query = $key."=".$value;
$url .= $separator . $query;
return $url;
}
function removeqsvar($url, $varname) {
list($urlpart, $qspart) = array_pad(explode('?', $url), 2, '');
parse_str($qspart, $qsvars);
unset($qsvars[$varname]);
$newqs = http_build_query($qsvars);
return $urlpart . '?' . $newqs;
}
$mysql_query = mysqli_query($connection, "Select id from country");
$total_rows = mysqli_num_rows($mysql_query);
$limit = 10;
$total_page = ceil($total_rows/$limit);
if( isset($_GET{'page'} ) ) {
$current_page = $_GET['page'];
if($current_page <=0)
$current_page = 1;
else if($current_page > $total_page)
$current_page = $total_page;
$offset = $limit * ($current_page-1);
}
else{
$current_page = 1;
$offset = 0;
}
$request_url = basename($_SERVER['REQUEST_URI']);
$pagination = create_pagination($request_url, $limit, $total_page, $current_page);
?>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Pagination Demo</title>
<link rel="stylesheet" href="lb_css.css" type="text/css" />
</head>
<body>
<div class="navigation" style="color:#333333; text-align:right; width:85%"><?php echo $pagination; ?></div>
<table width="75%" align="center" style="background-color:#F8F8F8" cellpadding="7" cellspacing="3">
<?php
$mysql_query = mysqli_query($connection, "Select * from country Limit $offset, $limit");
echo "<tr style='background-color:#999999'>";
while($mysql_query_fields = mysqli_fetch_field($mysql_query)){
$mysql_fields[] = $mysql_query_fields->name;
echo "<th align='left'>".ucfirst($mysql_query_fields->name)."</th>";
}
echo "</tr>";
while($mysql_rows = mysqli_fetch_array($mysql_query)){
echo "<tr>";
foreach($mysql_fields as $fields){
echo "<td>".$mysql_rows[$fields]."</td>";
}
echo "</tr>";
}
?>
</table>
<div class="navigation" style="color:#333333; text-align:right; width:85%"><?php echo $pagination; ?></div>
</body>
<?php
close_db();
?>
</html>
Conclusion
To make your page easier to scroll and reduce load time then you must try this example of pagination with PHP and MySql. Because this example gives you a short method of implementing your pagination with good display a result.
Did you find this page helpful?
X