I am starting to work on a new project and want to start making use of the JSON capabilities of ColdFusion and a handful of JavaScript based widgets. I often find that I can find some great example over on Themeforest.net and I was not disappointed. A fantastic new Administration theme titled “Boo” (bootstrap based) was available that has great functionality and some of the best examples of the datatables.js script I have ever seen.
Creating this example was quite simple once I figured out how to present the data to datables. There was a sample text file formatted as such:
{ "aaData": [
[ ’1′, ‘Armand’, ‘Warren’, ’56045′, ‘Taiwan, Province of China’ ],
[ "2", "Xenos", "Salas", "71090", "Liberia" ],
[ "3", "Virginia", "Whitaker", "62723", "Nicaragua" ]
]}
ColdFusion doesn’t present JSON data in this manner by default and thanks to an example by Jon Joyce and a function (QuerytoArray) from Ben Nadel, this became trivial.
</cffunction>
<cffunction access="remote" name="getTestData" returntype="any">
<cfset var dc = "">
<cfquery name="dc">
Select id, fname, lname, zip, country
From dt_testdata_tbl
</cfquery>
<cfscript>
local.strData = StructNew();
local.strData[’aaData’] = QueryToArray(dc);
return local.strData;
</cfscript>
</cffunction>
The ajax call on the datatables looks like this:
sAjaxSource: "http://www.cfuser.com/library/demos/datatables/_cfcs/dataCall.cfc?method=getTestData&returnformat=json",
aoColumns: [
{ "mDataProp": "ID" , "sTitle": "ID"},
{ "mDataProp": "FNAME" , "sTitle": "Name"},
{ "mDataProp": "LNAME" , "sTitle": "Surname"},
{ "mDataProp": "ZIP" , "sTitle": "ZIP"},
{ "mDataProp": "COUNTRY" , "sTitle": "Country"}
The fully functional demo is at http://www.cfuser.com/library/demos/datatables/index.cfm.
