Site Admin
Site Admin Founder of MeaningArticles
627 Views

Create Pagination in PHP and MySql

Hello Dev.

On this article, we're going to create pagination with php and MySql. It might be viable that your SQL SELECT query can also return results into thousand and millions of information. And it's miles glaringly no longer an awesome idea to display all those outcomes on a single web page. So we will split this result into more than one pages.

Pagination

Paging approach showing all of your fetched consequences in multiple pages as opposed to displaying them all on one page. It makes that web page so long and could take so much time to load.

Pagination in PHP and MySql

MySQL’s LIMIT clause help us to create a pagination feature. It makes use of two arguments First argument as OFFSET and the second argument the number of information as a way to be back from the database.

Follow those easy steps to create pagination in php

So let's start the lesson...

 

Step 1: Get page number


This code gets the current page range with the help of $_GET Array. Note that if it isn't present it'll set the default page number to 1(one).

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

 

Step 2: Formula for php pagination

You can continually manipulate the number of records to be displayed in a page with the aid of changing the value of $no_of_records_per_page variable.

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

 

Step 3: Get number of 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);

 

Step 4: Constructing the SQL Query for pagination

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

 

Step 5: Pagination buttons

Those Buttons are served to users as Next web page & previous web page, in an effort to without difficulty navigate via you pages. right here i'm the use of bootstrap’s pagination button, you could use your very own buttons in case you need.

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

 

Step 6: let’s collect all the codes in a single page

<html>
<head>
    <title>PHP and MySQL Pagination - meaningarticles.com</title>
    <!-- Bootstrap CDN -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
    <?php

        if (isset($_GET['pageno'])) {
            $pageno = $_GET['pageno'];
        } else {
            $pageno = 1;
        }
        $no_of_records_per_page = 10;
        $offset = ($pageno-1) * $no_of_records_per_page;

        $conn=mysqli_connect("localhost","my_user","my_password","my_db");
        // Check connection
        if (mysqli_connect_errno()){
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
            die();
        }

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

        $sql = "SELECT * FROM table LIMIT $offset, $no_of_records_per_page";
        $res_data = mysqli_query($conn,$sql);
        while($row = mysqli_fetch_array($res_data)){
            //here goes the data
        }
        mysqli_close($conn);
    ?>
    <ul class="pagination">
        <li><a href="?pageno=1">First</a></li>
        <li class="<?php if($pageno <= 1){ echo 'disabled'; } ?>">
            <a href="<?php if($pageno <= 1){ echo '#'; } else { echo "?pageno=".($pageno - 1); } ?>">Prev</a>
        </li>
        <li class="<?php if($pageno >= $total_pages){ echo 'disabled'; } ?>">
            <a href="<?php if($pageno >= $total_pages){ echo '#'; } else { echo "?pageno=".($pageno + 1); } ?>">Next</a>
        </li>
        <li><a href="?pageno=<?php echo $total_pages; ?>">Last</a></li>
    </ul>
</body>
</html>

I hope it assists you, thanks for visiting my article if you like my article then share it with your friends on the social media platform.

Happy Coding.....