jQuery 1.3 with PHP

Pagination through Ajax


OK, we’ve got some data from the server, and we’ve made the query run quickly, so
now let’s get onto pagination.


To get pagination working, we need to perform the data sorting on the server instead
of the browser. It is not possible for the sorting to be done on the client side because in
order to do that, the DataTables plugin must have all of the sortable data in memory.


Client-side code


In table.html, change the dataTable call to this:



$(‘#the_table’).dataTable({
‘bProcessing':true,
‘bServerSide':true,
‘sAjaxSource':’get_data.php’
});


The bServerSide parameter tells DataTables to do all the processing on the
server—that is, don’t do any sorting or searching locally, as the browser does not
have the information it needs to do this.


The bProcessing parameter adds a small text message, processing, to the screen
when it’s retrieving the information from the server. You can use CSS to make this
message more obvious if you want.


Server-side code


If you load up table.html in your browser now and look at the query that’s sent
(using a debugging tool such as Firebug or TamperData), you will see something
like this:


http://your.site/9-2-loading-dynamically/get_data.php?sEcho=1&iColumns

=4&sColumns=&iDisplayStart=0&iDisplayLength=10&sSearch=&bEscapeRegex=t
rue&sSearch_0=&bEscapeRegex_0=true&sSearch_1=&bEscapeRegex_1=true&sSea
rch_2=&bEscapeRegex_2=true&sSearch_3=&bEscapeRegex_3=true&iSortingCols
=1&iSortCol_0=0&iSortDir_0=asc


Quite a lot in there!


Broken apart, this URL has the following parameters:



  • sEcho: Internal variable.

  • iColumns: Number of columns being displayed.

  • sColumns: List of column names.

  • iDisplayStart: Where to paginate from.

  • iDisplayLength: Number of rows that are visible.

  • sSearch: String to search globally for.

  • bEscapeRegex: Whether search is a regular expression.

  • sSearch_(int): Column-specific search (one each for each column).

  • bEscapeRegex_(int): Whether or not the column-specific searches are regular
    expression objects.

  • iSortingCols: Number of columns to sort by.

  • iSortDir: Direction to sort in.


The interesting parts for us at the moment are iDisplayLength and iDisplayStart,
which tell us how many rows to send back, and where in the results to start.
Essentially, they’re the numbers to feed into MySQL’s limit clause.


In the earlier example URL, we’re starting at the beginning, at iDisplayStart=0,
and reading 10 values; iDisplayLength=10, which is the default for DataTables.


In get_data.php, we already have an “initialize variables” section, with
values hardcoded.


Replace that section with this code:



// { initialize variables
// { amount of records to show
$amt=10;
if(isset($_REQUEST[‘iDisplayLength’])){
$amt=(int)$_REQUEST[‘iDisplayLength’];
if($amt>100 || $amt<10)$amt=10;
}
// }
// { where to start showing the records from
$start=0;
if(isset($_REQUEST['iDisplayStart'])){
$start=(int)$_REQUEST['iDisplayStart'];
if($start<0) $start=0;
}
// }
// }


That just reads in what’s requested, and makes sure the numbers are sane.


We already wrote the queries in such a way that they accept variable starts and row
numbers. So, that’s basically it—you can now load the next page, change the visible
number, and so on.


Next, we need to fix it so that column sorting is obeyed.


Sorting by column


In the URL string shown in the last section, there are a few parameters to do
with sorting:



  • iSortingCols: This defines the number of columns that we are sorting.
    (We’ll stick with just one for this chapter.)

  • iSortCol_0: This defines the first column to sort. The value is numerical,
    starting with 0, and corresponds to what columns are displayed in the table,
    not the fields in the database.

  • iSortDir_0: This defines the direction the column should be sorted in.
    Handily, this value is either asc or desc, which we can plug directly into
    the query.


To make use of the numerical nature of iSortCol_0, we will add an array of column
names to get_data.php. Add it to the “initialize variables” block:



$cols=array(‘ccode’,’city’,’longitude’,’latitude’);


And then, we will generate MySQL’s order by clause’s information by adding the
following just below this line:



// { sort by
$scol=0;
if(isset($_REQUEST[‘iSortCol_0′])){
$scol=(int)$_REQUEST[‘iSortCol_0′];
if($scol>3 || $scol<0) $scol=0;
}
$sdir='asc';
if(isset($_REQUEST['iSortDir_0'])){
if($_REQUEST['iSortDir_0']!='asc') $sdir='desc';
}
$scol_name=$cols[$scol];
// }


Again, just a little sanitizing goes into it. The values asc and desc are sanitized
by recognising that there are only two possible values. If it’s not one, it must be
the other.


And the main query is then amended to include order information:



$rs=dbAll(“select ccode,city,longitude,latitude
from cities
order by $scol_name $sdir limit $start,$amt”);


You can now do interesting queries, such as finding the most northern cities in the
world by sorting in descending order by latitude:

Canada, Greenland, and Svalbard (part of Norway)—brr!


Notice that some of the entries are repeated. This is because my sample database
includes alternative spellings as well. Numerous separate spellings, and Ny-Ålesund
only has 40 inhabitants!


Filtering


Of course, no data table is complet e without filtering. There’s no point having a few
million results if you can’t narrow it down to a manageable level.


The URL string we’re working with includ es a number of sSearch parameters: the
sSearch parameter itself and an sSearch_0/1/2/3 parameter for each of the table
columns. We will only use the main sSearch in this chapter’s example.


In our example, we will take the string we’re given, and match it against the
beginning of the city name field, and if there are exactly two letters, will also match it
against the country.


Server-side code


First, we need to set up MySQL’s where clause. Add this to the “initialize variables”
section of get_data.php:



// { search
$search_sql=”;
if(isset($_REQUEST[‘sSearch’]) && ”!=$_REQUEST[‘sSearch’]){
$stext=addslashes($_REQUEST[‘sSearch’]);
$search_sql=’where ‘;
if(strlen($stext)==2) $search_sql.=”ccode=’$stext’ or “;
$search_sql.=”city like ‘$stext%'”;
}
// }


This section builds up a $search_sql string if needed, which compares against city
and optionally also against the country code.


Now, we can set up the iTotalDisplayRecords variable correctly.


Add this below the “count existing records” section:



// { count records after filtering
$total_after_filter=$total_records;
if($search_sql){
$r=dbRow(“select count(ccode) as c from cities $search_sql”);
$total_after_filter=$r[‘c’];
}
// }


And we then also need to change the main query, and to add in the
iTotalDisplayRecords to the opening of the returned JSON object:



echo
‘{“iTotalRecords”:’.$total_records.’,
“iTotalDisplayRecords”:’.$total_after_filter.’,”aaData”:[‘;
$rs=dbAll(“select ccode,city,longitude,latitude
from cities $search_sql
order by $scol_name $sdir limit $start,$amt”);


When that’s complete, you can run queries in the browser. Here’s an example run
against my own City:

If you run this yourself, you’ll see that every time you hit a key, a query is sent to the
server. As I’ve said earlier in the book, this is a very bad idea, which can cause race
conditions and overloading on the server.


We’ll solve that now.


Setting a delay on the filter


To avoid overloading your server, you need to query the data only when you’ve
actually finished typing.


The DataTables plugin itself can be extended with further plugins, one of which is
called fnSetFilteringDelay , created by Zygimantas Berziunas, which delays the
sending of the query until after you’ve stopped typing.


To include it, simply copy and paste from the DataTables plugins page into your
tables.html page, above the $(document).ready section, and then activate it by
chaining it to your dataTable call.


Here is the JavaScript in full, with comments removed from the
fnSetFilteringDelay plugin for readability (the license for the plugin is GPL2 or
BSD3.x):



jQuery.fn.dataTableExt.oApi.fnSetFilteringDelay =
function ( oSettings, iDelay ) {
iDelay = (iDelay && (/^[0-9]+$/.test(iDelay))) ? iDelay : 250;
var $this = this, oTimerId;
var anControl = $( ‘div.dataTables_filter input:text’ );
anControl.unbind( ‘keyup’ ).bind( ‘keyup’, function() {
var $$this = $this;
window.clearTimeout(oTimerId);
oTimerId = window.setTimeout(function() {
$$this.fnFilter( anControl.val() );
}, iDelay);
});
return this;
}
$(document).ready(function(){
$(‘#the_table’).dataTable({
‘bProcessing':true,
‘bServerSide':true,
‘sAjaxSource':’get_data.php’
}).fnSetFilteringDelay();
});


What it does, is to remove keyup event of DataTables from the search box, and
replace it with a less eager version, which will wait until you are finished typing
before sending off the query.


Summary


In this chapter, we have learned how to display data using a plugin that allows the
data to be sorted, searched, and paginated.


In the next chapter, we will discuss various ways to optimize jQuery and some other
elements of the web development environment.

Comments

comments

Pages: 1 2

About Krishna Srinivasan

He is Founder and Chief Editor of JavaBeat. He has more than 8+ years of experience on developing Web applications. He writes about Spring, DOJO, JSF, Hibernate and many other emerging technologies in this blog.

Speak Your Mind

*