Ajax Pagination with Tabular Records using PHP and jQuery

Last modified on June 30th, 2018 by Vincy.

Pagination feature is for limiting the number of results instead of loading all in a list page. Loading multiple records with pagination will increase efficiency to load result page by page. We have seen several examples for PHP pagination with database results. For the pagination functionality, a perpage result count will be configured. This configured number will be used in the query to set the limit.

Ajax-Pagination-with-Tabular-Records-using-PHP-and-jQuery

Pagination functionality will be suitable when we need to show more result in a list, tabular form, gallery and more. In this example, we are going to learn how to apply pagination for the tabular records resulted in by querying the database. I have used jQuery AJAX for displaying the paginated result in a tabular form. The perpage count is configured as 5 to fetch five records from the database by setting the query limit. The number of pages is calculated from the total record count and the perpage configuration.

HTML Code to Display Paginated Tabular Records

This code shows the target container in the HTML to display the paginated result in a tabular form. On loading the landing page, an AJAX call will be sent to the PHP by passing the start and the limit parameters to query the database. In this example, the staff data are stored in the database and queried via AJAX to display the paginated results from the tbl_staff.

<html>
<head>

<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script
    src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>

<title>Ajax Pagination with Tabular Records using PHP and jQuery</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
    <div id="container">
        <div id="inner-container">

            <div id="results"></div>
            <div id="loader"></div>

        </div>
    </div>
</body>
</html>

AJAX Script to Access PHP to Query Pagination Results

This jQuery AJAX script is used to call PHP file getPageDate.php to fetch limited records from the database as specified. On sending AJAX request the start param will be posted to the PHP to set the query limit with perpage configuration. The resultant HTML response will be received on the AJAX success callback to update the UI with the paginated result.

<script type="text/javascript">
    function showRecords(perPageCount, pageNumber) {
        $.ajax({
            type: "GET",
            url: "getPageData.php",
            data: "pageNumber=" + pageNumber,
            cache: false,
    		beforeSend: function() {
                $('#loader').html('<img src="loader.png" alt="reload" width="20" height="20" style="margin-top:10px;">');
    			
            },
            success: function(html) {
                $("#results").html(html);
                $('#loader').html(''); 
            }
        });
    }
    
    $(document).ready(function() {
        showRecords(10, 1);
    });
</script>

PHP Code to Fetch Staff Details with Pagination

This is the PHP code that is called via AJAX. It receives the query limit params to set the start and limit value of the select query to retrieve the limited record from the database. After getting the results, it forms HTML response to display tabular results with pagination links. The pagination links are created in a loop which is executed until the total page count is reached. In this example, the MySQLi functions are used to query with the database. You can find PHP pagination example with PDO if you need. 

<?php
require_once ("db.php");

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

$perPageCount = 5;

$sql = "SELECT * FROM tbl_staff  WHERE 1";

if ($result = mysqli_query($conn, $sql)) {
    $rowCount = mysqli_num_rows($result);
    mysqli_free_result($result);
}

$pagesCount = ceil($rowCount / $perPageCount);

$lowerLimit = ($pageNumber - 1) * $perPageCount;

$sqlQuery = " SELECT * FROM tbl_staff WHERE 1 limit " . ($lowerLimit) . " ,  " . ($perPageCount) . " ";
$results = mysqli_query($conn, $sqlQuery);

?>

<table class="table table-hover table-responsive">
    <tr>
        <th align="center">Name</th>
        <th align="center">Experience<br>(in years)
        </th>
        <th align="center">Subject</th>
    </tr>
    <?php foreach ($results as $data) { ?>
    <tr>
        <td align="left">
            <?php echo $data['name'] ?>
        </td>
        <td align="left">
            <?php echo $data['experience'] ?>
        </td>
        <td align="left">
            <?php echo $data['major'] ?>
        </td>
    </tr>
    <?php
    }
    ?>
</table>

<div style="height: 30px;"></div>
<table width="50%" align="center">
    <tr>

        <td valign="top" align="left"></td>


        <td valign="top" align="center">
            <?php
	for ($i = 1; $i <= $pagesCount; $i ++) {
    if ($i == $pageNumber) {
        ?> <a href="javascript:void(0);" class="current">
                <?php echo $i ?>
        </a> <?php
    } else {
        ?> <a href="javascript:void(0);" class="pages"
            onclick="showRecords('<?php echo $perPageCount;  ?>', '<?php echo $i; ?>');">
                <?php echo $i ?>
        </a> <?php
    } // endIf
} // endFor

?>
        </td>
        <td align="right" valign="top">Page <?php echo $pageNumber; ?>
            of <?php echo $pagesCount; ?>
        </td>
    </tr>
</table>

PHP AJAX pagination with Tabular Record Output

This screenshot shows the output of the PHP AJAX pagination for tabular records.

AJAX-Pagination-with-Tabular-Records-Output

Download

↑ Back to Top

Share this Article