CFML: DataTables server-side code example from website

data-tables-server-side-example.cfm

<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>