In this tutorial, we are going to create pagination with PHP and MySql. It is probably possible that your SQL SELECT query may return result into thousand and millions of records. And it is obviously not a good idea to display all those results on one page. So we can split this result into multiple pages.

What is Pagination?

Paging means displaying all your fetched results in multiple pages instead of showing them all on one page. It makes that page so long and would take so much time to load.

How to create Pagination with PHP and MySql

MySQL’s LIMIT clause helps us to create pagination feature. It uses two arguments First argument as OFFSET and second argument the number of records which will be returned from the database.

Follow these simple steps to create pagination with PHP and MySql – 

1. Get the current page number

This code will get the current page number with the help of $_GET Array. Note that if it is not present it will set the default page number to 1.

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

2. Formula for pagination

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

3. Get the number of total 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 SQL Query for pagination

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

5. Pagination buttons

Here I am using bootstrap’s pagination button

First
    

        ">Prev
    
    

        = $total_pages){ echo '#'; } else { echo "?pageno=".($pageno + 1); } ?>">Next
    
    
">Last

6. Let’s assemble all the codes in one page

    
First
        

            ">Prev
        
        

            = $total_pages){ echo '#'; } else { echo "?pageno=".($pageno + 1); } ?>">Next
        
        
">Last
    

That’s all!

If you find this article helpful, don’t forget to share it with your friends. And stay updated with us by subscribing our blog.

You may also like

• Multi Level Nested Category System in Codeigniter and MySql
• Resize Image Without Stretching in Codeigniter

The post How to create Pagination with PHP and MySql appeared first on My Programming Tutorials.