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 with Informix ODBC driver

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 PHP script, which makes use of a database connection through the Informix ODBC driver for PHP is kindly provided by Luke Simmons. To use the code on your own server, check that thenthe INFORMIXDIR and ODBCINI paths are correct for your server-environment, set $sIndexColumn to a column which is indexed (for speed), $sTable to the table name, and fill in your database connection parameters to $gaSql. You may also wish to limit the query to only certain columns in the database by using the $aColumns array.

Feature set

Code

<?php
	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
	 * 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)
	 */
	putenv("INFORMIXDIR=/opt/informix");
	putenv("ODBCINI=/opt/informix/etc/odbc.ini");
	
	/* Indexed column (used for fast and accurate table cardinality) */
	$sIndexColumn = "";
	
	/* DB table to use */
	$sTable = "";

	/* Database connection information */
	$gaSql['user']       = "";
	$gaSql['password']   = "";
	$gaSql['dsn']        = "";
	$gaSql['database']   = "";

	/*
	* Columns
	* If you don't want all of the columns displayed you need to hardcode $aColumns array with your elements.
	* If not this will grab all the columns associated with $sTable
	*/
	$aColumns = array();

	
	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
	 * If you just want to use the basic configuration for DataTables with PHP server-side, there is
	 * no need to edit below this line
	 */
	
	/* 
	 * ODBC connection
	 */
	$gaSql['link'] =  odbc_connect( $gaSql['dsn'], $gaSql['user'], $gaSql['password']  ) or
		die( "Connection failed: " . odbc_error() );

	$cSql = "SELECT colname FROM systables JOIN syscolumns ON syscolumns.tabid = systables.tabid WHERE systables.tabname = '" . $sTable . "'";
	$cResult = odbc_exec( $gaSql['link'], $cSql );
	while ( $r = odbc_fetch_array( $cResult ) ) {
		array_push( $aColumns, $r['colname'] );
	};

		
	/* 
	 * Paging
	 * How rows are limited depends on which database you're using. This will need to be altered depending on your database.
	 */

	$sLimit = "";
	$sLimit = "SKIP " . intval( $_GET['iDisplayStart'] ) .  " FIRST " . 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
	 * Unchanged: This is standard SQL
	 * 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
	 */
	$sWhere = "";
	if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
	{
		$sWhere = "WHERE (";
		for ( $i=0 ; $i<count($aColumns) ; $i++ )
		{
			$sWhere .= $aColumns[$i]." LIKE '%".addslashes( $_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 '%".addslashes($_GET['sSearch_'.$i])."%' ";
		}
	}
	
	
	/*
	 * SQL queries
	 * Get data to display
	 * Different implementations of SQL use different ways to limit a data set. This would need to be altered depending on your database.
	 */
	$sQuery = "SELECT " . $sLimit . " " . implode(',',$aColumns) . " FROM $sTable $sWhere $sOrder";
	$rResult = odbc_exec($gaSql['link'],$sQuery) or die("$sQuery: " . odbc_error());
	
	/* Data set length after filtering */
	/* odbc_num_rows isn't supported by all ODBC drivers, so just run a count */
	/* This shouldn't need to be changed */
	$sQueryCnt = "SELECT count(*) as counter FROM $sTable $sWhere";
	
	$rResultCnt = odbc_exec( $gaSql['link'], $sQueryCnt ) or die (" $sQueryCnt: " . odbc_error());
	$aResultCnt = odbc_fetch_array( $rResultCnt, 0 );
	$iFilteredTotal = $aResultCnt['counter'];
	
	/* Total data set length */
	/* odbc_num_rows isn't supported by all ODBC drivers, so just run a count */
	/* This shouldn't need to be changed */
	$sQuery = "
		SELECT COUNT(".$sIndexColumn.") as counter
		FROM   $sTable
	";
	$rResultTotal = odbc_exec( $gaSql['link'], $sQuery ) or die(odbc_error());
	$aResultTotal = odbc_fetch_array($rResultTotal,0);
	$iTotal = $aResultTotal['counter'];
	
	
	/*
	 * Output
	 * Unchanged
	 */

	$output = array(
		"sEcho" => intval($_GET['sEcho']),
		"iTotalRecords" => $iTotal,
		"iTotalDisplayRecords" => $iFilteredTotal,
		"aaData" => array()
	);
	
	while ( $aRow = odbc_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;
	}
		
	echo json_encode( $output );
?>