Paginating AJAX JSON returned data

Question

Good evening all,

Just after some pointers really on paginating json encoded data.

I have been looking at some other pages on stackoverflow and for some reason cannot seem to wrap my head around being able to paginate the returned json data.

I have had a look at the following pages: How to use jQuery to paginate JSON data?, PHP/JSON-jquery pagination and http://www.9lessons.info/2010/10/pagination-with-jquery-php-ajax-and.html

I have the following pages:

fia.php (database script)

<?php

include('constants.inc.php');

$result=mysql_query("SELECT id, firstname, lastname FROM ".PERSON." ORDER BY lastname ASC, firstname ASC LIMIT 0, 30");
$num=mysql_numrows($result);

$data = array();

while ($row = mysql_fetch_row($result))
{
    $data[] = $row;
}

echo json_encode($data);
?>

fi.php (to display the results)

<?php
include('header.php');
?>
<script>
$(document).ready(function()
{
function loadit()
{
    $.ajax(
    {
        url: "fia.php",
        data: "",
        dataType: "json",
        success: function(d)
        {
            for (var i in d)
            {
                var r = d[i];

                var id = r[0];
                var fname = r[1];
                var lname = r[2];
                $('#stuff').append('<br/><b>ID</b> '+id+'<br/><b>Name</b> '+fname+' '+lname+'');
            }
        }
    });
}
loadit();
});
</script>
<div id="stuff">Stuff goes here</div>
<?php
include('footer.php');
?>

I just can't seem to wrap my head around passing the page variables to the php script through the ajax call. Do I just pass the page variable through data? If so, how do I get the pagination to show under the returned results - just looking for some pointers as to how it could be done

If I use solely PHP, I can paginate it and range it just fine and it will switch pages as I move forwards or backwards

Thanks in advance (apologies if this question seems really dumb)

score 1 · Answer 1 · answered Feb 14 '14 at 00:53

If you can manage the pagination in just php, it should not be that hard to do it using ajax:

You need to send the page number (and optionally the number of items per page) to your php script, so your data line would become something like: data: {pageNum: XX},
In your php script you'd use $_GET('pageNum') and the amount of items per page to set the correct parameters for the LIMIT clause.
In your php script you also need to get the total number of items so that you can send that number and the data items back to the success function of your ajax call.
In the success function of your ajax call you build your content and pagination using javascript like you would in php with the data returned in d.
You would need to modify your loadit function so that it accepts a parameter - the page number - and add an event handler to catch all clicks on the paginated links and pass them to your loadit() function

score 1 · Answer 2 · answered Feb 14 '14 at 00:54

One of the main benefits of AJAX is that you can load changes to a page without sending a request for an entirely new page. When using AJAX, realize that JavaScript will do all of the requesting on the fly and PHP will provide the data for the JavaScript requests. In your case, I would recommend using a query parameter or something to signal to the PHP what data it should produce. For example, you may wants to use the $_GET['startingFrom'] variable to check if you should load the data from an initial offset. Then, the MySQL query will fetch the data and the PHP will output the JSON for the specified data. When the user clicks a "Next" button on the page, you might call a function like this:

function clickedNext() {
    startingFrom += 30;
    $.ajax('fia.php', {
        data: {
            startingFrom: startingFrom
        }, ...
    });
}

score 1 · Answer 3 · answered Feb 14 '14 at 00:56

Here's the general idea (no error checking, etc. but it should get you started) In the php file

$page = $_POST['page'];  // Numbers 1 - whatever
$limit = 30;  // you could also have this be a parameter passed to the script
$offset = ($page -1) * $limit;


$slimit = " LIMIT ".$limit . " OFFSET ". $offset;
$sQuery = "SELECT id, firstname, lastname FROM ".PERSON." ORDER BY lastname ASC, firstname ASC" .$limit;

In the javascript

$(document).ready(function()
{
     var page = 0;
function loadit()
{
    $.ajax(
    {
        url: "fia.php",
        data: {page: page},
        type: 'post',
        dataType: "json",
        success: function(d)
        {
            for (var i in d)
            {
                var r = d[i];

                var id = r[0];
                var fname = r[1];
                var lname = r[2];
                $('#stuff').append('<br/><b>ID</b> '+id+'<br/><b>Name</b> '+fname+' '+lname+'');
            }
        }
    });
}
loadit();

nextpage = function() {
   page++;
   loadit();
}
prevpage = function() {
   page--;
   loadit();
}
});

Paginating AJAX JSON returned data

3 Answers3