AJAX Pagination with jQuery and PHP

Using Pagination you can divide the huge list of data in multiple parts or pages. The user only able to see specific numbers of records and need to navigate for viewing another list of records.

In the demonstration, I am creating a table structure which shows the list of records. Adding AJAX pagination which has a previous and next button for navigation.

Ajax Pagination with jQuery and PHP


Contents

  1. Configuration
  2. HTML
  3. CSS
  4. PHP
  5. jQuery
  6. Conclusion

 

1. Table structure

We are only using one Table in the tutorial example. employee table.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `emp_name` varchar(100) NOT NULL,
  'salary' varchar(20) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2.HTML

Creating a table structure and a <div id=’div_pagination’> for holding pagination controls – two hidden fields, next and previous buttons.

  • The first hidden field is used to store start selection row index. I set its default value to 0.
  • The second hidden field is used to store the total number of rows.

Completed Code

<div id="content">
    <table width="100%" id="emp_table" border="0">
        <tr class="tr_header">
            <th>Employee id</th>
            <th>Employee Name</th>
            <th>Salary</th>
        </tr>
    </table>

    <div id="div_pagination">
    <input type="hidden" id="txt_rowid" value="0">
    <input type="hidden" id="txt_allcount" value="0">
    <input type="button" class="button" value="Previous" id="but_prev" />

    <input type="button" class="button" value="Next" id="but_next" />
    </div>
</div>


3.CSS

#content{
    border:1px solid darkgrey;
    border-radius:3px;
    padding:5px;
}

/* Table */
#emp_table {
    border:3px solid lavender;
    border-radius:3px;
}

/* Table header */
.tr_header{
    background-color:dodgerblue;
}
.tr_header th{
    color:white;
    padding:10px 0px;
    letter-spacing: 1px;
}

/* Table rows and columns */
#emp_table td{
    padding:10px;
}
#emp_table tr:nth-child(even){
    background-color:lavender;
    color:black;
}

/* */
#div_pagination{
    width:100%;
    margin-top:5px;
    text-align:center;
}

.button{
    border-radius:3px;
    border:0px;
    background-color:mediumpurple;
    color:white;
    padding:10px 20px;
    letter-spacing: 1px;
}


4.PHP

Create a new PHP file getEmployeeInfo.php from where handling AJAX request.

According to AJAX post rowid and rowperpage values selecting data from MySQL table by using them in LIMIT and return a JSON response.

Completed Code

<?php
include "config.php";

/* Getting post data */
$rowid = $_POST['rowid'];
$rowperpage = $_POST['rowperpage'];

/* Count total number of rows */
$query = "SELECT count(*) as allcount FROM employee";
$result = mysql_query($query);
$fetchresult = mysql_fetch_array($result);
$allcount = $fetchresult['allcount'];

/* Selecting rows */
$query = "SELECT * FROM employee ORDER BY id ASC LIMIT ".$rowid.",".$rowperpage;

$result = mysql_query($query);

$employee_arr = array();
$employee_arr[] = array("allcount" => $allcount);

while($row = mysql_fetch_array($result)){
    $empid = $row['id'];
    $empname = $row['emp_name'];
    $salary = $row['salary'];

    $employee_arr[] = array("empid" => $empid,"empname" => $empname,"salary" => $salary);
}

/* encoding array to json format */
echo json_encode($employee_arr);

5.jQuery

Send first AJAX request on document ready state where passing rowid and rowperpage values as data. On AJAX successfully callback create new rows and add it in <table id=’emp_table’>.

Sending other AJAX requests on Next and Previous buttons clicks.

Working

  • For getting the next start rows index I added rowid and rowperpage values. If the value is less than or equal to allcount then send AJAX request.
  • Subtracting rowid with rowperpage to get the previous row start index. If the rowid value is less than 0 then set it 0.

Completed Code

<script type="text/javascript">
    // Total number of rows visible at a time
    var rowperpage = 5;
    $(document).ready(function(){
        getData();

        $("#but_prev").click(function(){
            var rowid = Number($("#txt_rowid").val());
            var allcount = Number($("#txt_allcount").val());
            rowid -= rowperpage;
            if(rowid < 0){
                rowid = 0;
            }
            $("#txt_rowid").val(rowid);
            getData();
        });

        $("#but_next").click(function(){
            var rowid = Number($("#txt_rowid").val());
            var allcount = Number($("#txt_allcount").val());
            rowid += rowperpage;
            if(rowid <= allcount){
                $("#txt_rowid").val(rowid);
                getData();
            }

        });
    });
    /* requesting data */
    function getData(){
        var rowid = $("#txt_rowid").val();
        var allcount = $("#txt_allcount").val();

        $.ajax({
            url:'getEmployeeInfo.php',
            type:'post',
            data:{rowid:rowid,rowperpage:rowperpage},
            dataType:'json',
            success:function(response){
                createTablerow(response);
            }
        });

    }
    /* Create Table */
    function createTablerow(data){

        var dataLen = data.length;

        $("#emp_table tr:not(:first)").remove();

        for(var i=0; i<dataLen; i++){
            if(i == 0){
                var allcount = data[i]['allcount'];
                $("#txt_allcount").val(allcount);
            }else{
                var empid = data[i]['empid'];
                var empname = data[i]['empname'];
                var salary = data[i]['salary'];

                $("#emp_table").append("<tr id='tr_"+i+"'></tr>");
                $("#tr_"+i).append("<td align='center'>"+empid+"</td>");
                $("#tr_"+i).append("<td align='left'>"+empname+"</td>");
                $("#tr_"+i).append("<td align='center'>"+salary+"</td>");
            }
        }
    }
</script>

 

6. Conclusion

I showed how you can implement AJAX pagination in your project.

The pagination is always a better option when to have to show the large list of data in the web page. It organized the records and user-friendly.

If you want to learn some other types of pagination you can view the following tutorial –

Spread the love

Related Post

One Comment

Leave a Reply

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