PHP


How to create Pagination with PHP and MySql

Pagination with PHP and MySql

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.

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.

You may also like

Login with Facebook using PHP
How to Convert Text to Image using PHP
Generate QR code in PHP using google API
Convert multidimensional array to XML file in PHP
cURL example in PHP
How to connect MySQL database using PHP

Published by Tirthraj Pandey

Professional Android app Developer, Full Stack Web Developer, Blogger, Entrepreneur And Founder of Coding Issue. I am always trying to learn new things or new possible ways to do things.

Did you find this page helpful?

X

Leave a Reply

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

*

*

*

© 2020 Coding Issue. All rights reserved | Proudly powered by Coding Issue