Skip to content

Instantly share code, notes, and snippets.

@mikesprague
Last active May 5, 2020 14:34
Show Gist options
  • Save mikesprague/5985661 to your computer and use it in GitHub Desktop.
Save mikesprague/5985661 to your computer and use it in GitHub Desktop.
CFML: DataTables server-side code example from website
<cfscript>
/*
Script: DataTables server-side script for ColdFusion (short script style) and MySQL
License: GPL v2 or BSD (3-point)
ReWrite: 12/12/2011 John Fournier
Notes: Adobe ColdFusion 9 + limited inline documentation used, see other long examples for explanation
*/
datasource = 'jQueryDTable'; // set to your ColdFusion database
sTable = 'ajax'; // your table
aColumns = ['engine','browser','platform','version','grade']; // your columns
iColumnsLen = ArrayLen(aColumns);
sIndexColumn = 'id';
param name='URL.sEcho' default='' type='string';
param name='URL.iDisplayStart' default='0' type='string'; //could be type integer, we'll force integer anyway in next step
param name='URL.iDisplayLength' default='10' type='string';
param name='URL.sSearch' default='' type='string';
param name='URL.iSortCol_0' default='0' type='string'; // 0 indexed sort column number
param name='URL.sSortDir_0' default='asc' type='string';
iDisplayStart = Int(Val(URL.iDisplayStart));
iDisplayLength = Int(Val(URL.iDisplayLength));
sSearch = Trim(URL.sSearch);
iSortCol_0 = Int(Val(URL.iSortCol_0));
iSortCol_0 = (iSortCol_0 GTE iColumnsLen) ? 0 : iSortCol_0; // sort column must be less than column count
sSortDir_0 = (LCase(URL.sSortDir_0) EQ 'asc')? 'asc' : 'desc';
sWhere = '';
if (sSearch NEQ '') {
sWhere = 'WHERE (';
for (i=1; i<= iColumnsLen; i++) {
sWhere &= "#aColumns[i]# LIKE '%#sSearch#'";
sWhere &= (i LT iColumnsLen) ? ' OR ' : '';
}
sWhere &= ')';
}
sOrder = (iSortCol_0 NEQ 0) ? 'ORDER BY #aColumns[iSortCol_0 + 1]# #sSortDir_0#' : '';
sLimit = (iDisplayLength NEQ 0) ? 'LIMIT #iDisplayStart#,#iDisplayLength#' : '';
q = new Query();
q.setdatasource(datasource);
q.setsql("SELECT SQL_CALC_FOUND_ROWS #ArrayToList(aColumns)# FROM #sTable# #sWhere# #sOrder# #sLimit#");
qResult = q.execute().getresult();
q.setsql("SELECT FOUND_ROWS() AS Total");
qCount = q.execute().getresult();
savecontent variable="aaData" {
for (i=1; i <= qResult.RecordCount; i++) {
writeOutput('[');
for (col=1; col <= iColumnsLen; col++) {
// the following line contains a conditional specific to this example
writeOutput((aColumns[col] EQ 'version') ? '"-"' : '"#jsStringFormat(qResult[aColumns[col]][i])#"');
writeOutput((col NEQ iColumnsLen) ? ',' : '');
}
writeOutput(']');
writeOutput((i NEQ qResult.RecordCount) ? ',' : '');
}
};
writeOutput('{
"sEcho": #Int(Val(URL.sEcho))#,
"iTotalRecords": #qCount.total#,
"iTotalDisplayRecords": #qResult.recordCount#,
"iTotalRecords": #qCount.total#,
"aaData": [#aaData#]
}');
</cfscript>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment