Creating Pagination with PHP and MySql

We’ll use PHP and MySQL to build pagination in this tutorial. Perhaps thousands or even millions of records could be returned by your SQL query. And it’s not a smart idea to have all of those results on one page. The solution is, we can break this up into numerous pages through PHP pagination.

What is Pagination?

For me, one of the most challenging aspects of being a developer is dealing with big amounts of data and making it understandable. When faced with endless columns of data, whether it’s a customer list for a major corporation or products collection, it may be hard and stressful.

The solution is, Pagination!

All of your search results are broken up into many pages instead of being displayed all at once. Otherwise, it will add an outsized amount of text to the page, making it incredibly slow to load.

Starting to create Pagination with PHP and MySql

The LIMIT clause in MySQL comes in handy when we’re building a pagination functionality. It takes an OFFSET parameter and a number of records to return from the database as its second argument.

Creating pagination in PHP is easy if you follow this guide properly.

Get the current page number

The $_GET Array will be used to retrieve the current page number. It’s important to know that if it’s missing, the default page number will be 1. As long as the URL is pagination.php?pageno=3 then the page number will be 3, if it is not, it will default to page 1.

  if (isset($_GET['page_no'])) {
    $page_no = $_GET['page_no'];
} else {
    $page_no = 1;
} 

2. The formula for pagination in PHP

You can always manage the number of records to be displayed in a page by changing the value of $no_of_records_per_page variable.

  $no_of_records_per_page = 10;
$offset = ($page_no-1) * $no_of_records_per_page;  

3. Determine the total number of pages.

  $total_pages_sql = "SELECT COUNT(*) FROM table";
$result = mysqli_query($conn,$total_pages_sql);
$total_rows = mysqli_fetch_array($result)[0];
$total_pages = ceil($total_rows / $no_of_records_per_page); 

4. Constructing the actual SQL Query.

 $sql = "SELECT * FROM table LIMIT $offset, $no_of_records_per_page"; 
 

The OFF SET value is also frequently used in conjunction with the LIMIT keyword. The OFF SET value allows us to pick which row to retrieve data from.

If we want to acquire a restricted amount of items starting from the centre of the rows, we can use the LIMIT keyword in conjunction with the offset value.

5. Pagination buttons

These buttons are shown to visitors as Next Page and Previous Page, allowing them to browse across your sites with ease. Here I am using the pagination button from Bootstrap, but you may use your own buttons if you choose.

 <ul id="pagi">
    <li><a href="?page_no=1">First</a></li>
    <li class="<?php if($page_no <= 1){ echo 'disabled'; } ?>">
        <a href="<?php if($page_no <= 1){ echo '#'; } else { echo "?page_no=".($pageno - 1); } ?>">Prev</a>
    </li>
    <li class="<?php if($page_no >= $total_pages){ echo 'disabled'; } ?>">
        <a href="<?php if($page_no >= $total_pages){ echo '#'; } else { echo "?page_no=".($pageno + 1); } ?>">Next</a>
    </li>
    <li><a href="?page_no=<?php echo $total_pages; ?>">Last</a></li>
</ul> 

Pagination attributes: link rel=”next” and rel=”prev”

Now, we’ll examine how to apply the rel=”next” and rel=”prev” link characteristics, including best practises and frequent pitfalls.

The rel=”next” and rel=”prev” link characteristics are used to convey to search engines the relationship between pages.

Frequently, these are mistakenly referred to as the rel=”next” and rel=”prev” pagination tags, and considered to be meta tags — however, they are not. We’ll refer to the rel=”next” and rel=”prev” properties as pagination attributes  and these are extremely important for the sake of OnPage SEO.

The pagination attributes are placed in the section of your HTML pages and look like this:

<link rel=”prev” href=”http://www.mysite.com/topic/page/4/” />
<link rel=”next” href=”http://www.mysite.com/topic/page/6/” />

Can we include paginated pages in the XML sitemap

Do not include paginated pages, even if they are indexable, in your XML sitemap. We are certain that you should only include pages in your XML sitemap that you intend to rank. Your paginated pages do not often fall under this category.

The exception to this rule is when you want to implement pagination with a View All page instead of the rel=”next” and rel=”prev” attributes. Your XML sitemap should include the View All page.