DataTables logo DataTables

This site contains the legacy documentation for DataTables v1.9 and earlier for reference only.
DataTables 1.9 was End Of Life in 2014. Do not use it for new work.
The current release of DataTables can always be found on DataTables.net.

Server-side processing | ColdFusion - cfscript

Important - Do not use this script for new development - it will not work! It is no longer maintained and does not support DataTables 1.10+'s server-side processing mode. This page is retained only for legacy users and it will likely be removed in the near future.

This script is kindly provided by John Fournier of South Carolina Educational Television. and is a modification of the other ColdFusion server-side processing script that is available to be more concise through the use of the cfscript tag.

Feature set

Code


   /*
      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#]
  }');