|
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.
It also has me wondering how I might be able to add custom filtering with server side processing (such as http://datatables.net/forums/comments.php?Discussi...).
I have no trouble with the SQL part, I just wouldn't know where to start with posting the custom filter fields to the server and sending the response back to the browser.
Have you looked into this at all?
Thanks.
Thanks for the comments, they are always appreciated. I've done exactly what you linked to on another project. I created a set of form fields to filter on, then used the JQuery serialise method to fire all the form values at the ajax handler to return filtered data.
I'll look for the code and post it, it was using the datatables API to destroy then recreate the data within the ajax handler.
Shaun
How can I open cfwindow from the datatables grid? I am using coldfusion 9.
Are you trying to get a lightbox pop up from a link in the datatable? If so then I would use a JQuery plugin like Fancybox (http://fancybox.net/) rather than CFWindow, as this is much more flexible, and you will be able to have greater control over it.
Thanks in advance.
also I have to pass the ID fields as hidden to the fancybox window.
Thanks again.
I've had a think about what you were trying to do, and put together an example, have a look through this blog entry: http://www.mccran.co.uk/index.cfm/2011/5/19/JQuery...
I think it does just what you want.
On another note, 10 years ago I started out programming in CF and switched to PHP shortly thereafter. While PHP is a terrible, messy, hacked lanaguage, CF somehow looks even worse! Needing to wrap simple conditionals in <cf...> is absurd. Well, anyway, I've moved on to Groovy, Ruby, & Python, scripting lang royalty.
CF continues to exist, so there is a market for it, clearly. I guess in the end it just needs to work, and CF must be more than adequate in this respect. Coding enjoyment, not so sure....
Glad you enjoyed the Datatables blog post, I agree with you thinking, the ability that JQuery plugins gives developers to quickly and painlessly deliver functionality is a game changer.
ColdFusion can look elegant, I think a lot of what makes a language useable or not relies on more than just the syntax, for example if you are OO, or using a framework. To apply that to your example I've almost entirely done away with conditional Cfif statements using ORM ond OO.
I biuld first my colums
loop
...
<cfset colCount += 1><cfsavecontent variable="col#colCount#">...</cfsavecontent>
<cfset colCount += 1><cfsavecontent variable="col#colCount#">...</cfsavecontent>
....
<cfset contentArr = arrayNew(1)>
<cfloop from="1" to="#colCount#" index="idx">
<cfset arrayAppend(contentArr, evaluate("col"&idx))>
</cfloop>
<cfset arrayAppend(data, contentArr)>
...
/loop
<cfoutput>{ "aaData": #serializejson(data)#}</cfoutput>
Thanks for the CF example for processing DataTables.
My two cents:
For security purposes I would advise against exposing the database table's name on the client's end.
{ "name": "table", "value": "ukLocationCodes" }
Best to keep this in the ajax coldFusion script.
@various, what do you mean by 'pipelining' it?
Change
<cfif variables.count is not rResult.recordcount>,</cfif>
to
<cfif variables.count is not rResult.recordcount AND rResult.recordcount NEQ rResult.currentRow>,</cfif>
and the button 'Last' will work.
My code is like:
<!--- Data set after filtering --->
<cfquery datasource="#coldfusionDatasource#" name="qFiltered">
SELECT #listColumns#
FROM #sTableName# WHERE Contact_GroupID = 1<!---my code--->
<cfif len(trim(url.sSearch))>
WHERE
<cfloop list="#listColumns#" index="thisColumn"><cfif thisColumn neq listFirst(listColumns)> OR </cfif>#thisColumn# LIKE <cfif thisColumn is "version"><!--- special case ---><cfqueryparam cfsqltype="CF_SQL_FLOAT" value="#val(url.sSearch)#" /><cfelse><cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#trim(url.sSearch)#%" /></cfif></cfloop>
</cfif>
<cfif url.iSortingCols gt 0>
ORDER BY <cfloop from="0" to="#url.iSortingCols-1#" index="thisS"><cfif thisS is not 0>, </cfif>#listGetAt(listColumns,(url["iSortCol_"&thisS]+1))# <cfif listFindNoCase("asc,desc",url["sSortDir_"&thisS]) gt 0>#url["sSortDir_"&thisS]#</cfif> </cfloop>
</cfif>
</cfquery>
Nice work though :)
http://adgcq.ph2.ca/contacts.cfm
Pat
<cfif variables.count is not rResult.recordcount>,</cfif>
adds an extra "," at the end of the JSON which makes the dataTable to fail.
This happens sometimes when going to the last page.
Here is an example of how the JSON ends:
{
"sEcho": 5,
"iTotalRecords": 8427,
"iTotalDisplayRecords": 36,
"aaData": [
[
"Kondapi Ravi"
,
"50"
]
,
]
}
As you can see an extra "," is being put in place
I like the way you handle the information and create the JSON.
I would only like to advice a small modification
I would replace:
"#rResult[variables.i][rResult.currentRow]#"
with:
<CFSET outputResults = ReplaceNoCase(rResult[variables.i][rResult.currentRow],'"','', 'ALL' ) />
"#outputResults#"
or
"#ReplaceNoCase(rResult[variables.i][rResult.currentRow],'"','', 'ALL' )#"
that fails.
This works:
<CFSET outputResults = ReplaceNoCase(rResult[variables.i][rResult.currentRow],'"','', 'ALL' ) />
"#outputResults#"
I'd experienced similar problems with ending the JSON generation block as well. It took a fair bit of tinkering and moving back and forth through the paging options to test all the variation but I ended out somewhere near your solution as well.
This is the classic case of not updating the original blog article!
Use a Tool like Charles Proxy to debug your JSON requests, it makes life a lot easier.
I couldn't see your example as it requires me to login.
thanks
Pat
Also please check the below links:
http://www.wholesaleledlights.co.uk/
http://www.ledtape.co.uk/
http://www.ledstriplights.co.uk/
http://jualjamtangankwsupermurah.com
http://tokositataswanita-online.com/
likes kaufen
http://socialgrand.com/buy-instagram-likes/
3 $('#displayData').dataTable( {
are you sure that displaydata is the right column?
http://www.theworkbootsdoctor.com/
<a href='https://www.facebook.com' target='_blank'>facebook</a>
<a href="http://mortgagebrokercalgarysearch.ca/">Mo... Broker Calgary</a>
rubber sheet available in different size rolls buy them from ukrubbersheet.co.uk
http://likesandfollowersclub.com/
http://morefollowersonline.net/
http://discountfollowers.com/
http://cheapfollowers.net/
Packers and Movers Pune @
http://www.expert5th.in/packers-and-movers-pune/
Packers and Movers Hyderabad @
http://www.expert5th.in/packers-and-movers-hyderab...
Packers and Movers Mumbai @
http://www.expert5th.in/packers-and-movers-mumbai/...
Packers and Movers Gurgaon @
http://www.expert5th.in/packers-and-movers-gurgaon...
Packers and Movers Bangalore @
http://www.expert5th.in/packers-and-movers-bangalo...
Packers and Movers Delhi @
http://www.expert5th.in/packers-and-movers-delhi/
Packers and Movers Noida @
http://www.expert5th.in/packers-and-movers-noida/
Packers and Movers Chennai @
http://www.expert5th.in/packers-and-movers-chennai...
Packers and Movers Thane @
http://www.expert5th.in/packers-and-movers-thane/
Packers and Movers Navi Mumbai @
http://www.expert5th.in/packers-and-movers-navimum...
Packers and Movers Faridabad @
http://www.expert5th.in/packers-and-movers-faridab...
Packers and Movers Ghaziabad @
http://www.expert5th.in/packers-and-movers-ghaziab...
http://laurasguideformen.wikidot.com/
Hampir setiap wanita selalu mendambakan memiliki payudara besar dan berisi. Umumnya kaum hawa merasa lebih seksi dan penuh percaya diri jika memiliki ukuran buah dada yang besar, Padahal masih banyak <b><a href='http://caragoogle.com/2015/12/cara-membesarkan-pay...'>Cara Membesarkan Payudara Secara Alami Tanpa Efek Samping</a></b> yang bisa di tempuh. Cara-cara tersebut layak untuk dicoba karena menggunakan bahan-bahan alami yang bisa kita jumpai di rumah.
Biasanya bekas luka terjadi karena bekas jerawat, bakar, terjatuh, kecelakaan atau setelah melakukan operasi. Banyak yang telah mencoba berbagai <b><a href='http://caragoogle.com/2015/12/8-cara-mudah-menghil...'>Cara Mudah Menghilangkan Bekas Luka</a></b> tetapi hasilnya tidak seperti yang diharapkan. Sebenarnya cara menghilangkan bekas luka dapat dihilangkan dengan cepat, asalkan dibarengi dengan niat dan tekad yang kuat. Sehingga bekas luka menjadi lebih cepat dihilangkan dengan mudah.