SSP Class original

https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/ssp.class.php

Mejoras SSP Class con respecto al original

HTML Ejemplo

<table id="consentimiento-datatable" class="display">
	<thead>
		<tr role="row">
			<th>Código</th>
			<th>Tabla</th>
			<th>Nombre</th>
			<th>Email</th>
			<th>Fecha Envío</th>
			<th>Fecha Aceptación</th>
			<th>Fecha Alta</th>
			<th>Fecha Baja</th>
			<th>DNI</th>
		</tr>
	</thead>
	<tfoot>
		<tr>
			<td>Código</td>
			<td>Tabla</td>
			<td>Nombre</td>
			<td>Email</td>
			<td>Fecha Envío</td>
			<td>Fecha Aceptación</td>
			<td>Fecha Alta</td>
			<td>Fecha Baja</td>
			<td>DNI</td>
		</tr>
	</tfoot>
</table>

Configuración JAVASCRIPT

<script type="text/javascript" charset="utf-8">
var table;
$(document).ready(function() {
	/* CREACIÓN TABLA DINÁMICA - DATATABLE */
	table = $('#consentimiento-datatable').DataTable({
		"lengthMenu": [
			[20, 25, 50, 100, 250, -1],
			[20, 25, 50, 100, 250, "Todas las entradas"]
		],
		select: true,
		dom: 'lBfrtip',
		"processing": true,
		"serverSide": true,
		"ajax": "./AJAX/acceso.php",
		"columns": [
			{ "data": "Codigo" },
			{ "data": "Tabla" },
			{ "data": "Nombre" },
			{ "data": "Email" },
			{ 
				"class": "fechaEnvio", 
				"data": "FechaEnvio" 
			},
			{ 
				"class": "fechaAceptacion", 
				"data": "FechaAceptacion" 
			},
			{ 
				"class": "fechaAlta", 
				"data": "FechaAlta" 
			},
			{ 
				"class": "fechaBaja", 
				"data": "FechaBaja" 
			},
			{ "data": "DNI" }
		]
	});
</script>

Configuración inicial de DataTables (./AJAX/acceso.php)

//Parámetros de conexión al SQL Server
$serverName = "PC\SQLEXPRESS";
$database = "DATABASE";
$connectionOptions = array(
    "Database" => $database,
    "UID" => "",
    "PWD" => ""
);

/*
    * DataTables server-side processing script.
    *
    * See http://datatables.net/usage/server-side for full details on the server-
    * side processing requirements of DataTables.
    *
    * @license MIT - http://datatables.net/license_mit
    */

    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    * Easy set variables
    */

    // DB table to use
    $table = '[DATABASE].[dbo].[Table]';

    // Table's primary key
    $primaryKey = 'Codigo';

    // Array of database columns which should be read and sent back to DataTables.
    // The `db` parameter represents the column name in the database, while the `dt`
    // parameter represents the DataTables column identifier. In this case object
    // parameter names
    $columns = array(
        array(
            'db' => 'Codigo AS DT_RowId',
            'dt' => 'DT_RowId',
            'formatter' => function( $d, $row ) {
                // Technically a DOM id cannot start with an integer, so we prefix
                // a string. This can also be useful if you have multiple tables
                // to ensure that the id is unique with a different prefix
                return 'row_'.$d;
            }
        ),
        array( 'db' => 'Codigo',  'dt' => 'Codigo' ),
        array( 'db' => 'Tabla',   'dt' => 'Tabla' ),
        array( 'db' => 'Nombre',   'dt' => 'Nombre' ),
        array( 'db' => 'Email',     'dt' => 'Email' ),
        array( 'db' => '[Fecha Envio]', 'dt' => 'FechaEnvio' ),
        array( 'db' => '[Fecha Aceptacion]', 'dt' => 'FechaAceptacion' ),
        array( 'db' => '[Fecha Alta]', 'dt' => 'FechaAlta' ),
        array( 'db' => '[Fecha Baja]', 'dt' => 'FechaBaja' ),
        array( 'db' => 'DNI',     'dt' => 'DNI' )
    );

    // Parámetros de conexión a BASE DE DATOS
    // SqlserverVer: MySQL, Sqlserver
    // SqlserverVer: 2008, 2009... NULL (POSTERIOR 2012)
    $sql_details = array(
        "type" => "Sqlserver",
        "server_version" => 2008,
        'user' => $connectionOptions['UID'],
        'pass' => $connectionOptions['PWD'],
        'db'   => $database,
        'host' => $serverName
    );

    /**
     * Clase SSP
     * Controla el acceso a la base de datos y las consultas para DataTables
     */
    require( 'ssp.class.php' );

    echo json_encode(
        SSP::complex($_GET, $sql_details, $table, $primaryKey, $columns, null, "(Tabla IN ('ALUMNO','CONTACTO','PRUEBA'))")
    );

SSP Class de DataTables (./AJAX/ssp.class.php)

<?php
/*
 * Helper functions for building a DataTables server-side processing SQL query
 *
 * The static functions in this class are just helper functions to help build
 * the SQL used in the DataTables demo server-side processing scripts. These
 * functions obviously do not represent all that can be done with server-side
 * processing, they are intentionally simple to show how it works. More complex
 * server-side processing operations will likely require a custom script.
 *
 * See http://datatables.net/usage/server-side for full details on the server-
 * side processing requirements of DataTables.
 *
 * @license MIT - http://datatables.net/license_mit
 */
// REMOVE THIS BLOCK - used for DataTables test environment only!
/* $file = $_SERVER['DOCUMENT_ROOT'].'/datatables/pdo.php';
if ( is_file( $file ) ) {
	include( $file );
}*/

class SSP
{

	private static $serverType;
	private static $serverTypeVer  = NULL;

	/**
	 * Create the data output array for the DataTables rows
	 *
	 *  @param  array $columns Column information array
	 *  @param  array $data    Data from the SQL get
	 *  @return array          Formatted data in a row based format
	 */
	static function data_output($columns, $data)
	{
		$out = array();
		for ($i = 0, $ien = count($data); $i < $ien; $i++) {
			$row = array();

			for ($j = 0, $jen = count($columns); $j < $jen; $j++) {
				$column = $columns[$j];
				// Is there a formatter?
				if (isset($column['formatter'])) {

					// 	Formatter para añadir el ID único al tr de la tabla
					// 	Si el identificador de la columna de DataTable 'dt' es DT_RowId
					// 	añadirá la columna aunque esté duplicada en la sentencia SQL ya que en vez
					// 	de utilizar 'db' como columna utilizará el 'dt' que hacer referencia a: 
					// 	column AS DT_RowId
					/* 	$columns = array(
							array(
								'db' => 'Codigo AS DT_RowId',
								'dt' => 'DT_RowId',
								'formatter' => function( $d, $row ) {
									// Technically a DOM id cannot start with an integer, so we prefix
									// a string. This can also be useful if you have multiple tables
									// to ensure that the id is unique with a different prefix
									return 'row_'.$d;
								}
							)
						);
					*/

					if ($column['dt'] == 'DT_RowId') {
						$row[$column['dt']] = $column['formatter']($data[$i][$column['dt']], $data[$i]);
					} else {
						$row[$column['dt']] = $column['formatter']($data[$i][$column['db']], $data[$i]);
					}
				} else {

					// Eliminamos corchetes SQL SERVER
					$dbColumn = str_ireplace(array('[', ']'), array('', ''), $columns[$j]['db']);
					$row[$column['dt']] = $data[$i][$dbColumn];
				}
			}
			$out[] = $row;
		}
		return $out;
	}
	/**
	 * Database connection
	 *
	 * Obtain an PHP PDO connection from a connection details array
	 *
	 *  @param  array $conn SQL connection details. The array should have
	 *    the following properties
	 *     * host - host name
	 *     * db   - database name
	 *     * user - user name
	 *     * pass - user password
	 *  @return resource PDO connection
	 */
	static function db($conn)
	{
		if (is_array($conn)) {
			return self::sql_connect($conn);
		}
		return $conn;
	}
	/**
	 * Paging
	 *
	 * Construct the LIMIT clause for server-side processing SQL query
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  array $columns Column information array
	 *  @return string SQL limit clause
	 */
	static function limit($request, $columns)
	{
		$limit = '';
		if (isset($request['start']) && $request['length'] != -1) {

			// "LIMIT" PARA SQL SERVER
			if (self::$serverType == 'Sqlserver') {
				// SQL SERVER >= 2012
				if (empty(self::$serverTypeVer)) {
					$limit = " OFFSET " . intval($request['start']) . " ROWS FETCH NEXT " . intval($request['length']) . " ROWS ONLY";

					// SQL SERVER < 2012
				} elseif (self::$serverTypeVer < 2012) {
					$limit = " row > " . intval($request['start']) . " and row <= " . (intval($request['start']) + intval($request['length'])) . " ";
				}
			} else {
				$limit = " LIMIT " . intval($request['start']) . ", " . intval($request['length']);
			}
		}
		return $limit;
	}
	
	/**
	 * Ordering
	 *
	 * Construct the ORDER BY clause for server-side processing SQL query
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  array $columns Column information array
	 *  @return string SQL order by clause
	 */
	static function order($request, $columns)
	{
		$order = '';
		if (isset($request['order']) && count($request['order'])) {
			$orderBy = array();
			$dtColumns = self::pluck($columns, 'dt');
			for ($i = 0, $ien = count($request['order']); $i < $ien; $i++) {
				// Convert the column index into the column data property
				$columnIdx = intval($request['order'][$i]['column']);
				$requestColumn = $request['columns'][$columnIdx];
				$columnIdx = array_search($requestColumn['data'], $dtColumns);
				$column = $columns[$columnIdx];
				if ($requestColumn['orderable'] == 'true') {
					$dir = $request['order'][$i]['dir'] === 'asc' ?
						'ASC' : 'DESC';
					$orderBy[] = $column['db'] . ' ' . $dir;
				}
			}
			if (count($orderBy)) {
				$order = 'ORDER BY ' . implode(', ', $orderBy);
			}
		}
		return $order;
	}

	/**
	 * Searching / Filtering
	 *
	 * Construct the WHERE clause for server-side processing SQL query.
	 *
	 * 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 performance on large
	 * databases would be very poor
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  array $columns Column information array
	 *  @param  array $bindings Array of values for PDO bindings, used in the
	 *    sql_exec() function
	 *  @return string SQL where clause
	 */
	static function filter($request, $columns, &$bindings)
	{
		$globalSearch = array();
		$columnSearch = array();
		$dtColumns = self::pluck($columns, 'dt');
		if (isset($request['search']) && $request['search']['value'] != '') {
			$str = $request['search']['value'];
			for ($i = 0, $ien = count($request['columns']); $i < $ien; $i++) {
				$requestColumn = $request['columns'][$i];
				$columnIdx = array_search($requestColumn['data'], $dtColumns);
				$column = $columns[$columnIdx];
				if ($requestColumn['searchable'] == 'true') {

					// MAYÚSCULAS para mejorar la búsqueda de strings
					$binding = self::bind($bindings, '%' . mb_strtoupper($str) . '%', PDO::PARAM_STR);
					$globalSearch[] = " UPPER(" . $column['db'] . ") LIKE " . $binding;
				}
			}
		}
		// Individual column filtering
		if (isset($request['columns'])) {
			for ($i = 0, $ien = count($request['columns']); $i < $ien; $i++) {
				$requestColumn = $request['columns'][$i];
				$columnIdx = array_search($requestColumn['data'], $dtColumns);
				$column = $columns[$columnIdx];
				$str = $requestColumn['search']['value'];
				if (
					$requestColumn['searchable'] == 'true' &&
					$str != ''
				) {

					// MAYÚSCULAS para mejorar la búsqueda de strings
					$binding = self::bind($bindings, '%' . mb_strtoupper($str) . '%', PDO::PARAM_STR);
					$columnSearch[] = " UPPER(" . $column['db'] . ") LIKE " . $binding;
				}
			}
		}
		// Combine the filters into a single string
		$where = '';
		if (count($globalSearch)) {
			$where = '(' . implode(' OR ', $globalSearch) . ')';
		}
		if (count($columnSearch)) {
			$where = $where === '' ?
				implode(' AND ', $columnSearch) : $where . ' AND ' . implode(' AND ', $columnSearch);
		}
		if ($where !== '') {
			$where = 'WHERE ' . $where;
		}
		return $where;
	}
	/**
	 * Perform the SQL queries needed for an server-side processing requested,
	 * utilising the helper functions of this class, limit(), order() and
	 * filter() among others. The returned array is ready to be encoded as JSON
	 * in response to an SSP request, or can be modified if needed before
	 * sending back to the client.
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  array|PDO $conn PDO connection resource or connection parameters array
	 *  @param  string $table SQL table to query
	 *  @param  string $primaryKey Primary key of the table
	 *  @param  array $columns Column information array
	 *  @return array          Server-side processing response array
	 */
	static function simple($request, $conn, $table, $primaryKey, $columns)
	{
		$bindings = array();
		$db = self::db($conn);
		// Build the SQL query string from the request
		$limit = self::limit($request, $columns);
		$order = self::order($request, $columns);
		$where = self::filter($request, $columns, $bindings);
		// Main query to actually get the data
		$data = self::sql_exec(
			$db,
			$bindings,
			"SELECT " . implode(", ", self::pluck($columns, 'db')) . "
			 FROM $table
			 $where
			 $order
			 $limit"
		);
		// Data set length after filtering
		$resFilterLength = self::sql_exec(
			$db,
			$bindings,
			"SELECT COUNT({$primaryKey})
			 FROM   $table
			 $where"
		);
		$recordsFiltered = $resFilterLength[0][0];
		// Total data set length
		$resTotalLength = self::sql_exec(
			$db,
			"SELECT COUNT({$primaryKey})
			 FROM   $table"
		);
		$recordsTotal = $resTotalLength[0][0];
		/*
		 * Output
		 */
		return array(
			"draw"            => isset($request['draw']) ?
				intval($request['draw']) : 0,
			"recordsTotal"    => intval($recordsTotal),
			"recordsFiltered" => intval($recordsFiltered),
			"data"            => self::data_output($columns, $data)
		);
	}
	/**
	 * The difference between this method and the simple one, is that you can
	 * apply additional where conditions to the SQL queries. These can be in
	 * one of two forms:
	 *
	 * * 'Result condition' - This is applied to the result set, but not the
	 *   overall paging information query - i.e. it will not effect the number
	 *   of records that a user sees they can have access to. This should be
	 *   used when you want apply a filtering condition that the user has sent.
	 * * 'All condition' - This is applied to all queries that are made and
	 *   reduces the number of records that the user can access. This should be
	 *   used in conditions where you don't want the user to ever have access to
	 *   particular records (for example, restricting by a login id).
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  array|PDO $conn PDO connection resource or connection parameters array
	 *  @param  string $table SQL table to query
	 *  @param  string $primaryKey Primary key of the table
	 *  @param  array $columns Column information array
	 *  @param  string $whereResult WHERE condition to apply to the result set
	 *  @param  string $whereAll WHERE condition to apply to all queries
	 *  @return array          Server-side processing response array
	 */
	static function complex($request, $conn, $table, $primaryKey, $columns, $whereResult = null, $whereAll = null)
	{
		$bindings = array();
		$db = self::db($conn);

		$whereAllSql = '';
		// Build the SQL query string from the request
		$limit = self::limit($request, $columns);
		$order = self::order($request, $columns);
		$where = self::filter($request, $columns, $bindings);
		$whereResult = self::_flatten($whereResult);
		$whereAll = self::_flatten($whereAll);

		if ($whereResult) {
			$where = $where ?
				$where . ' AND ' . $whereResult : 'WHERE ' . $whereResult;
		}

		if ($whereAll) {
			$where = $where ?
				$where . ' AND ' . $whereAll : 'WHERE ' . $whereAll;
			$whereAllSql = 'WHERE ' . $whereAll;
		}

		if (self::$serverType == 'Sqlserver' &&  !empty(self::$serverTypeVer) && self::$serverTypeVer < 2012) {
			// SQL SERVER ES MUY RARO
			// Main query to actually get the data
			// Modificado para SQL SERVER anteriores al 2012 ya que NO existe OFFSET.
			$imploded = implode(", ", self::pluck($columns, 'db'));
			$data = self::sql_exec(
				$db,
				$bindings,
				"SELECT $imploded
				FROM (SELECT $imploded, row_number() over ($order) as row from $table $where) A
				WHERE
				$limit
				$order"
			);
		} else {

			// Main query to actually get the data
			$data = self::sql_exec(
				$db,
				$bindings,
				"SELECT " . implode(", ", self::pluck($columns, 'db')) . "
				FROM $table
				$where
				$order
				$limit"
			);
		}

		// Data set length after filtering
		$resFilterLength = self::sql_exec(
			$db,
			$bindings,
			"SELECT COUNT({$primaryKey})
			FROM $table
			$where"
		);

		$recordsFiltered = $resFilterLength[0][0];

		// Total data set length
		$resTotalLength = self::sql_exec(
			$db,
			"",
			"SELECT COUNT({$primaryKey})
			FROM $table 
			$whereAllSql"
		);

		$recordsTotal = $resTotalLength[0][0];

		/*
		 * Output
		 */
		return array(
			"draw"            => isset($request['draw']) ? intval($request['draw']) : 0,
			"recordsTotal"    => intval($recordsTotal),
			"recordsFiltered" => intval($recordsFiltered),
			"data"            => self::data_output($columns, $data)
		);
	}
	/**
	 * Connect to the database
	 *
	 * @param  array $sql_details SQL server connection details array, with the
	 *   properties:
	 *     * host - host name
	 *     * db   - database name
	 *     * user - user name
	 *     * pass - user password
	 * @return resource Database connection handle
	 */
	static function sql_connect($sql_details)
	{
		try {

			if ($sql_details['type'] == 'Sqlserver') {
				$db = @new PDO(
					"sqlsrv:Server=" . $sql_details['host'] . ";Database=" . $sql_details['db'],
					$sql_details['user'],
					$sql_details['pass'],
					array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
				);

				self::$serverType = $sql_details['type'];
				self::$serverTypeVer = $sql_details['server_version'];
			} else {
				$db = @new PDO(
					"mysql:host={$sql_details['host']};dbname={$sql_details['db']}",
					$sql_details['user'],
					$sql_details['pass'],
					array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
				);
			}
		} catch (PDOException $e) {
			self::fatal(
				"An error occurred while connecting to the database. " .
					"The error reported by the server was: " . $e->getMessage()
			);
		}
		return $db;
	}
	/**
	 * Execute an SQL query on the database
	 *
	 * @param  resource $db  Database handler
	 * @param  array    $bindings Array of PDO binding values from bind() to be
	 *   used for safely escaping strings. Note that this can be given as the
	 *   SQL query string if no bindings are required.
	 * @param  string   $sql SQL query to execute.
	 * @return array         Result from the query (all rows)
	 */
	static function sql_exec($db, $bindings, $sql = null)
	{
		// Argument shifting
		if ($sql === null) {
			$sql = $bindings;
		}

		/* var_dump($sql);
		die(); */

		$stmt = $db->prepare($sql);

		// var_dump($bindings);

		// Bind parameters
		if (is_array($bindings)) {
			for ($i = 0, $ien = count($bindings); $i < $ien; $i++) {
				$binding = $bindings[$i];
				$stmt->bindValue($binding['key'], $binding['val'], $binding['type']);
			}
		}

		/* echo var_dump($stmt);
		die(); */

		// Execute
		try {
			$stmt->execute();
		} catch (PDOException $e) {
			self::fatal("An SQL error occurred: " . $e->getMessage());
		}

		// Return all
		return $stmt->fetchAll(PDO::FETCH_BOTH);
	}

	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
	 * Internal methods
	 */
	/**
	 * Throw a fatal error.
	 *
	 * This writes out an error message in a JSON string which DataTables will
	 * see and show to the user in the browser.
	 *
	 * @param  string $msg Message to send to the client
	 */
	static function fatal($msg)
	{
		echo json_encode(array(
			"error" => $msg
		));
		exit(0);
	}

	/**
	 * Create a PDO binding key which can be used for escaping variables safely
	 * when executing a query with sql_exec()
	 *
	 * @param  array &$a    Array of bindings
	 * @param  *      $val  Value to bind
	 * @param  int    $type PDO field type
	 * @return string       Bound key to be used in the SQL where this parameter
	 *   would be used.
	 */
	static function bind(&$a, $val, $type)
	{
		$key = ':binding_' . count($a);
		$a[] = array(
			'key' => $key,
			'val' => $val,
			'type' => $type
		);
		return $key;
	}

	/**
	 * Pull a particular property from each assoc. array in a numeric array, 
	 * returning and array of the property values from each item.
	 *
	 *  @param  array  $a    Array to get data from
	 *  @param  string $prop Property to read
	 *  @return array        Array of property values
	 */
	static function pluck($a, $prop)
	{
		$out = array();
		for ($i = 0, $len = count($a); $i < $len; $i++) {
			$out[] = $a[$i][$prop];
		}
		return $out;
	}

	/**
	 * Return a string from an array or a string
	 *
	 * @param  array|string $a Array to join
	 * @param  string $join Glue for the concatenation
	 * @return string Joined string
	 */
	static function _flatten($a, $join = ' AND ')
	{
		if (!$a) {
			return '';
		} else if ($a && is_array($a)) {
			return implode($join, $a);
		}
		return $a;
	}
}