Reading Time: 8 minutes

1,589 Views

Inside this article we will discuss about the concept i.e PHP MySQLi Data Pagination Using Ajax. Article contains the classified information about getting pagination links of mysql data. This article doesn’t contains any use of third party plugin like DataTable jQuery plugin.

Article use the core concept of PHP MySQLi Ajax and Bootstrap to render table and their pagination links. You can use this concept when you want to display a bulk list of data into your application.

If you are looking for an article which gives you the concept to create data pagination using PHP and Ajax then this article will help you a lot to understand the things in a very clear way.

Learn More –

Let’s get started.

Create Database & Table

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

CREATE DATABASE php_applications;

Inside this database, we need to create a table.

Table we need – products

CREATE TABLE `products` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(70) COLLATE utf8mb4_unicode_ci NOT NULL,
 `slug` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
 `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Next, you need to insert some test data into it. Table with data will look like this –

Application Folder Structure

You need to create a folder structure to develop this application in PHP and MySQLi. Have a look the files inside this application –

Create a folder with name php-mysqli-pagination-ajax and create these 3 files into it.

Database Configuration

Open dbconfig.php file from folder. Add these lines of code into it.

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP MySQLi Data Pagination Using Ajax Tutorial
 @Email: onlinewebtutorhub@gmail.com
 @Website: https://onlinewebtutorblog.com/
*/

// Database configuration
$host   = "localhost";
$dbuser = "admin";
$dbpass = "Admin@123";
$dbname = "php_applications";

// Create database connection
$conn = new mysqli($host, $dbuser, $dbpass, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

Application Programming

Open index.php file and write this following code into it.

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP MySQLi Data Pagination Using Ajax Tutorial
 @Email: onlinewebtutorhub@gmail.com
 @Website: https://onlinewebtutorblog.com/
*/
?>
<html lang="en">

<head>
    <title>PHP MySQLi Data Pagination Using Ajax Tutorial</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.0/css/bootstrap.min.css" />
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.js"></script>
</head>

<body>
    <div class="row mt-5">
        <div class="col-md-6 offset-3 mt-5">
            <div class="card">
                <div class="card-header bg-primary text-center text-white">
                    <h6 class="m-0">PHP MySQLi Data Pagination Using Ajax Tutorial</h6>
                </div>
                <div class="card-body" style="height: 280px;">
                    <div id="productTable"></div>
                </div>
            </div>
        </div>
    </div>

    <script type="text/javascript">
        $(document).ready(function() {

            function lodetable(page) {
                $.ajax({
                    url: 'table.php',
                    type: 'POST',
                    data: {
                        page_no: page
                    },
                    success: function(data) {
                        $('#productTable').html(data);
                    }
                });
            }

            lodetable();

            $(document).on("click", "#pagenation a", function(e) {
                e.preventDefault();
                var page_id = $(this).attr("id");
                lodetable(page_id);
            });

        });
    </script>

</body>

</html>

Here,

We have used an ajax request which calls table.php

Now,

Create Data Table File

Open table.php file and write this code into it.

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP MySQLi Data Pagination Using Ajax Tutorial
 @Email: onlinewebtutorhub@gmail.com
 @Website: https://onlinewebtutorblog.com/
*/
?>

<!DOCTYPE html>
<html>

<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title></title>
    <style type="text/css">
        #pagenation a {
            color: #fff;
        }

        #pagenation {
            text-align: center;
            margin-top: 5%;
        }

        .button-style {
            border-radius: 20px;
        }

        .link {
            border-radius: 100px !important;
        }
    </style>
</head>

<body>

    <?php
    // Include Database
    include 'dbconfig.php';

    $limit_per_page = 10;
    $page = isset($_POST['page_no']) ? $_POST['page_no'] : 1;

    $offset = ($page - 1) * $limit_per_page;

    $query = "SELECT * FROM products LIMIT {$offset},{$limit_per_page}";

    $statement = $conn->prepare($query);
    $statement->execute();
    $result = $statement->get_result();

    $products = array();

    while ($item = $result->fetch_assoc()) {
        $products[] = $item;
    }
    ?>
    <table class="table">
        <thead>
            <tr>
                <th scope="col">Id</th>
                <th scope="col">Name</th>
                <th scope="col">Slug</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($products as $product) { ?>
                <tr>
                    <th scope="row">
                        <?php echo $product['id']; ?>
                    </th>
                    <td>
                        <?php echo $product['name']; ?>
                    </td>
                    <td>
                        <?php echo $product['slug']; ?>
                    </td>
                </tr>
            <?php } ?>
        </tbody>
    </table>
    <?php
    $sql_total = "SELECT count(*) FROM products LIMIT 50";

    $stmt = $conn->prepare($sql_total);
    $stmt->execute();
    $number_filter_row = $stmt->get_result()->fetch_row();

    $total_record = $number_filter_row[0];

    $total_pages = ceil($total_record / $limit_per_page);
    ?>
    <div class="pagenation" id="pagenation">

        <?php if ($page > 1) { ?>

            <a href="" id="<?php echo $page - 1; ?>" class="button-style btn btn-success">Previous</a>

        <?php } ?>

        <?php for ($i = 1; $i <= $total_pages; $i++) { ?>
            <a id="<?php echo $i ?>" href="" class="link btn btn-primary"><?php echo $i ?></a>
        <?php } ?>

        <?php if ($page != $total_pages) { ?>

            <a href="" id="<?php echo $page + 1; ?>" class="button-style btn btn-success">Next</a>

        <?php } ?>

    </div>

</body>

</html>

Application Testing

Now,

Open into browser.

URL: http://localhost/php-mysqli-pagination-ajax/index.php

Download Complete Source Code

Download Source Code

We hope this article helped you to learn PHP MySQLi Data Pagination Using Ajax Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.