|
JQuery Datatables plugin example using a server side data request (coldfusion) |
||||||||
Im my previous article on how to use the JQuery datatables plug I concentrated mainly on the JQuery script, and how to build the AJAX request to receive a JSON response.
In this article I will demonstrate the full application which will include the front end JQuery, building the back end server response and a few tips that I've picked up since implementing the plugin. I am using an MS SQL table filled with UK location data that I also used for a weather webservice, to fill the table.
A full example of this working can be seen here: Data table server side example
The front end - JQuery
This is built using the JQuery datatables plugin. So firstly get the JQuery library from Google, and the Jquery plugin script. For this example we are also using the demo css provided by www.datatables.net.
2<sc/ript language="javascript" src="dataTables.js"></script>
3
4<style type="text/css" title="currentStyle">
5 @import "demo_page.css";
6 @import "demo_table.css";
7</style>
Next to actually build our data table object. We simply list any of the parameters that we want to pass to the dataTable method as name value pairs. I have been using this for a while now, and have settled on the options below. (I'm only explaining certain values, if you are unsure of them all, use the documentation on www.datatables.net)
The 'bStateSave' value is very handy as it tells the plugin to use Javascript cookie to remember any user filtering or sorting criteria. In this way page reloads do not reset the data display.
The 'bServerSide' value tells the dataTable that the data is coming froma server request.
The 'sAjaxSource' value tells the dataTable what template to query for a Json response.
The 'aoColumns' value builds an Array which sets up the actual rows in the dataTable. This is where you can set the width, and the headers for the display.
The last few options are dealing with the paging setup. They are text book ripped from www.datatables.net.
2$(document).ready(function() {
3 $('#displayData').dataTable( {
4 "bProcessing": true,
5 "bStateSave": true,
6 "bServerSide": true,
7 "sAjaxSource": "handler.cfm",
8 "aoColumns": [
9{"sName": "id", "sTitle": "ID", "sWidth": "20%", "bSortable": "true"},
10{"sName": "varCode", "sTitle": "Code", "sWidth": "40%", "bSortable": "true"},
11{"sName": "VarLocation", "sTitle": "Location", "sWidth": "40%", "bSortable": "true"}
12],
13"sPaginationType": "full_numbers",
14"aaSorting": [[1,'asc']],
15"oLanguage": {
16 "sLengthMenu": "Page length: _MENU_",
17 "sSearch": "Filter:",
18 "sZeroRecords": "No matching records found"
19 },
Next we need to actually send the request to the server. The 'fnServerData' function collates all the values, and allows you to add any other data you want here. Stick to the "name: value method" of passing data and you can't go wrong. In this example I am passing in a table value of 'ukLocationCode' and a SQL string. These values can be referenced as POST values in the data handling script.
Lastly I am using the &.ajax function to POST the data. I have left a commented out $.getJSON method to show the GET method. I am using POST as IE tends to cache the data results using GET requests.
2 aoData.push(
3 { "name": "table", "value": "ukLocationCodes" },
4 { "name": "sql", "value": "SELECT [id], [varCode], [varLocation]" }
5 );
6
7 $.ajax( {"dataType": 'json',
8 "type": "POST",
9 "url": sSource,
10 "data": aoData,
11 "success": fnCallback} );
12
13
14// $.getJSON( sSource, aoData, function (json) {fnCallback(json)} );
15 }
16 } );
17 } );
18</script>
The final part is the html display. The only thing to watch for here is that you give the table element the same id value as used in the script above.
2
3<p>This is the front end template for a data Tables example. It is handling the data(Json) from an AJAX post, and displaying it in a tabular view below.
4 All changes are made inline, so there are no refreshes.</p>
5<br/>
6
7<table cellpadding="0" cellspacing="0" border="0" class="display" id="displayData">
8 <thead>
9 <tr>
10 <th align="left">ID</th>
11 <th align="left">Code</th>
12 <th align="left">Location</th>
13 </tr>
14 </thead>
15 <tbody>
16 <tr>
17 <td colspan="3" class="dataTables_empty">Loading data from server</td>
18 </tr>
19 </tbody>
20</table>
The back end – Coldfusion response
My server response has been built using Coldfusion, but almost all of the principles here are not language specific. IE if you are doing this in PHP then watch out for the same sticking points.This script params all the POST values that it is expecting to ensure they exist. It is then performing two database queries. The first to get the total record count for the results. The second to actually get the data to go into the table. The second query uses a few of the values passed to it to determine if there are any filtering or sorting criteria being applied.
Lastly I use a create the Json response from the Query results. I am simply looping over the query records and outputting them in a Json format. Here it is also possible to intercept any specific values and apply custom formatting. In that way they are returned in exactly the right format for your dataTable display.
2<cfparam name="form.table" default="">
3<cfparam name="form.sColumns" default="">
4<cfparam name="form.editButtonText" default="">
5<cfparam name="form.editButtonTarget" default="">
6<cfparam name="form.sSearch" default="">
7<cfparam name="variables.fieldlist" default="">
8
9<cfsetting showDebugOutput=false>
10<cfsetting enablecfoutputonly="true">
11<cfprocessingdirective suppresswhitespace="true">
12
13<!--- this comes from the AJAX script in the template --->
14<cfset variables.fieldlist=form.sColumns>
15<cfset variables.count=0>
16
17<!--- strip off the comma if it is the last element --->
18<cfif right(variables.fieldlist,'1') EQ ",">
19 <!--- last char is a comma --->
20 <cfset variables.listLength = len(variables.fieldlist)>
21 <cfset variables.fieldlist = left(variables.fieldlist, variables.listLength-1)>
22</cfif>
23
24<!--- get count of records --->
25<cfquery name="qGetCount" datasource="A8002CMS">
26 SELECT COUNT(*) AS fullCount
27 FROM #form.table#
28</cfquery>
29
30<cfquery name="rResult" datasource="A8002CMS">
31 #preservesinglequotes(form.sql)#
32 FROM #form.table#
33
34 WHERE 1 = 1
35<cfif len(form.sSearch)>
36 AND (
37<cfloop from="1" to="#listLen(variables.fieldlist)#" index="variables.index">
38#listGetAt(variables.fieldlist, variables.index,',')# LIKE '%#form.sSearch#%' <cfif variables.index LT listLen(variables.fieldlist)> OR </cfif>
39</cfloop>
40 )
41</cfif>
42
43<cfif isdefined('form.iSortCol_0')>
44 ORDER BY
45<cfloop from="0" to="#form.iSortingCols-1#" index="variables.i">
46 #listGetAt(variables.fieldlist,form["iSortCol_#variables.i#"]+1)# #form["sSortDir_#variables.i#"]# <cfif variables.i is not form.iSortingCols-1>, </cfif>
47</cfloop>
48
49</cfif>
50</cfquery>
51
52<!--- strip off the table name from the values, otherwise it will break making the json --->
53<cfset variables.fieldlist = ReplaceNoCase(variables.fieldlist,'#form.table#.','','all')>
54
55<!--- create the JSON response --->
56<cfsavecontent variable="variables.sOutput"><cfoutput>{
57 "sEcho": #form.sEcho#,
58 "iTotalRecords": #qGetCount.fullCount#,
59 "iTotalDisplayRecords": #rResult.recordcount#,
60 "aaData": [
61 <cfloop query="rResult" startrow="#form.iDisplayStart+1#" endrow="#form.iDisplayStart+form.iDisplayLength#"><cfset variables.count=variables.count+1>
62[<cfloop list="#variables.fieldlist#" index="variables.i">
63<!--- custom translations --->
64"#rResult[variables.i][rResult.currentRow]#"
65<cfif variables.i is not listLast(variables.fieldlist)>, </cfif>
66</cfloop>]
67
68<cfif rResult.recordcount LT form.iDisplayStart+form.iDisplayLength>
69 <cfif variables.count is not rResult.recordcount>,</cfif>
70<cfelse>
71 <cfif variables.count LT form.iDisplayLength>,</cfif>
72</cfif>
73
74</cfloop>
75 ]
76}</cfoutput></cfsavecontent>
77</cfprocessingdirective>
78</cfsilent>
79<cfoutput>#variables.sOutput#</cfoutput>
Points of note
- Make sure that there is no whitespace in the beginning of your Json response. If there is then some browsers will not interpret it (I'm looking at you IE 6/7)
- Watch out for trailing commas after your data elements In your Json. Firefox will compensate for them, but IE thinks there is a missing element so will not display any data at all.
- Use www.jsonlint.com to validate your Json
- Use firebug for firefox, or http://www.charlesproxy.com/ to track the inline AJAX requests and responses. Both these tools are invaluable
- Your AJAX requests will still be subject to an Application (.cfm/.cfc) code that they inherit. In one example of this code I had four random lines of whitespace appearing that were actually in an Application.cfm file further down my folder structure.
that example will call a function in an extended cfc that will automatically convert a coldfusion query to the correct json format for both the dataTables plugin and jqGrid.
Thanks again this helped out a lot.
There is another option to the dataTable JQuery, (editable) that uses the JEditable plugin. http://www.datatables.net/examples/api/editable.ht...
This looks like you can have table edits submit back to the server when the user has changed the value.
The last time I did something like this it was in Flex. There is a datagrid object, and you can set an attribute 'editable=true', which allows you to edit records inline and a listener event fires the results to a webservice object.
Will be interesting to see what you put together.
The contents of the 'handler.cfm' file is the last set of code in this entry, the coldfusion that starts with 'cfsilent'. Just copy and past that into your cfm server side file and it should work. You'll need to change a few values, like the query etc, but its mostly generic.
Yes, this works with a multi filtering table, and even with redrawing the same table, with different values, as I've discovered in the lat week or so.
Shaun
Do you have an online version of the app that I could take a look at? If you are not seeing any errors it may be because they are in the Json response, tracking the server side response in firebug or charles is key with AJAX requests. If your handler template breaks you'll never see it in the browser.