An updated server-side processing script for DataTables

data.php

<?php

/*
 * Script:    DataTables server-side script for PHP and MySQL
 * Copyright: 2012 - John Becker, Beckersoft, Inc.
 * Copyright: 2010 - Allan Jardine
 * License:   GPL v2 or BSD (3-point)
 */
  
class TableData {
 
 	private $_db;

	public function __construct() {

		try {
			$host		= 'localhost';
			$database	= 'my_database';
			$user		= 'myusername';
			$passwd		= 'mypassword';
		
		    $this->_db = new PDO('mysql:host='.$host.';dbname='.$database, $user, $passwd, array(PDO::ATTR_PERSISTENT => true));
		} catch (PDOException $e) {
		    error_log("Failed to connect to database: ".$e->getMessage());
		}		
		
	}

	public function get($table, $index_column, $columns) {

		// Paging
		$sLimit = "";
		if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' ) {
			$sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".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" ) {
					$sortDir = (strcasecmp($_GET['sSortDir_'.$i], 'ASC') == 0) ? 'ASC' : 'DESC';
					$sOrder .= "`".$columns[ intval( $_GET['iSortCol_'.$i] ) ]."` ". $sortDir .", ";
				}
			}
			
			$sOrder = substr_replace( $sOrder, "", -2 );
			if ( $sOrder == "ORDER BY" ) {
				$sOrder = "";
			}
		}
		
		/* 
		 * 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 ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
			$sWhere = "WHERE (";
			for ( $i=0 ; $i<count($columns) ; $i++ ) {
				if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" ) {
					$sWhere .= "`".$columns[$i]."` LIKE :search OR ";
				}
			}
			$sWhere = substr_replace( $sWhere, "", -3 );
			$sWhere .= ')';
		}
		
		// Individual column filtering
		for ( $i=0 ; $i<count($columns) ; $i++ ) {
			if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' ) {
				if ( $sWhere == "" ) {
					$sWhere = "WHERE ";
				}
				else {
					$sWhere .= " AND ";
				}
				$sWhere .= "`".$columns[$i]."` LIKE :search".$i." ";
			}
		}
		
		// SQL queries get data to display
		$sQuery = "SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $columns))."` FROM `".$table."` ".$sWhere." ".$sOrder." ".$sLimit;
		$statement = $this->_db->prepare($sQuery);
		
		// Bind parameters
		if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
			$statement->bindValue(':search', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
		}
		for ( $i=0 ; $i<count($columns) ; $i++ ) {
			if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' ) {
				$statement->bindValue(':search'.$i, '%'.$_GET['sSearch_'.$i].'%', PDO::PARAM_STR);
			}
		}

		$statement->execute();
		$rResult = $statement->fetchAll();
		
		$iFilteredTotal = current($this->_db->query('SELECT FOUND_ROWS()')->fetch());
		
		// Get total number of rows in table
		$sQuery = "SELECT COUNT(`".$index_column."`) FROM `".$table."`";
		$iTotal = current($this->_db->query($sQuery)->fetch());
		
		// Output
		$output = array(
			"sEcho" => intval($_GET['sEcho']),
			"iTotalRecords" => $iTotal,
			"iTotalDisplayRecords" => $iFilteredTotal,
			"aaData" => array()
		);
		
		// Return array of values
		foreach($rResult as $aRow) {
			$row = array();			
			for ( $i = 0; $i < count($columns); $i++ ) {
				if ( $columns[$i] == "version" ) {
					// Special output formatting for 'version' column
					$row[] = ($aRow[ $columns[$i] ]=="0") ? '-' : $aRow[ $columns[$i] ];
				}
				else if ( $columns[$i] != ' ' ) {
					$row[] = $aRow[ $columns[$i] ];
				}
			}
			$output['aaData'][] = $row;
		}
		
		echo json_encode( $output );
	}

}

header('Pragma: no-cache');
header('Cache-Control: no-store, no-cache, must-revalidate');

// Create instance of TableData class
$table_data = new TableData();

// Get the data
$table_data->get('table_name', 'index_column', array('column1', 'column2', 'columnN'));


/*
 * Alternatively, you may want to use the same class for several differnt tables for different pages.
 * By adding something similar to the following to your .htaccess file you can control this a little more...
 *
 * RewriteRule ^pagename/data/?$ data.php?_page=PAGENAME [L,NC,QSA]
 *
 
switch ($_SERVER['REQUEST_METHOD']) {
    case 'GET':
        if (isset($_REQUEST['_page'])) {
        	if($_REQUEST['_page'] === 'PAGENAME') {
	            $table_data->get('table_name', 'index_column', array('column1', 'column2', 'columnN'));
	        }
        }
        break;
    default:
        header('HTTP/1.1 400 Bad Request');
}
*/
?>

This works great, thank you :) how would you use the function multiple times to UNION the tables?

Searching is not working for me and not sure why... everything else is working fine. Any ideas?

DataTables warning (table id = 'sample_1'): DataTables warning: JSON data from server could not be parsed. This is caused by a JSON formatting error.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number' in /home/test/public_html/assets/data-tables/test-pdo.php:99 Stack trace: #0 /home/test/public_html/assets/data-tables/test-pdo.php(99): PDOStatement->execute() #1 /home/test/public_html/assets/data-tables/test-pdo.php(143): TableData->get('accounts', 'account_id', Array) #2 {main}

thrown in /home/test/public_html/assets/data-tables/test-pdo.php on line 99

line 99 is - $statement->execute(); from Bind Parameters

I am setting emulate prepares to false which is causing the problem above... any ideas?

Does anyone monitor this page? Still would like to know about the error above when settings emulate prepares to false...

I don't really monitor the page. You may have better luck in a PDO forum on that issue. If you delve into what the code is doing it's bit limited in what it does. In other projects I've found it easier to implement the querying code and populating of DataTables manually if it requires more fine-grained control.

my db's are getting too big and I now have to start to use server-side, not going well.
how would i pass in extra variables in for the sql like "where acctID = 100"?
sorry for my rookie question. cheers.

Hello,

Thanks for the PDO class. You made factoring it into my API beautiful. I did some changes to meet the specification of the current api. https://gist.github.com/snaquaye/4d68079ef98cdc2e7937

Thanks

@jjb3rd @snaquaye, a few more updates were required for me on DT 1.10.12 w/ JQ 3.1.0. Thanks for the head start! Here are my changes: https://gist.github.com/sraboy/3102387f44248a80cb9e9e1fe851307d. The documentation was pretty hard to follow (at least for someone like me who is brand-new to PHP, AJAX, JS and all this web stuff) so I'm not sure if this is really the right way but it's the way that works. I'm still a bit surprised that a working set of example scripts isn't included in the regular releases; there appear to have been a lot of breaking changes.

gr8 m8

How to add where conditions in request. Means i want my custom where to be added along with the datatables request.??

# thanks john that's really usefull, keep on update ..

This is really usefull thank you, but how can i call it. i tried this

<script type="text/javascript"> $(document).ready(function() { $('#table').dataTable({ "bProcessing": true, "bServerSide": true, "sAjaxSource": "data.php?op=table" }); }); </script>