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 | PHP (CakePHP)

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 from Matt Hughes provides DataTables server-side processing for CakePHP. Broken down into the MVC components of CakePHP, configuration of the data the DataTable will work with from the database is done in the Model.

Feature set

View

<?php echo $this->Html->script('jquery.dataTables'); ?>

<script type="text/javascript">
	$(document).ready(function() {
		$('#browserList').dataTable({
			"bProcessing": true,
			"bServerSide": true,
			"sAjaxSource": "<?php echo $this->Html->Url(array('controller' => 'Grid', 'action' => 'ajaxData')); ?>"
		});
	});
</script>

<h1>Browser List</h1>

<table id="browserList">
	<thead>
		<tr>
			<th>Title</th>
			<th>Description</th>
			<th>Release Year</th>			
			<th>Rating</th>			
		</tr>
	</thead>
	<tbody>
		<tr>
			<td colspan="4" class="dataTables_empty">Loading data from server...</td>
		</tr>
	</tbody>
</table>

Controller

<?php
class GridController extends AppController {
	
	function index() { }
	
	function ajaxData() {
		$this->modelClass = "Browser";
		$this->autoRender = false;			
		$output = $this->Browser->GetData();
		
		echo json_encode($output);
	}
}
?>

Model

<?php
class Browser extends AppModel {
	public function GetData() {
		/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
		 * Easy set variables
		*/
		
		/* Array of database columns which should be read and sent back to DataTables. Use a space where
		 * you want to insert a non-database field (for example a counter or static image)
		*/
		$aColumns = array( 'engine', 'browser', 'platform', 'version', 'grade' );
		
		/* Indexed column (used for fast and accurate table cardinality) */
		$sIndexColumn = "id";
		
		/* DB table to use */
		$sTable = "ajax";
		
		App::uses('ConnectionManager', 'Model');
		$dataSource = ConnectionManager::getDataSource('default');
		
		/* Database connection information */
		$gaSql['user']       = $dataSource->config['login'];
		$gaSql['password']   = $dataSource->config['password'];
		$gaSql['db']         = $dataSource->config['database'];
		$gaSql['server']     = $dataSource->config['host'];
		
		
		/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
		 * If you just want to use the basic configuration for DataTables with PHP server-side, there is
		* no need to edit below this line
		*/
		
		/*
		 * Local functions
		*/
		function fatal_error ( $sErrorMessage = '' )
		{
			header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
			die( $sErrorMessage );
		}
		
		
		/*
		 * MySQL connection
		*/
		if ( ! $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  ) )
		{
			fatal_error( 'Could not open connection to server' );
		}
		
		if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
		{
			fatal_error( 'Could not select database ' );
		}
		
		
		/*
		 * Paging
		*/
		$sLimit = "";
		if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
		{
			$sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
					intval( $_GET['iDisplayLength'] );
		}
		
		
		/*
		 * Ordering
		*/
		$sOrder = "";
		if ( isset( $_GET['iSortCol_0'] ) )
		{
			$sOrder = "ORDER BY  ";
			for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
			{
				if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
				{
					$sOrder .= "`".$aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."` ".
					 	($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
				}
			}
		
			$sOrder = substr_replace( $sOrder, "", -2 );
			if ( $sOrder == "ORDER BY" )
			{
				$sOrder = "";
			}
		}
		
		
		/*
		 * Filtering
		* NOTE this does not match the built-in DataTables filtering which does it
		* word by word on any field. It's possible to do here, but concerned about efficiency
		* on very large tables, and MySQL's regex functionality is very limited
		*/
		$sWhere = "";
		if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
		{
			$sWhere = "WHERE (";
			for ( $i=0 ; $i<count($aColumns) ; $i++ )
			{
				$sWhere .= "`".$aColumns[$i]."` LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
			}
			$sWhere = substr_replace( $sWhere, "", -3 );
			$sWhere .= ')';
		}
		
		/* Individual column filtering */
		for ( $i=0 ; $i<count($aColumns) ; $i++ )
		{
			if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
			{
				if ( $sWhere == "" )
				{
					$sWhere = "WHERE ";
				}
				else
				{
					$sWhere .= " AND ";
				}
				$sWhere .= "`".$aColumns[$i]."` LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
			}
		}
		
		
		/*
		 * SQL queries
		* Get data to display
		*/
		$sQuery = "
	SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $aColumns))."`
			FROM   $sTable
			$sWhere
			$sOrder
			$sLimit
			";
		$rResult = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
		
		/* Data set length after filtering */
		$sQuery = "
	SELECT FOUND_ROWS()
";
		$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
		$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
		$iFilteredTotal = $aResultFilterTotal[0];
		
		/* Total data set length */
		$sQuery = "
	SELECT COUNT(`".$sIndexColumn."`)
			FROM   $sTable
			";
		$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
		$aResultTotal = mysql_fetch_array($rResultTotal);
		$iTotal = $aResultTotal[0];
		
		
		/*
		 * Output
		*/
		$output = array(
				"sEcho" => intval($_GET['sEcho']),
				"iTotalRecords" => $iTotal,
				"iTotalDisplayRecords" => $iFilteredTotal,
				"aaData" => array()
		);
		
		while ( $aRow = mysql_fetch_array( $rResult ) )
		{
			$row = array();
			for ( $i=0 ; $i<count($aColumns) ; $i++ )
			{
				if ( $aColumns[$i] == "version" )
				{
					/* Special output formatting for 'version' column */
					$row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
				}
				else if ( $aColumns[$i] != ' ' )
				{
					/* General output */
					$row[] = $aRow[ $aColumns[$i] ];
				}
			}
			$output['aaData'][] = $row;
		}
		
		return $output;
	}
}
?>