asked on

Coldfusion Server Side Data tables.... once again :P

Hello again,

I posted a question in the past about this and basically gave up trying to work on this. Wouldn't you know it comes up again so I REALLY need to figure out how to get this working and how to work with this since its always coming up. I'm going to post everything I have for this.... Its funny because im not getting any errors its not even loading the jquery library.... These are located in the included header. I know its seeing the header properly as the style of the text is there. Thanks in advance ... I REALLY need some help understanding how this works.

ssDatatables.cfm

<cfinclude template="header.cfm">

<script type="text/javascript" charset="utf-8">
$(document).ready(function() {
    $('#displayData').dataTable( {
    "bProcessing": true,
    "bStateSave": true,
    "bServerSide": true,
    "sAjaxSource": "handler.cfm",
    "aoColumns": [ 
{"sName": "clientid", "sTitle": "ID", "sWidth": "20%", "bSortable": "true"},
{"sName": "firstname", "sTitle": "FirstName", "sWidth": "40%", "bSortable": "true"},
{"sName": "lastname", "sTitle": "LastName", "sWidth": "40%", "bSortable": "true"}
],
"sPaginationType": "full_numbers",
"aaSorting": [[1,'asc']],
"oLanguage": {
        "sLengthMenu": "Page length: _MENU_",
        "sSearch": "Filter:",
        "sZeroRecords": "No matching records found"
                                },
                                "fnServerData": function ( sSource, aoData, fnCallback ) {
        aoData.push(
            { "name": "table", "value": "tblActivity" },
            { "name": "sql", "value": "SELECT [clientid], [firstname], [lastname]" }
            );

            $.ajax( {"dataType": 'json',
                 "type": "POST",
                 "url": sSource,
                 "data": aoData,
                 "success": fnCallback} );


// $.getJSON( sSource, aoData, function (json) {fnCallback(json)} );
                        }
                } );
            } );
</script>

<h2>Data Tables Example</h2>

<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. 
    All changes are made inline, so there are no refreshes.</p>
<br/>

<table cellpadding="0" cellspacing="0" border="0" class="display" id="displayData"> 
    <thead>
        <tr>
            <th align="left">ID</th>
            <th align="left">Code</th>
            <th align="left">Location</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td colspan="3" class="dataTables_empty">Loading data from server</td> 
        </tr>
    </tbody>
</table>

Open in new window

handler.cfm

<cfsilent>
<cfparam name="form.table" default="">
<cfparam name="form.sColumns" default="">
<cfparam name="form.editButtonText" default="">
<cfparam name="form.editButtonTarget" default="">
<cfparam name="form.sSearch" default="">
<cfparam name="variables.fieldlist" default="">

<cfsetting showDebugOutput=false>
<cfsetting enablecfoutputonly="true">
<cfprocessingdirective suppresswhitespace="true">

<!--- this comes from the AJAX script in the template --->
<cfset variables.fieldlist=form.sColumns>
<cfset variables.count=0>

<!--- strip off the comma if it is the last element --->
<cfif right(variables.fieldlist,'1') EQ ",">
    <!--- last char is a comma --->
    <cfset variables.listLength = len(variables.fieldlist)>
    <cfset variables.fieldlist = left(variables.fieldlist, variables.listLength-1)>
</cfif>

<!--- get count of records --->
<cfquery name="qGetCount" datasource="#request.dsnLogin#" username="#request.dsnuLogin#" password="#request.dsnpLogin#">
    SELECT COUNT(*) AS fullCount
    FROM #form.table#
</cfquery>

<cfquery name="rResult" datasource="#request.dsnLogin#" username="#request.dsnuLogin#" password="#request.dsnpLogin#">
    #preservesinglequotes(form.sql)#
    FROM #form.table#

    WHERE 1 = 1
<cfif len(form.sSearch)>
        AND (
<cfloop from="1" to="#listLen(variables.fieldlist)#" index="variables.index">
#listGetAt(variables.fieldlist, variables.index,',')# LIKE '%#form.sSearch#%' <cfif variables.index LT listLen(variables.fieldlist)> OR </cfif>
</cfloop>
    )
</cfif>

<cfif isdefined('form.iSortCol_0')>
    ORDER BY
<cfloop from="0" to="#form.iSortingCols-1#" index="variables.i">
    #listGetAt(variables.fieldlist,form["iSortCol_#variables.i#"]+1)# #form["sSortDir_#variables.i#"]# <cfif variables.i is not form.iSortingCols-1>, </cfif>
</cfloop>

</cfif>
</cfquery>

<!--- strip off the table name from the values, otherwise it will break making the json --->
<cfset variables.fieldlist = ReplaceNoCase(variables.fieldlist,'#form.table#.','','all')>

<!--- create the JSON response --->
<cfsavecontent variable="variables.sOutput"><cfoutput>{
    "sEcho": #form.sEcho#,
    "iTotalRecords": #qGetCount.fullCount#,
    "iTotalDisplayRecords": #rResult.recordcount#,
    "aaData": [ 
    <cfloop query="rResult" startrow="#form.iDisplayStart+1#" endrow="#form.iDisplayStart+form.iDisplayLength#"><cfset variables.count=variables.count+1>
[<cfloop list="#variables.fieldlist#" index="variables.i">
<!--- custom translations --->
"#rResult[variables.i][rResult.currentRow]#"
<cfif variables.i is not listLast(variables.fieldlist)>, </cfif>
</cfloop>]

<cfif rResult.recordcount LT form.iDisplayStart+form.iDisplayLength>
    <cfif variables.count is not rResult.recordcount>,</cfif>
<cfelse>
    <cfif variables.count LT form.iDisplayLength>,</cfif>
</cfif>

</cfloop>
            ]
}</cfoutput></cfsavecontent>
</cfprocessingdirective>
</cfsilent>
<cfoutput>#variables.sOutput#</cfoutput>

Open in new window

2-4-2013-7-02-52-AM.jpg

gdemaria

I think you're so used to the issue, that you forgot to actually ask a question.

Your question title is about database tables, but you mention not loading jquery... what is the issue you're having?