This example was based on this link
https://datatables.net/forums/discussion/40613/datatable-jquery-server-side-with-adobe-cold-fusion-and-sql-server
and i am using datatable version 1.10
On first page load, all data sucessfully loaded into #formsTable
But it wont work when clicking sorting,searching and paging.
It just hang with 'processing..'
pageA.cfm (only show scripting part here...)
<script type="text/javascript">
$(document).ready(function(){
$('#formsTable').DataTable({
processing:true,
serverSide:true,
ajax:{
url:'pageB.cfm'
},
columns:[
{title: "id",data:'id'},
{title: "Name",data:'name'},
{title: "Emp.No",data:'empno'},
{title: "IC",data:'ic'}
]
})
})
</script>
pageB.cfm (server-side)
<cfcontent reset="true">
<cfset listColumns = "id,emp_no,emp_name,number_id2" />
<cfset sIndexColumn = "id" />
<cfparam name="draw" default="1" type="integer" />
<cfparam name="start" default="0" type="integer" />
<cfparam name="length" default="10" type="integer" />
<cfparam name="url.sSearch" default="" type="string" />
<cfparam name="url.iSortingCols" default="0" type="integer" />
<!--- query data --->
<cfquery datasource="hrms" name="qFiltered">
select id,emp_no,emp_name,number_id2 from employee
<cfif len(trim(url.sSearch))>
Where
(
<cfloop list="#listColumns#" index="thisColumn">
<cfif thisColumn neq listFirst(listColumns)>
OR
</cfif>
#thisColumn# LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#trim(url.sSearch)#%" />
</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>
<!--- query data count --->
<cfquery dbtype="query" name="qCount">
SELECT COUNT(#sIndexColumn#) as total
FROM qFiltered
</cfquery>
<!--- Output --->
{"draw": <cfoutput>#val(draw)#</cfoutput>,
"recordsTotal": <cfoutput>#qCount.total#</cfoutput>,
"recordsFiltered": <cfoutput>#qFiltered.recordCount#</cfoutput>,
"aaData": [
<cfoutput query="qFiltered" startrow="#val(start+1)#" maxrows="#val(length)#">
<cfif currentRow gt (start+1)>,</cfif>
{
"id":#SerializeJSON(qFiltered.currentrow)#,
"name":#SerializeJSON(qFiltered.emp_name)#,
"empno":#SerializeJSON(qFiltered.emp_no)#,
"ic":
<cfif trim(qFiltered.number_id2) neq '[empty string]'>
#SerializeJSON(qFiltered.number_id2)#
<cfelse>
""
</cfif>
}
</cfoutput> ] }
I am stuck here , it seems that something is missing on pageB.cfm ?
Edited: thanks for @user12031119 pointing out that sent parameter from pageA.cfm to pageB.cfm is different now with v1.10, so basically these parameters should be change :-
sEcho : draw
iDisplayStart : start
iDisplayLength : length
iTotalRecords : recordsTotal
iTotalDisplayRecords : recordsFiltered
with that change i can now click on next/prev page (paging) but still search and sorting dont work
OK finally it works now.
So this is it, basic datatables.net coldfusion/lucee server side example.
Datatables Version 1.10.xx
Language Coldfusion/lucee
pageA.cfm (scripting)
<script type="text/javascript">
$(document).ready(function(){
$('#formsTable').DataTable({
processing:true,
serverSide:true,
ajax:{
url:'pageB.cfm',
type :'post'
},
columns:[
{title: "id",data:'id'},
{title: "Name",data:'name'},
{title: "Emp.No",data:'empno'},
{title: "IC",data:'ic'}
],
language: {
infoEmpty: "No records available",
}
})
})
</script>
pageB.cfm
<cfcontent reset="true">
<cfset listColumns = "id,emp_no,emp_name,number_id2" />
<cfset sIndexColumn = "id" />
<cfparam name="draw" default="1" type="integer" />
<cfparam name="start" default="0" type="integer" />
<cfparam name="length" default="10" type="integer" />
<cfparam name="search" default="" type="string" />
<cfif len(form["search[value]"]) gt 0>
<cfset search=form["search[value]"]>
</cfif>
<!--- Data set after filtering --->
<cfquery datasource="hrms" name="qFiltered">
select id,emp_no,emp_name,number_id2 from employee
<cfif len(trim(search))>
where
(
<cfloop list="#listColumns#" index="thisColumn">
<cfif thisColumn neq listFirst(listColumns)>
OR
</cfif>
#thisColumn# LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#trim(search)#%" />
</cfloop>
)
</cfif>
<cfif form["order[0][column]"] gt 0>
ORDER BY
<cfif form["order[0][column]"] eq '1'>
emp_name <cfif form["order[0][dir]"] eq 'desc'>desc</cfif>
</cfif>
<cfif form["order[0][column]"] eq '2'>
emp_no <cfif form["order[0][dir]"] eq 'desc'>desc</cfif>
</cfif>
<cfif form["order[0][column]"] eq '3'>
number_id2 <cfif form["order[0][dir]"] eq 'desc'>desc</cfif>
</cfif>
</cfif>
</cfquery>
<!--- Total data set length --->
<cfquery dbtype="query" name="qCount">
SELECT COUNT(#sIndexColumn#) as total
FROM qFiltered
</cfquery>
<cfif qFiltered.recordcount gt 0>
<cfset recordsTotal=#qCount.total#>
<cfelse>
<cfset recordsTotal=0>
</cfif>
<!---
Output
--->
{"draw": <cfoutput>#val(draw)#</cfoutput>,
"recordsTotal": <cfoutput>#recordsTotal#</cfoutput>,
"recordsFiltered": <cfoutput>#qFiltered.recordCount#</cfoutput>,
"data":
<cfif qFiltered.recordcount gt 0>
[
<cfoutput query="qFiltered" startrow="#val(start+1)#" maxrows="#val(length)#">
<cfif currentRow gt (start+1)>,</cfif>
{
"id":#SerializeJSON(qFiltered.currentrow)#,
"name":#SerializeJSON(qFiltered.emp_name)#,
"empno":#SerializeJSON(qFiltered.emp_no)#,
"ic":
<cfif trim(qFiltered.number_id2) neq '[empty string]'>
#SerializeJSON(qFiltered.number_id2)#
<cfelse>
""
</cfif>
}
</cfoutput> ]
<cfelse>
""
</cfif>
}