Ajax Pagination with Tabular Records using PHP and jQuery

Ajax-Pagination

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.

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.

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

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 employee data are stored in the database and queried via AJAX to display the paginated results from the tbl_employee.

<!DOCTYPE html>
<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 employee 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.

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

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

$perPageCount = 5;

$sql = "SELECT * FROM tbl_employee  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_employee 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">Age</th>
    <th align="center">Subject</th>
  </tr>
  <?php //foreach ($results as $data) { 
	while($data = mysqli_fetch_assoc($results)) {
	?>
  <tr>
    <td align="left"><?php echo $data['name'] ?></td>
    <td align="left"><?php echo $data['age'] ?></td>
    <td align="left"><?php echo $data['subject'] ?></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>

Database script

Includes a SQL script to set up the database for running this example.

--
-- Database: `pagination`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_employee`
--

CREATE TABLE IF NOT EXISTS `tbl_employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_employee`
--

INSERT INTO `tbl_employee` (`id`, `name`, `age`, `subject`) VALUES
(1, 'Elizabeth', 20, 'Computer Science'),
(2, 'Reena', 23, 'Artificial Intelligence'),
(3, 'Robert', 35, 'Statistics'),
(4, 'Samantha', 32, 'Mathematics'),
(5, 'Gabriella', 40, 'Operation Research'),
(6, 'Oliver', 26, 'Computer Science'),
(7, 'Peyton', 28, 'Artificial Intelligence'),
(8, 'Mclane', 31, 'Statistics'),
(9, 'Mackenzie', 42, 'Mathematics'),
(10, 'Bailey', 23, 'Operation Research'),
(11, 'Abel', 28, 'Computer Science'),
(12, 'Jennifer', 29, 'Artificial Intelligence'),
(13, 'Jane Thomas', 28, 'Statistics'),
(14, 'Violet', 19, 'Mathematics'),
(15, 'Sebastian', 20, 'Operation Research'),
(16, 'Sophia', 17, 'Computer Science'),
(17, 'Isabella', 22, 'Digital Systems'),
(18, 'Gabriel', 36, 'Artificial Intelligence'),
(19, 'Brielle', 16, 'Statistics'),
(20, 'Michelle', 43, 'Mathematics');
Please follow and like us:

By Rodney

I’m Rodney D Clary, a web developer. If you want to start a project and do a quick launch,I am available for freelance work.

Leave a Reply

Your email address will not be published. Required fields are marked *