DataTables logo DataTables

This is a legacy documentation page and the methods described may not work with DataTables 1.10+.
Please see the upgrade notes for 1.10.

Server-side processing | PHP with Informix ODBC driver

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 );
?>