An updated server-side processing script for DataTables

data.php

<?php
/*
 * Script:    DataTables server-side script for PHP and MySQL
 * Copyright: 2016 - Steven Lavoie
 * Copyright: 2012 - John Becker, Beckersoft, Inc.
 * Copyright: 2010 - Allan Jardine
 * License:   GPL v2 or BSD (3-point)
 */

namespace Common\Utilities;

require_once __DIR__.'/../../../generated-conf/db.properties.php';
 	
class TableData {
 	private $_db;

	public function __construct() {
			$host		  = ''.DATABASE_HOST;
			$database	  = ''.DATABASE_NAME;
			$user		  = ''.DB_USERNAME;
			$passwd		  = ''.DB_PASSWORD;

		  $this->_db = new \PDO('mysql:host='.$host.';dbname='.$database, $user, $passwd, array(\PDO::ATTR_PERSISTENT => true));
	}

	public function get($table, $index_column, $columns) {
		// Paging
		$sLimit = "";
		if ( isset( $_GET['start'] ) && $_GET['length'] != '-1' ) {
			$sLimit = "LIMIT ".intval( $_GET['start'] ).", ".intval( $_GET['length'] );
		}
		
		// Ordering
		$sOrder = "";
		if ( isset( $_GET['order'][0]['column'] ) && $_GET['columns'][$_GET['order'][0]['column']]['orderable'] == "true" ) {
			$sOrder = "ORDER BY `".$columns[$_GET['order'][0]['column']]."` ".$_GET['order'][0]['dir'];
		}
		
		/* 
		 * 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['search']['value']) && $_GET['search']['value'] != "" ) {
			$sWhere = "WHERE (";
			for ( $i=0 ; $i<count($columns) ; $i++ ) {
				if ( isset($_GET['columns'][$i]['searchable']) && $_GET['columns'][$i]['searchable'] == "true" ) {
					$sWhere .= "`".$columns[$i]."` LIKE :search OR ";
				}
			}
			$sWhere = substr_replace( $sWhere, "", -3 ); //deletes the last OR
			$sWhere .= ')';
		}
		
		// Individual column filtering
		for ( $i=0 ; $i<count($columns) ; $i++ ) {
			if ( isset($_GET['columns'][$i]['searchable']) && $_GET['columns'][$i]['searchable'] == "true" && $_GET['columns'][$i]['search']['value'] != '' ) {
				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['search']['value']) && $_GET['search']['value'] != "" ) {
			$statement->bindValue(':search', '%'.$_GET['search']['value'].'%', \PDO::PARAM_STR);
		}
		for ( $i=0 ; $i<count($columns) ; $i++ ) {
			if ( isset($_GET['columns'][$i]['searchable']) && $_GET['columns'][$i]['searchable'] == "true" && $_GET['columns'][$i]['search']['value']['value'] != '' ) {
				$statement->bindValue(':search'.$i, '%'.$_GET['columns'][$i]['search']['value'].'%', \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(
			"draw" => intval($_GET['draw']),
			"recordsTotal" => $iTotal,
			"recordsFiltered" => $iFilteredTotal,
			//"query" => $statement,		//add for debugging
			"data" => 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['data'][] = $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', "index_column", array( "col1", "col2", "col3", "col4", "col5", "col6" ));

/*
 * 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');
}
*/
?>