DataTables logo DataTables

via Ad Packs

Server-side processing | PHP with MSSQL ODBC driver

This PHP script makes use of a database connection through the MSSQL ODBC driver for PHP. To use the code on your own server, check that thenthe MSSQLDIR 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("MSSQLDIR=/opt/mssql");
	putenv("ODBCINI=/opt/mssql/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 = "TOP " . addslashes( $_GET['iDisplayLength'] ) .  " ";
  $sLimit2 = "";
  $sLimit2 = "TOP " . addslashes( ((int)$_GET['iDisplayStart'] + (int)$_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] ) ]."
				 	".addslashes( $_GET['sSortDir_'.$i] ) .", ";
			}
		}
		
		$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 * FROM ( SELECT " . $sLimit . " * FROM ( SELECT " . $sLimit2 . " " . implode(',',$aColumns) . " FROM $sTable $sWhere $sOrder )as x)as y";
	$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 );
?>