Paginating on the client side, as we did in the previous recipe, is great, but it's not necessarily efficient. If you only want to display four records, why retrieve more than that? If you have a database such as Microsoft SQL Server, MySQL, or PostgreSQL, for example, and a server-side language such as PHP, Ruby, or ASP.NET, you can use jQuery's built-in AJAX functions to retrieve specific subsets of data.
We're not going to delve into the server-side code, as that will vary depending on your choice of server-side technologies. Even within a given technology, there are often various frameworks and/or approaches.
What we'll discuss is how to use jQuery on the client side to communicate with your server. I'm going to trust that you'll know how to write the server-side code to query your database and return the appropriate JSON data.
For this recipe, start off with the simplest table of all. Use the same data as in the previous recipe, but start off with a completely empty table body. Keep the Previous and Next links, as they'll still be needed.
Create the table with an ID of your choice. Leave the
tbody
tag empty. On the initial page load, an AJAX call will retrieve data from the server, and populate the rows within thetbody
.<table border="1"> <thead> <tr> <th class="ranking">Ranking</th> <th class="title">Movie</th> <th class="releaseYear">Release Year</th> </tr> </thead> <tbody> </tbody> </table>
Reuse the same Previous and Next links that were used in the previous recipe:
<div> <a href="#" class="paginate" id="previous">Previous</a> | <a href="#" class="paginate" id="next">Next</a> </div>
Finally, add the script. Continue to page four records at a time.
<script type="text/javascript"> $( document ).ready( function() { var firstRecord = 0; var pageSize = 4; var recordcount = $.ajax({ type: "GET", url: "_recordcount.php", async: false }).responseText; $( "a.paginate" ).click( function( e ) { e.preventDefault(); if ( $( this ).attr( "id" ) == "next" ) { if ( firstRecord + pageSize <= recordcount ) { firstRecord += pageSize; } } else { if ( firstRecord != 0 ) { firstRecord -= pageSize; } } paginate( firstRecord, pageSize ); }); var paginate = function( startAt, pageSize ) { $( "#movies tbody tr" ).remove(); $.ajax({ type: "POST", url: "_paginate.php", data: { startAt: firstRecord, pageSize: pageSize }, dataType: "json" }).done( function( data ) { $.each( data, function( index, val ) { var tablerow = "<tr><td>" + val[ 0 ] + "</td><td>" + val[ 1 ] + "</td><td>" + val[ 2 ] + "</td></tr>"; $( "#movies tbody" ).append( tablerow ); }); }); } paginate( firstRecord, pageSize ); }); </script>
The table should initially load with the first four records displayed, even though the table markup was empty:
Once again, there's quite a lot happening here, but some of it should be familiar from the previous recipe.
Let's start by defining three variables:
firstRecord: All of the table rows will be stored in an array. This variable holds the value, or the array index position, of the row that's currently being displayed at the first position. This is exactly the same as in the previous recipe.
pageSize: How many records do we want to display per page? This value is referred to several times in the code, so assigning it to a variable makes it easier to change down the road, should the need arise. This is exactly the same as in the previous recipe.
recordcount: In the previous recipe, there was a variable called
tablerows
, which held the number of rows in the table. That was used to ensure that the user didn't try to page past the number of total rows. But since the data is being retrieved only as it's needed, the total number of table rows isn't immediately known. So make the first of two$.ajax()
calls. Take a closer look at the$.ajax()
call before moving on:var recordcount = $.ajax({ type: "GET", url: "_recordcount.php", async: false }).responseText;
$.ajax()
is a built-in jQuery function. The options being used here are as follows:
type: "GET"
– No data is being posted, justGET
therecordcount
.URL:
– This is the page that jQuery will interact with.async: false
– Because the result of this AJAX call is being assigned to a variable, it should not happen asynchronously. If it does, there is a risk of trying to reference the variable before the value is set.
The AJAX call returns an object. Among the properties returned is the responseText
. That's the specific value that is assigned to the recordcount
variable.
Now that the total number of records is known, continue.
The next section handles the click
events on the Previous and Next links. This block remains unchanged from the previous recipe. Simply calculate the firstRecord
variable for the next set of records, and call the paginate()
function.
The paginate()
function itself is rather different than in the previous recipe, but still fairly straightforward.
The first thing to do is to remove all of the table rows within the table body. On initial page load, there are none, but subsequent calls to the function will have some displayed. Removing the rows is accomplished using jQuery's built-in remove()
method.
$( "#movies tbody tr" ).remove();
Now for another AJAX call, this time to retrieve the records. It's similar to the first AJAX call with a couple of notable exceptions. First, it's doing a POST
rather than a GET
, because data is being sent to the server. That data is the startAt
value and the pageSize
value. Because JSON is being sent back, specify that as the dataType.
The returned data will look as follows (taken from Firefox's Firebug plugin):
This is an array of arrays. Loop over that data manually, and construct the table rows to be displayed.
This looping is done via the .done()
method, which is built into the $.ajax()
object. The done()
method has a hook into the data that was returned from the AJAX call. Being the creative sort, I refer to that in the code as data. This is just a variable name, however. You can name it whatever you like. In this case, movierecords
might have been a more descriptive name.
Loop over that array data via jQuery's $.each()
function. You've seen something similar before, where .each()
was used to iterate over a jQuery collection. In this case, it's not a reference to a specific jQuery object or collection, but a generic iterator that's part of jQuery itself. In fact, the $
in front of it is a direct reference to jQuery.
$.each()
takes two arguments. The object that will be iterated over, and a callback function that holds an index variable and a variable reference to the current item:
$.each( data, function( index, val )
For each loop iteration, create a table row with three table cells. The value of each cell will be val[ 0 ]
, val[ 1 ]
, and val[ 2 ]
. Remember that the arrays are zero-based. The constructed table row is then assigned to a variable, tablerow
(yes, once again, creatively named).
var tablerow = "<tr><td>" + val[ 0 ] + "</td><td>" + val[ 1 ] + "</td><td>" + val[ 2 ] + "</td></tr>";
The final step is to append the newly constructed table row into the table body of the table. .append()
is another built-in jQuery function that, surprisingly enough, appends an element into an existing element.
$( "#movies.tbody" ).append( tablerow );
That's it for the function. All that's left to do is to call it on initial page load, and four rows should appear in the table.
While it's much nicer to only retrieve the specific records to display, this can still be cleaned up a bit. As with client-side paging, you might want to disable the Previous and Next links when the user is at the lower or upper boundaries of the recordset
.
Additionally, we show a great deal of faith that our server will be up and running, and that our server-side code will be error free. As with the .done()
method, $.ajax()
also has a built-in .error()
method that will fire if something unexpected should occur. It's probably a good idea to make use of this and be prepared for the worst.