|
Using the JQuery dataTables plugin to display dynamic data in tables: part 1 |
||||||||
Rather than writing out long winded table code to display your data in a tabulated fashion why not use the dataTables JQuery plugin to do it for you?
In this blog entry I'll be generating tables using a JQuery plugin, but I will also be generating the JQuery code from an XML document.
The theory behind the dataTables JQuery plugin is that when the template loads it makes an AJAX request to a remotely specified template. That template returns a JSON object of data which is formatted and used in a tabular display. This means that you can perfom filtering and sort functions inline, and the JQuery simply re submits the AJAX request, receiving new JSON each time. So no refreshing.
I'll be dealing with the auto generation of the JSON back end in the second part of this article. Here is how I setup the tabular display.
Build a standard html template, including the CSS and JQuery plugins.
2<html lang="en-GB">
3<head>
4<InvalidTag http-equiv="Content-Type" content="text/html; charset=iso-8859-1" lang="en">
5<InvalidTag name="language" content="en-GB">
6
7<style type="text/css" title="currentStyle">
8 @import "demo_page.css";
9 @import "demo_table.css";
10</style>
11
12<s/cript type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
13<s/cript type="text/javascript" language="javascript" src="jquery.dataTables.js"></script>
Next we build our JQuery function. I wont go into massive detail about all the parameters and values being passed in here, but it is well documented on http://www.datatables.net/.
2$(document).ready(function() {
3 $('#example').dataTable( {
4 "bProcessing": true,
5 "bServerSide": true,
6 "sAjaxSource": "content.cfm",
7 "aoColumns": [
8{ "sName": "Edit", "sTitle": "Edit", "sWidth":"10%"} ,
9{ "sName": "Band", "sTitle": "Band"} ,
10{ "sName": "Genre","sTitle": "Genre"} ,
11{ "sTitle": "Fake Column"}
12],
13 "sPaginationType": "full_numbers",
14 "aaSorting": [[1,'asc']],
15 "oLanguage": {
16 "sLengthMenu": "Page length: _MENU_",
17 "sSearch": "Filter:"
18 }
19 } );
20 } );
21</script>
The really important column here is the "aoColumns" JSON data block. This specifies what fields are returned from your AJAX call, and parameters they must adhere to.
In this example we are anticipating that we will receive four columns of data back (Edit,Band,Genre and Fake Column).
Lastly we create a table with an ID of "example", as this is what the JQuery is looking for. This table must be formatted in a certain way, as the JQuery plugin will re write the specified elements.
2<thead>
3<tr>
4 <th>Edit</th>
5 <th>Band</th>
6 <th>Genre</th>
7 <th>Fake Column</th>
8</tr>
9</thead>
10 <tbody>
11 <tr>
12 <td colspan="3" class="dataTables_empty">Loading data from server</td>
13 </tr>
14 </tbody>
15</table>
The code also contains the header elements that will match the returned column values from the AJAX request. The last part of the table is displayed when the data is loading.
This all works well, but to extend it further I have altered the code to read from an XML document. The XML document Is loaded when the template starts, and the data fields and attributes are read, and looped over to create the JQuery code and table headers. In this way it is a generic table display template, driven from an XML document.
The XML doc:
2 <form>
3 <field sName="Edit" source="data" sTitle="Edit" sWidth="10%">Edit</field>
4 <field sName="Band" source="data" sTitle="Band">Band</field>
5 <field sName="Genre" source="data" sTitle="Genre">Genre</field>
6 <field sName="fake column" source="" sTitle="Fake Column">Fake column</field>
7 </form>
Read the XML file and parse it out into an Array:
2<cfset variables.xml = XMLParse("test.xml") />
3<!--- <cfdump var="#variables.xml#" label="Raw xml document"> --->
4<cfset variables.fields = XMLSearch(variables.xml,"form/field")>
5<cfset variables.totalRecords = ArrayLen(variables.fields)>
Use something like this to dynamically generate the JQuery and table values:
2<cfloop index="variables.index" from="1" to="#ArrayLen(variables.fields)#">
3 { <cfif variables.fields[variables.index].XmlAttributes.source EQ "data">"sName": "#variables.fields[variables.index].XmlAttributes.sName#",</cfif>
4 <cfif structkeyexists(variables.fields[variables.index].XmlAttributes, 'sTitle')>"sTitle": "#variables.fields[variables.index].XmlAttributes.sTitle#"</cfif>
5 <cfif structkeyexists(variables.fields[variables.index].XmlAttributes, 'sWidth')>,"sWidth":"#variables.fields[variables.index].XmlAttributes.sWidth#"</cfif>
6 } <cfif variables.index NEQ variables.totalRecords>,</cfif>
7</cfloop>
8</cfoutput>
9<!--- table values --->
10
11<cfoutput>
12 <cfloop index="variables.index" from="1" to="#ArrayLen(variables.fields)#">
13 <th align="left">#variables.fields[variables.index].XmlText#</th>
14 </cfloop>
15</cfoutput>
The JSON response is hard coded in this example, so the result will not filter or search. I'll handle that in article two.
There is a full example of this here.
"The theory behind the dataTables JQuery plugin is that when the template loads it makes an AJAX request to a remotely specified template."
...that seems to imply that the plugin REQUIRES you to provide the table data via AJAX, which isn't the case: you can simply output an HTML table in your page and then apply the plugin to that table to provide filtering, sorting, and pagination.
Certainly there are some cases where requesting the data via AJAX makes sense (especially with large data sets), but other times it's overkill.
dataTables is a great plugin, though, definitely my current favorite for managing tabular data.
Thats a good point, one I had overlooked. I don't think I can remember the last time I wrote a table that wasn't data driven! That being siad it is still very handy functionality, I'll give it a go, and see how it works without the AJAX call.
Thanks
Shaun
That way, if the user for some reason has Javascript turned off, they at least get a "dumb" table of data. If the data in the table is only ever provided by an AJAX call, folks with Javascript turned off (a rarity, admittedly) will just get an empty table.
I plan on writing a second article dealing with building the JSON response as well.
One exmple i saw said to do this...but I couldn't get it to work. I think I'm missing something small..
if (typeof oTable == 'undefined') {
oTable = $('#commentstable').dataTable({
"bProcessing": true,
"bServerSide": true,
"iDisplayLength": 150,
"bLengthChange": false,
"sAjaxSource": "datatables_comments_list.php"
});
} else {
oTable.fnClearTable( 0 );
oTable.fnDraw();
}
seems unclear what
if (typeof oTable == 'undefined') --is checking against exactly..
Any help would be much appreciated.
Thanks,
Tim
Thanks for the feedback, I've been meaning to write the second part, so here it is:
http://www.mccran.co.uk/index.cfm/2010/4/29/JQuery...
It is pretty much and end to end example, using Coldfusion but you should be able to see what is happening. The Json response from the server relies more on web standards than any sort of proprietary code.