Supercharging HTML tables with DataTables

Prototype CPRC DataTables interfaceIn the CHSS web team we have been building web interfaces that work with large amounts of data harvested from the University’s research database (PURE). The crucial thing about these interfaces is that they are ‘display’ interfaces – there is no interactivity with the underlying database as such, no ‘writing’ of the data. The emphasis on the interfaces is on displaying the data in a tabular format, allowing text to be searched and for the display of data to be filtered and sorted by particular fields (or columns).

Using standard methods of creating a web interface that communicates with a database using something like Perl DBI or PHP PDO to build a simple display-only interface has quite a few unneccessary overheads in terms of performance and complexity – and it turns out there is a technology that can be used to construct this sort of web interface in a much more simple and lightweight way, using DataTables.

DataTables is a plug-in for the jQuery JavaScript library, and is ‘client-side’ technology. There is no database, and no requirement even for server-side scripting technologies such as PHP. It’s a good way of displaying a large amount of ‘static’ data, that doesn’t need to change very much (perhaps being updated by some automated nightly re-indexing or harvesting mechanism) and is a monolithic ‘block’ of data (i.e. it is not in an entity-relationship structure).

DataTables can be thought of as a way of ‘supercharging’ standard HTML tables with extra functionality applied to data contained in a table (searching, filtering, ordering, conditional views and layouts, scrolling, pagination). The data used by DataTable exists in HTML table rows and cells, and all the data that is required for the web interface is loaded once when the browser requests the web page containing the interface, along with the web page HTML and JavaScript libraries – all subsequent searching, filtering and ordering of the data is done without further downloads, using client-side JavaScript alone. The DataTables JavaScript recognises the HTML table (which must be in ‘well-formedXHTML) using the DOM and an ‘ID’ attribute in the <table> tag. This is a very different approach from the standard methodology of re-loading the webpage every time a different view of the data is required (or perhaps using JSON or AJAX) and using a new connection to the database server to perform a new SQL transaction.

It might be thought that one disadvantage of the DataTables approach would be poor performance and slow loading of web pages that use DataTables with a large amount of data (DataTables automatically generates internal indexes dynamically) – but we have used the plug-in effectively for web interfaces that have hundreds of rows of data. At some point presumably the scale of the data would be such that this would become a problem, but for data on the order of hundreds of (and perhaps a few thousand) rows in size, DataTables seems to work well.

There is a middle-ground between the simplicity of client-side DataTables and the complexity of a standard web browser client/RDBMS server model, and that is using a relational database that resides purely on the client-side, such as SQLite.

Examples

  • 2. http://www.eddietest.hss.ed.ac.uk/wordpress/research/ This is a DataTables interface (incorporated into a WordPress theme template) that displays some sample PURE research data. This interface was a prototype developed by the web team for the CPRC website and shows the power of DataTables, combining it with a scrolling area, the jQuery UI Tabs library (for a tabbed interface) and the Column Filter add-on (to collapse and hide data so that it can then be expanded and displayed upon a user request) to make the interface feature-rich but compact.
  • 3. http://www.edwardboyle.com/drupal/summit_diary This interface (on my personal website) shows how DataTables works with a (reasonably) large dataset of 400 or so rows consisting of numerical, textual and date data types – displaying all of this data using a standard HTML table would be cumbersome, and DataTables allows the effective visualisation of such a large amount of data (using column sorting and scrolling) fairly easily, without a database.

1 thought on “Supercharging HTML tables with DataTables

  1. Hi Eddie,

    Great article, thanks!

    Is there an example of how to pull publication data from PURE that we can see the source for?

    I look after a custom built page for a group in the school of engineering. We’ve had our own publications database which we’ve maintained for a while now so we can showcase our research on our personal and project-specific web pages. It’s been a bit of a pain duplicating all of that data into PURE. Going forward, we don’t want to have enter new publications into two separate systems, so I’m very interested in updating our pages to pull the data directly from PURE.

    Kind regards,
    Richard.

Leave a Reply

Your email address will not be published. Required fields are marked *