Preamble

There are many ways to get your data into DataTables, and if you are working with seriously large databases, you might want to consider using the server-side options that DataTables provides. Basically all of the paging, filtering, sorting etc that DataTables does can be handed off to a server (or any other data source - Google Gears or Adobe Air for example!) and DataTables is just an events and display module.

The example here shows a very simple display of the CSS data (used in all my other examples), but in this instance coming from the server on each draw. Filtering, multi-column sorting etc all work as you would expect.

Live example

Initialisation code

$(document).ready(function() {
	$('#example').dataTable( {
		"bProcessing": true,
		"bServerSide": true,
		"sAjaxSource": "../examples_support/server_processing.php"
	} );
} );

Server side (PHP) code

<?php
  /* MySQL connection */
	$gaSql['user']       = "*********";
	$gaSql['password']   = "*********";
	$gaSql['db']         = "*********";
	$gaSql['server']     = "*********";
	$gaSql['type']       = "mysql";
	
	$gaSql['link'] =  mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  ) or
		die( 'Could not open connection to server' );
	
	mysql_select_db( $gaSql['db'], $gaSql['link'] ) or 
		die( 'Could not select database '. $gaSql['db'] );
	
	/* Paging */
	$sLimit = "";
	if ( isset( $_GET['iDisplayStart'] ) )
	{
		$sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
			mysql_real_escape_string( $_GET['iDisplayLength'] );
	}
	
	/* Ordering */
	if ( isset( $_GET['iSortCol_0'] ) )
	{
		$sOrder = "ORDER BY  ";
		for ( $i=0 ; $i<mysql_real_escape_string( $_GET['iSortingCols'] ) ; $i++ )
		{
			$sOrder .= fnColumnToField(mysql_real_escape_string( $_GET['iSortCol_'.$i] ))."
			 	".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
		}
		$sOrder = substr_replace( $sOrder, "", -2 );
	}
	
	/* 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 ( $_GET['sSearch'] != "" )
	{
		$sWhere = "WHERE engine LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
		                "browser LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
		                "platform LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
		                "version LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
		                "grade LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%'";
	}
	
	$sQuery = "
		SELECT SQL_CALC_FOUND_ROWS id, engine, browser, platform, version, grade
		FROM   ajax
		$sWhere
		$sOrder
		$sLimit
	";
	$rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
	
	$sQuery = "
		SELECT FOUND_ROWS()
	";
	$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
	$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
	$iFilteredTotal = $aResultFilterTotal[0];
	
	$sQuery = "
		SELECT COUNT(id)
		FROM   ajax
	";
	$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
	$aResultTotal = mysql_fetch_array($rResultTotal);
	$iTotal = $aResultTotal[0];
	
	$sOutput = '{';
	$sOutput .= '"sEcho": '.intval($_GET['sEcho']).', ';
	$sOutput .= '"iTotalRecords": '.$iTotal.', ';
	$sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
	$sOutput .= '"aaData": [ ';
	while ( $aRow = mysql_fetch_array( $rResult ) )
	{
		$sOutput .= "[";
		$sOutput .= '"'.addslashes($aRow['engine']).'",';
		$sOutput .= '"'.addslashes($aRow['browser']).'",';
		$sOutput .= '"'.addslashes($aRow['platform']).'",';
		if ( $aRow['version'] == "0" )
			$sOutput .= '"-",';
		else
			$sOutput .= '"'.addslashes($aRow['version']).'",';
		$sOutput .= '"'.addslashes($aRow['grade']).'"';
		$sOutput .= "],";
	}
	$sOutput = substr_replace( $sOutput, "", -1 );
	$sOutput .= '] }';
	
	echo $sOutput;
	
	
	function fnColumnToField( $i )
	{
		if ( $i == 0 )
			return "engine";
		else if ( $i == 1 )
			return "browser";
		else if ( $i == 2 )
			return "platform";
		else if ( $i == 3 )
			return "version";
		else if ( $i == 4 )
			return "grade";
	}
?>

Other examples

Basic initialisation

• Zero configuration
• Feature enablement
• Sorting data
• Multi-column sorting
• Multiple tables
• Hidden columns
• DOM positioning
• State saving
• Alternative pagination styles
• Change language information (internationalisation)
• ThemeRoller themes (Smoothness)

Advanced initialisation

• Events (pre initialisation)
• Events (post initialisation)
• Column rendering
• Sorting without HTML tags
• Multiple table controls (sDom)
• Custom toolbar (element) around table
• Set sorting controls
• Column grouping through col/row spans
• Row grouping
• Row callback
• Footer callback
• Change language information from a file (internationalisation)

Data sources

• DOM
• Javascript array
• Ajax source
• Server side processing

Server-side processing

• Obtain server-side data
• Add extra HTTP variables
• Use HTTP POST
• Custom column ordering (in callback data)
• Pipelining data (reduce Ajax calls for paging)
• Show and hide details about a particular record
• User selectable rows (multiple rows)

API

• Dynamically add a new row
• Individual column filtering
• Highlight rows and columns
• Show and hide details about a particular record
• User selectable rows (multiple rows)
• User selectable rows (single row) and delete rows
• Editable rows (with jEditable)
• Submit form with elements in table
• Index column (static number column)
• Show and hide columns dynamically
• Regular expression filtering

Plug-ins

• Add custom API functions
• Sorting and type detection
• Custom pagination controls
• Range filtering / custom filtering
• Live DOM sorting

Please refer to the DataTables documentation for full information about its API properties and methods.