Advanced Ajax Pagination PHP, MySQL Using jQuery

This tutorial help to display Ajax Pagination using PHP, MySQL and jQuery.The Ajax Pagination is very useful to display set of data without any page refresh, In AJAX request will go async manner so that web request will work parallel with another request.

In previous article, You have learned Ajax Pagination with PHP jQuery, But here i am creating advanced ajax pagination with simplePagination jquery plugin.

simplePagination.js plugin is useful to display pagination on large data set.You can also create pagination without ajax using simplePagination jquery plugin.Simple pagination is a simple jQuery pagination plugin,which are supporting CSS3 themes and Bootstrap.You can download from simplepagination plugin.

We will create ajax pagination with php and MySQL.There are following steps need to create ajax pagination with PHP and MySQL.

Checkout other tutorials of pagination,

There are following files and folder will participate on this ajax pagination tutorial

  • db.php : Use to create connection with MySQL
  • index.php : Use to show listing of records using php
  • pagination.php : Use to fetch records from database and send to listing page.
  • dist : This folder use to keep simplePagination.js plugin files.

Dynamic Pagination in PHP and MySQL

Step 1: Let’s included all js and css files into the head section of index.html file.

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css">
<script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-2.0.3.js"></script>
<script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="dist/simplePagination.css" />
<script src="dist/jquery.simplePagination.js"></script>

Step 2: Created connection file db.php and paste below code to create database connection with mysql using PHP.

    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "test";

    $conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());
    /* check connection */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    } 
    

Step 3: Created HTML table list and pagination link.

    <table class="table table-bordered table-striped">  
    <thead>  
    <tr>  
    <th>Name</th>  
    <th>Salary</th>
    <th>Age</th>  
    </tr>  
    </thead>  
    <tbody id="target-content">
    </tbody> 
    </table>
    <nav><ul class="pagination">
    <?php if(!empty($total_pages)):for($i=1; $i<=$total_pages; $i++):  
                if($i == 1):?>
                <li class='active'  id="<?php echo $i;?>"><a href='pagination.php?page=<?php echo $i;?>'><?php echo $i;?></a></li> 
                <?php else:?>
                <li id="<?php echo $i;?>"><a href='pagination.php?page=<?php echo $i;?>'><?php echo $i;?></a></li>
            <?php endif;?>          
    <?php endfor;endif;?>
    </ul></nav>

Here, we have created a blank HTML table list, Also added first time records using onInit method.

Step 4: Call simple Pagination methods on pagination container.

        <script type="text/javascript">
            $(document).ready(function(){
                $('.pagination').pagination({
                    items: <?php echo $total_records;?>,
                    itemsOnPage: <?php echo $limit;?>,
                    cssStyle: 'light-theme',
                    currentPage : 1,
                    onPageClick : function(pageNumber) {
                        jQuery("#target-content").html('loading...');
                        jQuery("#target-content").load("pagination.php?page=" + pageNumber);
                    },
                    onInit :function() {
                        jQuery("#target-content").html('loading...');
                        jQuery("#target-content").load("pagination.php?page=1");
                    }
                });
            });
        </script>
    

Here, We are calling onInit method to fill first time records into the table when the page loaded, Next time, we are calling onPageClick method and render rows of data into tbody of table.

Step 5: The pagination.php file use to fetch records from database and bind with tr and return html to pagination ajax method.

<?php
include('db.php');

if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; };  
$start_from = ($page-1) * $limit;  
  
$sql = "SELECT * FROM employee ORDER BY id ASC LIMIT $start_from, $limit";  
$rs_result = mysqli_query($conn, $sql); 
?>

<?php  
while ($row = mysqli_fetch_assoc($rs_result)) {
?>  
            <tr>  
            <td><?php echo $row["employee_name"]; ?></td>  
            <td><?php echo $row["employee_salary"]; ?></td>  
            <td><?php echo $row["employee_age"]; ?></td>  
            </tr>  
<?php  
};  
?>

You can download source code and Demo from below link.

Please feel free to send queries to me using below comment section.Don’t forget to share it with your friends. And stay updated with us by subscribing our blog.

25 thoughts on “Advanced Ajax Pagination PHP, MySQL Using jQuery

  1. i am unable to get data from the table for two columns in pagination
    it is showing zero for columns.
    here i am pasting my code .

    Run

    Lane

    Sample id

    Project

    Raw Bases

    Raw Reads

  2. after doing the same thing three time (like,tweet and g+) fir bhi.. download button doesn’t appear.

    how Pathetic script you used bro.

      • Thanking for sending the files bro.
        however i am getting an issue.

        while i am testing this script with 4K data record, it making two adjacent. i want that only one adjacent should be appear at the middle.

        see the output
        https://s22.postimg.io/nj1pr3qoh/error.png

        • i am using simplepagination js library, You can use properties of that,like need to set

          displayedPages :10, default is 3, so that u are getting 3 sets of pagination

          refr : http://flaviusmatis.github.io/simplePagination.js/

  3. Why did you use pagination.php?

    It looks like the same code is executed in the index.php file.

    Edit: Now I see. The AJAX in the jQuery needs the file 🙂

    Thanks.

  4. 1. Well, I have set the limit as 5. On the first page; I see only 5 content but on the next page; I see all the 16 tupple of the database.

    2. And where are you calling Call simple Pagination methods on pagination container?

    • i think ur limit is resetting , hv u checked what is passing limit on second page using network tab in debugger

Leave a Reply

Your email address will not be published.