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 PostgreSQL

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 is basically the same as the PHP with MySQL demo, but with a few small modifications for use with PostgreSQL.

To use the code on your own server, simply change the $aColumns array to list the columns you wish to include from your database, set $sIndexColumn to a column which is indexed (for speed), $sTable to the table name, and finally fill in your database connection parameters to $gaSql.

Feature set

Code

<?php
	/*
	 * Script:    DataTables server-side script for PHP and PostgreSQL
	 * Copyright: 2010 - Allan Jardine
	 * License:   GPL v2 or BSD (3-point)
	 */
	
	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
	 * 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 = "oid";
	
	/* DB table to use */
	$sTable = "ajax";
	
	/* Database connection information */
	$gaSql['user']       = "";
	$gaSql['password']   = "";
	$gaSql['db']         = "";
	$gaSql['server']     = "";
	
	
	
	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
	 * If you just want to use the basic configuration for DataTables with PHP server-side, there is
	 * no need to edit below this line
	 */
	
	/* 
	 * DB connection
	 */
	$gaSql['link'] = pg_connect(
		" host=".$gaSql['server'].
		" dbname=".$gaSql['db'].
		" user=".$gaSql['user'].
		" password=".$gaSql['password']
	) or die('Could not connect: ' . pg_last_error());
	
	
	/* 
	 * Paging
	 */
	$sLimit = "";
	if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
	{
		$sLimit = "LIMIT ".intval( $_GET['iDisplayLength'] )." OFFSET ".
			intval( $_GET['iDisplayStart'] );
	}
	
	
	/*
	 * Ordering
	 */
	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 assumes that the field that is being searched on is a string typed field (ie. one
	 * on which ILIKE can be used). Boolean fields etc will need a modification here.
	 */
	$sWhere = "";
	if ( $_GET['sSearch'] != "" )
	{
		$sWhere = "WHERE (";
		for ( $i=0 ; $i<count($aColumns) ; $i++ )
		{
			if ( $_GET['bSearchable_'.$i] == "true" )
			{
				$sWhere .= $aColumns[$i]." ILIKE '%".pg_escape_string( $_GET['sSearch'] )."%' OR ";
			}
		}
		$sWhere = substr_replace( $sWhere, "", -3 );
		$sWhere .= ")";
	}
	
	/* Individual column filtering */
	for ( $i=0 ; $i<count($aColumns) ; $i++ )
	{
		if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
		{
			if ( $sWhere == "" )
			{
				$sWhere = "WHERE ";
			}
			else
			{
				$sWhere .= " AND ";
			}
			$sWhere .= $aColumns[$i]." ILIKE '%".pg_escape_string($_GET['sSearch_'.$i])."%' ";
		}
	}
	
	
	$sQuery = "
		SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
		FROM   $sTable
		$sWhere
		$sOrder
		$sLimit
	";
	$rResult = pg_query( $gaSql['link'], $sQuery ) or die(pg_last_error());
	
	$sQuery = "
		SELECT $sIndexColumn
		FROM   $sTable
	";
	$rResultTotal = pg_query( $gaSql['link'], $sQuery ) or die(pg_last_error());
	$iTotal = pg_num_rows($rResultTotal);
	pg_free_result( $rResultTotal );
	
	if ( $sWhere != "" )
	{
		$sQuery = "
			SELECT $sIndexColumn
			FROM   $sTable
			$sWhere
		";
		$rResultFilterTotal = pg_query( $gaSql['link'], $sQuery ) or die(pg_last_error());
		$iFilteredTotal = pg_num_rows($rResultFilterTotal);
		pg_free_result( $rResultFilterTotal );
	}
	else
	{
		$iFilteredTotal = $iTotal;
	}
	
	
	
	/*
	 * Output
	 */
	$output = array(
		"sEcho" => intval($_GET['sEcho']),
		"iTotalRecords" => $iTotal,
		"iTotalDisplayRecords" => $iFilteredTotal,
		"aaData" => array()
	);
	
	while ( $aRow = pg_fetch_array($rResult, null, PGSQL_ASSOC) )
	{
		$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 );
	
	// Free resultset
	pg_free_result( $rResult );
	
	// Closing connection
	pg_close( $gaSql['link'] );
?>