Last Update - 02 Jul 2020

Pagination in php

Pagination in php

Pagination is a very important and useful task in development. Pagination provides high speed and low risk of web page loading. Let's start with this example to solve the pagination problem.

Top 50 MySQL Interview Questions and Answers

First of all, you need to connect to the MySQL database with your PHP:

config.php:

<?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

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

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>";
    }
?>

For attractive pagination link you need some style-sheet 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;
}

Finally, your last page should be like this:

On-Page SEO Techniques in 2020

<!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>

Did you find this page helpful? X