Download Pagination in PHP and MySql With Example

Abhigyan Singh 23rd Sep 2020

This tutorial will explain about making Pagination in PHP and MySql. Pagination technique is very important in Web Application where users need to display more content on one page. So using this Pagination technique we will divide the content into multiple pages and display all those content pages wise.

We can explain it by a simple example below:

Suppose, you are running an e-commerce website and there are a thousand products available in each category. Now if you will display all the products on a single page, it will be very slow and not user friendly as well. So the best technique to display the product using pagination technique. You can break thousand products into multiple pages.

This pagination technique creates separate url for each page. Here we are using PHP to create Pagination and Mysql to fetch data from the table. This pagination technique is very important where we need to create more URL. We can increase the number of URL by using the Paging technique.

What is pagination in a web application?

Pagination is a technique to divide content into several pages. Here we can assign each page a separate URL. By Clicking that URL or Page Number, the user can view this Page. For every page, we assign an incremental Page number.

In a simple word, pagination means showing all the result set into multiple pages instead of a single one page. It will also reduce the database and server load by dividing data into multiple pages.

You can read my other article related to Load More Results in PHP, Ajax from database . This is another method to create pagination without creating multiple pages.

I have created another tutorial to create Pagination in CakePHP3 and Mysql. You can check if you are familiar with CakePHP3.

How to make pagination in PHP

Now we will explain our code on how to implement pagination using PHP and mysql. Some of the major highlights are given below:

  1. Here we will use Mysql LIMIT clause to limit the number of records.
  2. Fetch those records using PHP script.
  3. Show those results set on our web page.
  4. We will also create the pagination number based on the total count of records divided by the number of records displaying in each page
  5. We will also create a unique url for each page to remove duplicate content issue.

Advantage of pagination in a web application:

  1. Improve user experience by reducing the server load time.
  2. Helps to increase the website pages by dividing it in multiple pages
  3. Better control while browsing the website compare to an infinite scroll. you can easily reach to the footer.

Follow the below script to create pagination in PHP. We will explain each code in detail which comes in the below article.

Now you are at the stage of implementing pagination in php, So I have some more articles that can help you to create your website more effective.

Here we will create four files named:
1. db_config.php
2. index.php
3. function.php
4. style.css

1. db_config.php

This is the file for connecting to the database. You need to configure as per your connectivity setting.

<?php

define ("DB_HOST", "localhost"); // Your database host name
define ("DB_USER", "root"); // Your database user
define ("DB_PASS",""); // Your database password
define ("DB_NAME",""); // Your database name

$link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection.");
$db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database");

?>

2. index.php

This is the main file where we displaying our pagination.

In this page, we first set a LIMIT to display the page. Then make a query to display all the content with the initial page limit and then after every click of the page we will get a page number and according to that page, the previous query again executes. This process will run until the page end.

<div class="navi">
<?php

//Make Database connectivity
include_once "dbConfig.php";

include_once "function.php";

if(isset($_GET["page"]))
$page = (int)$_GET["page"];
else
$page = 1;

$setLimit = 10;
$pageLimit = ($page * $setLimit) - $setLimit;

// Your SQL query go here. This query will display all record by setting the Limit.

$sql = "SELECT * FROM TABLE_NAME where status = 1 LIMIT ".$pageLimit." , ".$setLimit;
$query = mysql_query($sql);

while ($rec = mysql_fetch_array($query)) {
?>
<div class="show"><a href="https://discussdesk.com//<?php echo $rec["url"];?>.htm" target="_blank"><?php echo $rec['title'];?></a></div>
<?php } ?>
</div>

<?php
// Call the Pagination Function to load Pagination.

echo displayPaginationBelow($setLimit,$page);
?>

Here we have the page limit “$setLimit = 10”.

3. function.php

Here we have created a function to make pagination and index.php calls this function to display the pagination. This function is responsible for creating page numbers, page url, and the number of articles that will display on one page.

function displayPaginationBelow($per_page,$page){
$page_url="?";
$query = "SELECT COUNT(*) as totalCount FROM TABLE_NAME where status = 1";
$rec = mysql_fetch_array(mysql_query($query));
$total = $rec['totalCount'];
$adjacents = "2";

$page = ($page == 0 ? 1 : $page);
$start = ($page - 1) * $per_page;

$prev = $page - 1;
$next = $page + 1;
$setLastpage = ceil($total/$per_page);
$lpm1 = $setLastpage - 1;

$setPaginate = "";
if($setLastpage > 1)
{
$setPaginate .= "<ul class='setPaginate'>";
$setPaginate .= "<li class='setPage'>Page $page of $setLastpage</li>";
if ($setLastpage < 7 + ($adjacents * 2))
{
for ($counter = 1; $counter <= $setLastpage; $counter++)
{
if ($counter == $page)
$setPaginate.= "<li><a class='current_page'>$counter</a></li>";
else
$setPaginate.= "<li><a href='{$page_url}page=$counter'>$counter</a></li>";
}
}
elseif($setLastpage > 5 + ($adjacents * 2))
{
if($page < 1 + ($adjacents * 2))
{
for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
{
if ($counter == $page)
$setPaginate.= "<li><a class='current_page'>$counter</a></li>";
else
$setPaginate.= "<li><a href='{$page_url}page=$counter'>$counter</a></li>";
}
$setPaginate.= "<li class='dot'>...</li>";
$setPaginate.= "<li><a href='{$page_url}page=$lpm1'>$lpm1</a></li>";
$setPaginate.= "<li><a href='{$page_url}page=$setLastpage'>$setLastpage</a></li>";
}
elseif($setLastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
{
$setPaginate.= "<li><a href='{$page_url}page=1'>1</a></li>";
$setPaginate.= "<li><a href='{$page_url}page=2'>2</a></li>";
$setPaginate.= "<li class='dot'>...</li>";
for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
{
if ($counter == $page)
$setPaginate.= "<li><a class='current_page'>$counter</a></li>";
else
$setPaginate.= "<li><a href='{$page_url}page=$counter'>$counter</a></li>";
}
$setPaginate.= "<li class='dot'>..</li>";
$setPaginate.= "<li><a href='{$page_url}page=$lpm1'>$lpm1</a></li>";
$setPaginate.= "<li><a href='{$page_url}page=$setLastpage'>$setLastpage</a></li>";
}
else
{
$setPaginate.= "<li><a href='{$page_url}page=1'>1</a></li>";
$setPaginate.= "<li><a href='{$page_url}page=2'>2</a></li>";
$setPaginate.= "<li class='dot'>..</li>";
for ($counter = $setLastpage - (2 + ($adjacents * 2)); $counter <= $setLastpage; $counter++)
{
if ($counter == $page)
$setPaginate.= "<li><a class='current_page'>$counter</a></li>";
else
$setPaginate.= "<li><a href='{$page_url}page=$counter'>$counter</a></li>";
}
}
}

if ($page < $counter - 1){
$setPaginate.= "<li><a href='{$page_url}page=$next'>Next</a></li>";
$setPaginate.= "<li><a href='{$page_url}page=$setLastpage'>Last</a></li>";
}else{
$setPaginate.= "<li><a class='current_page'>Next</a></li>";
$setPaginate.= "<li><a class='current_page'>Last</a></li>";
}

$setPaginate.= "</ul>\n";
}


return $setPaginate;
}

4. style.css

This is css file that gives some design to the pagination. You can alter it as per your requirement.

.navi {
width: 500px;
margin: 5px;
padding:2px 5px;
border:1px solid #eee;
}

.show {
color: blue;
margin: 5px 0;
padding: 3px 5px;
cursor: pointer;
font: 15px/19px Arial,Helvetica,sans-serif;
}
.show a {
text-decoration: none;
}
.show:hover {
text-decoration: underline;
}

ul.setPaginate li.setPage{
padding:15px 10px;
font-size:14px;
}

ul.setPaginate{
margin:0px;
padding:0px;
height:100%;
overflow:hidden;
font:12px 'Tahoma';
list-style-type:none;
}

ul.setPaginate li.dot{padding: 3px 0;}

ul.setPaginate li{
float:left;
margin:0px;
padding:0px;
margin-left:5px;
}

ul.setPaginate li a
{
background: none repeat scroll 0 0 #ffffff;
border: 1px solid #cccccc;
color: #999999;
display: inline-block;
font: 15px/25px Arial,Helvetica,sans-serif;
margin: 5px 3px 0 0;
padding: 0 5px;
text-align: center;
text-decoration: none;
}

ul.setPaginate li a:hover,
ul.setPaginate li a.current_page
{
background: none repeat scroll 0 0 #0d92e1;
border: 1px solid #000000;
color: #ffffff;
text-decoration: none;
}

ul.setPaginate li a{
color:black;
display:block;
text-decoration:none;
padding:5px 8px;
text-decoration: none;
}

This is just a function to create Pagination according to the Page limit and query. In this function, we first get the total count of the row in the mysql table. Then we calculate the number of pages according to total count and page limit and set a unique URL for each page number and then display the page number.

That's all Enjoy with pagination code. Download it and if any problem occurs please let me know in Comment Section.

Also, read This: (Load More Results in PHP, Ajax from database)

If you like this Post Useful, please follow me.

Authored By Abhigyan Singh

He is a continuous blogger and has blogged on different topic. He loves to surf Internet and always trying to get new Idea about new Technology and Innovations and sharing these great information to all the technology lovers.

Also on DiscussDesk