Data Tables and DataTables Plugin in jQuery 1.3 with PHP

In this article by Kae Verens, we will look at:

How to install and use the DataTables plugin

How to load data pages on request from the server

Searching and ordering the data

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.

The DataTables plugin allows sorting, filtering, and pagination on your data.

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:

data-tables-and-datatables-plugin-jquery-13-php-img-0

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:

<html> 
  <head> 
    <script src="../jquery.min.js"></script> 
    <script src="../datatables/media/js/jquery.dataTables.js">
    </script> 
    <style type="text/css">
      @import "../datatables/media/css/demo_table.css";</style> 
    <script> 
      $(document).ready(function(){ 
        $('#the_table').dataTable(); 
      }); 
    </script> 
  </head> 
  <body> 
    <div style="width:500px"> 
      <table id="the_table"> 
        <thead> 
          <tr>
            <th>Artist / Band</th><th>Album</th><th>Song</th>
          </tr> 
        </thead> 
        <tbody> 
          <tr><td>Muse</td>
              <td>Absolution</td>
              <td>Sing for Absolution</td>
          </tr> 
          <tr><td>Primus</td>
            <td>Sailing The Seas Of Cheese</td>
            <td>Tommy the Cat</td>
          </tr> 
          <tr><td>Nine Inch Nails</td>
              <td>Pretty Hate Machine</td>
              <td>Something I Can Never Have</td>
          </tr> 
          <tr><td>Horslips</td>
            <td>The Táin</td>
            <td>Dearg Doom</td>
          </tr> 
          <tr><td>Muse</td>
              <td>Absolution</td>
              <td>Hysteria</td>
          </tr> 
          <tr><td>Alice In Chains</td>
            <td>Dirt</td>
            <td>Rain When I Die</td>
          </tr> 
          <!-- PLACE MORE SONGS HERE -->
        </tbody> 
      </table> 
    </div> 
  </body> 
</html>

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

data-tables-and-datatables-plugin-jquery-13-php-img-1

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:

data-tables-and-datatables-plugin-jquery-13-php-img-2

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

data-tables-and-datatables-plugin-jquery-13-php-img-3

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 database I'm using in the example is MySQL (http://www.mysql.com/).

It is trivial to convert the example to use any other database, such as PostgreSQL or SQLite.

For your use, here is a short list of large data sets:

http://wordlist.sourceforge.net/—Links to collections of words.

http://www.gutenberg.org/wiki/Gutenberg:Offline_Catalogs—A list of books placed online by Project Gutenburg.

http://www.world-gazetteer.com/wg.php?men=stdl—A list of all the cities in the world, including populations.

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:

<html> 
  <head> 
    <script src="../jquery.min.js"></script> 
    <script src="../datatables/media/js/jquery.dataTables.js">
    </script> 
    <style type="text/css"> 
      @import "../datatables/media/css/demo_table.css"; 
      table{width:100%} 
    </style> 
    <script> 
      $(document).ready(function(){ 
        $('#the_table').dataTable({ 
          'sAjaxSource':'get_data.php'
        }); 
      }); 
    </script> 
  </head> 
  <body> 
    <div style="width:500px"> 
      <table id="the_table"> 
        <thead> 
          <tr>
            <th>Country</th>
            <th>City</th>
            <th>Latitude</th>
            <th>Longitude</th>
          </tr> 
        </thead> 
        <tbody> 
        </tbody> 
      </table> 
    </div> 
  </body> 
</html>

In this example, we've added a parameter to the .dataTable call, sAjaxSource, which is the URL of the script that will provide the data (the file will be named get_data.php).

Server-side code

On the server side, we will start off by providing the first ten rows from the database.

DataTables expects the data to be returned as a two-dimensional array named aaData.

In my own database, I've created a table like this:

CREATE TABLE `cities` ( 
  `ccode` char(2) DEFAULT NULL, 
  `city` varchar(87) DEFAULT NULL, 
  `longitude` float DEFAULT NULL, 
  `latitude` float DEFAULT NULL, 
  KEY `city` (`city`(5)) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Most of the searching will be done on city names, so I've indexed city.

Initially, let's just extract the first page of information. Create a file called get_data.php and save it in the same directory as tables.html:

<?php 
// { initialise variables 
  $amt=10; 
  $start=0; 
// } 
// { connect to database 
  function dbRow($sql){ 
    $q=mysql_query($sql); 
    $r=mysql_fetch_array($q); 
    return $r; 
  } 
  function dbAll($sql){ 
    $q=mysql_query($sql); 
    while($r=mysql_fetch_array($q))$rs[]=$r; 
    return $rs; 
  } 
  mysql_connect('localhost','username','password'); 
  mysql_select_db('phpandjquery'); 
// } 
// { count existing records 
  $r=dbRow('select count(ccode) as c from cities'); 
  $total_records=$r['c']; 
// } 
// { start displaying records 
  echo '{"iTotalRecords":'.$total_records.',
         "iTotalDisplayRecords":'.$total_records.',
         "aaData":['; 
  $rs=dbAll("select ccode,city,longitude,latitude from cities 
             order by ccode,city limit $start,$amt"); 
  $f=0; 
  foreach($rs as $r){ 
    if($f++) echo ','; 
    echo '["',$r['ccode'],'",
           "',addslashes($r['city']),'",
           "',$r['longitude'],'",
           "',$r['latitude'],'"]'; 
  } 
echo ']}'; 
// }

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.