Basic coldfusion/lucee datatables server side example

AhmadZuwari

Its hard to find any coldfusion/lucee server side example that really work.

After a few try and error i got this work. So this should be basic example for noob like me

Version : 1.10.xxx
Language : Coldfusion/lucee
Database : Mssql

ok we have employee table with column: id,empno,empname,ic

pageA.cfm (scripting part only, you should now the rest here)

<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:'empname'},
                {title: "Emp.No",data:'empno'},
                {title: "IC",data:'ic'}
            ],
        language: {
            infoEmpty: "No records available",
        }

        })
   })
</script>

pageB.cfm (server side page)

<cfcontent reset="true">
<cfset listColumns = "id,empno,empname,ic" />
<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,empno,empname,ic 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'>
    empname <cfif form["order[0][dir]"] eq 'desc'>desc</cfif>
    </cfif>
    <cfif form["order[0][column]"] eq '2'>
    empno <cfif form["order[0][dir]"] eq 'desc'>desc</cfif>
    </cfif>
    <cfif form["order[0][column]"] eq '3'>
    ic <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.empname)#,
        "empno":#SerializeJSON(qFiltered.empno)#,
        "ic":
        <cfif trim(qFiltered.ic) neq '[empty string]'>
        #SerializeJSON(qFiltered.ic)#
        <cfelse>
        ""
        </cfif>
    }
</cfoutput> ]
<cfelse>
    ""
</cfif>
 }

Hope this help

colin

That will be helpful, thanks for sharing,

Colin

AhmadZuwari

appreciate it @colin

BTW how do i edit this post?

pageA.cfm (scripting part only, you should know the rest here)

colin

Standard users can only edit a post within 15 minutes of submitting - so that window has long since passed. As an admin, I can do it at any time, so feel free to message me or just say what you want changed,

Colin