Data Tables and DataTables Plugin in jQuery 1.3 with PHP

Packt Publishing

For PHP developers, this intro to jQuery has it all. It will empower your applications with new responsive capabilities and custom-built plugins. You’ll be amazed at the difference a few lines of JavaScript can make.

From time to time, you will want to show data in your website and allow the data to be sorted and searched.

It always impresses me that whenever I need to do anything with jQuery, there are usually plugins available, which are exactly or close to what I need.

Here's an example screen from the project we will build in this article. The data is from a database of cities of the world, filtered to find out if there is any place called nowhere in the world:

Get your copy of DataTables from http://www.datatables.net/, and extract it into the directory datatables, which is in the same directory as the jquery.min.js file.

What the DataTables plugin does is take a large table, paginate it, and allow the columns to be ordered, and the cells to be filtered.

Setting up DataTables

Setting up DataTables involves setting up a table so that it has distinct < thead > and < tbody > sections, and then simply running dataTable() on it.

As a reminder, tables in HTML have a header and a body. The HTML elements < thead > and < tbody > are optional according to the specifications, but the DataTables plugin requires that you put them in, so that it knows what to work with.

These elements may not be familiar to you, as they are usually not necessary when you are writing your web pages and most people leave them out, but DataTables needs to know what area of the table to turn into a navigation bar, and which area will contain the data, so you need to include them.

Client-side code

The first example in this article is purely a client-side one. We will provide the data in the same page that is demonstrating the table.

Copy the following code into a file in a new demo directory and name it tables.html:

When this is viewed in the browser, we immediately have a working data table:

Note that the rows are in alphabetical order according to Artist/Band. DataTables automatically sorts your data initially based on the first column.

The HTML provided has a < div > wrapper around the table, set to a fixed width. The reason for this is that the Search box at the top and the pagination buttons at the bottom are floated to the right, outside the HTML table. The < div > wrapper is provided to try to keep them at the same width as the table.

There are 14 entries in the HTML, but only 10 of them are shown here. Clicking the arrow on the right side at the bottom-right pagination area loads up the next page:

And finally, we also have the ability to sort by column and search all data:

In this screenshot, we have the data filtered by the word horslips, and have ordered Song in descending order by clicking the header twice.

With just this example, you can probably manage quite a few of your lower-bandwidth information tables. By this, I mean that you could run the DataTables plugin on complete tables of a few hundred rows. Beyond that, the bandwidth and memory usage would start affecting your reader's experience. In that case, it's time to go on to the next section and learn how to serve the data on demand using jQuery and Ajax.

As an example of usage, a user list might reasonably be printed entirely to the page and then converted using the DataTable plugin because, for smaller sites, the user list might only be a few tens of rows and thus, serving it over Ajax may be overkill. It is more likely, though, that the kind of information that you would really want this applied to is part of a much larger data set, which is where the rest of the article comes in!

Getting data from the server

The rest of the article will build up a sample application, which is a search application for cities of the world.

This example will need a database, and a large data set. I chose a list of city names and their spelling variants as my data set. You can get a list of this type online by searching.

The exact point at which you decide a data set is large enough to require it to be converted to serve over Ajax, instead of being printed fully to the HTML source, depends on a few factors, which are mostly subjective. A quick test is: if you only ever need to read a few pages of the data, yet there are many pages in the source and the HTML is slow to load, then it's time to convert.

The reason I chose a city name list is that I wanted to provide a realistic large example of when you would use this.

In your own applications, you might also use the DataTables plugin to manage large lists of products, objects such as pages or images, and anything else that can be listed in tabular form and might be very large.

The city list I found has over two million variants in it, so it is an extreme example of how to set up a searchable table.

It's also a perfect example of why the Ajax capabilities of the DataTables project are important. Just to see the result, I exported all the entries into an HTML table, and the file size was 179 MB. Obviously, too large for a web page.

So, let's find out how to break the information into chunks and load it only as needed.

Client-side code

On the client side, we do not need to provide placeholder data. Simply print out the table, leaving the < tbody > section blank, and let DataTables retrieve the data from the server.

We're starting a new project here, so create a new directory in your demos section and save the following into it as tables.html:

In a nutshell, what happens is that the script counts how many cities are there in total, and then returns that count along with the first ten entries to the client browser using JSON as the transport.

iTotalDisplayRecords is the total number after filtering (explained later)

aaData is a two-dimensional array of data that corresponds to the rows and columns of the shown table

For the moment, iTotalRecords is equal to iTotalDisplayRecords. Later in the article, we'll see how this would change.

If you have done like I did, and have installed a very large database, you'll see the first ten entries of the table appear in the browser several seconds after the page opens.

This is because it is much slower to build a list of items from a database than it is to just read the list directly from a file.

We'll address that now, and will come back to pagination, ordering, and searches afterwards.

Caching your database queries

In any large database, it is important to cache your queries. Database calls can be quite expensive, even after careful tuning.

In the database that I set up for this, there are over two million rows, and queries can take seconds to complete (tens of seconds if no indexing is done).

While modern databases do include caching engines for popular queries, it is much better to simply open and read a file that contains cached information that translates to "There are 2673762 rows in this database" than to have the database actually count the rows.

We will create a very simple caching mechanism that takes the requested query, encodes the query to a string using MD5, then returns the cached query if it exists, and creates the cache if not. Remember, MD5 returns a pseudo-random string of characters that can be used to save a cache with a unique name.

A nice thing about cities and countries is that the data does not change very quickly. So, it would not be necessary to clear the cache all that often—after a few hundred queries through it, it should be quite fast, as the most common searches will be cached quickly, and non-cached searches will increasingly only be for rarer requests.

Server-side code

So, let's add the caching functions. Add this to the top of get_data.php:

These functions read and write from a directory named cache contained in the same directory as the file itself. Create the directory and make sure it is writable by the server.

The reason I use json_encode instead of serialize is that if I ever want to pass the data directly back to jQuery without re-encoding it, then JSON is perfect. With serialize, I would need to decode it in PHP, then re-encode as JSON before sending it on.

Now, we need to change the database querying functions so that they read from the cache if possible. Change the two functions to:

Note that this caches all queries—if you are using this in a project where the data is more volatile, then you will want to change the caching method to only cache data you are sure is likely to be static, and to allow for easy clearing of the cache in case of data changing.

Before we carry on, you should verify for yourself that there is a marked improvement in the speed. Load up the page a few times first using the non-cached version, and time it, and then load up the cached version a few times. On very large data sets, the speed difference should be very obvious.

If you overwrote your cache, you can emulate this, by manually removing the server's cache after each page load.

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.

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.

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 complete 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 includes 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 article'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:

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):

Alerts & Offers

Series & Level

We understand your time is important. Uniquely amongst the major publishers, we seek to develop and publish the broadest range of learning and information products on each technology. Every Packt product delivers a specific learning pathway, broadly defined by the Series type. This structured approach enables you to select the pathway which best suits your knowledge level, learning style and task objectives.

Learning

As a new user, these step-by-step tutorial guides will give you all the practical skills necessary to become competent and efficient.

Beginner's Guide

Friendly, informal tutorials that provide a practical introduction using examples, activities, and challenges.

Essentials

Fast paced, concentrated introductions showing the quickest way to put the tool to work in the real world.

Cookbook

A collection of practical self-contained recipes that all users of the technology will find useful for building more powerful and reliable systems.

Blueprints

Guides you through the most common types of project you'll encounter, giving you end-to-end guidance on how to build your specific solution quickly and reliably.

Mastering

Take your skills to the next level with advanced tutorials that will give you confidence to master the tool's most powerful features.

Starting

Accessible to readers adopting the topic, these titles get you into the tool or technology so that you can become an effective user.

Progressing

Building on core skills you already have, these titles share solutions and expertise so you become a highly productive power user.