PHP DataTables: With Custom Row support

datatables_full_including_query_search_order.php

This is a quick and easy solution to DataTables server processing
Read through the documentation, this solution supports Function columns/Variable columns
in the format of {Function} where is stored in UsersTable_FilterReplace although
you can change and modify anything.

Javascript:

<script>
$(function() {
	if((typeof is_function != 'function')) {
		function is_function(obj) {
			return (typeof obj == 'function');
		}
	}

	var Users = {
		// Getters (Makes life easier)
		getID:              function(data) { return data[0]; },
		getState:           function(data) { return data[1]; },
		getUsername:        function(data) { return data[2]; },

		// Post Processes data, 
		// Changes data just before placing them in the 
		// table.
		postProcessData:    function(data) {
			
			// Example of changing number columns to 
			// a value, this can be done on any column!
			//data[5] = ((data[5] == 0 || data[5] == "0" ? "No" : "Yes"));
			//data[6] = ((data[6] == 0 || data[6] == "0" ? "No" : "Yes"));

			return data;
		}
	};

	// Manages custom variables from server
	// etc {vars} or referred to as {Function} columns
	var UsersTable_FilterReplace = {
		// Get the data for the {Function} column of 
		// {Manage}
		"{Manage}": function(data) {
			// Setup the content
			var id   = Users.getID(data);
			var $url = "edit_user.php?id=" + id;
			
			// Return the html content for the column
			return '<a href="' + $url + '">Manage</a>';
		}
	};

	var DataTable = {
		// Our DataTable object
		dataTable:  null,

		// Inits our dataTable object
		InitDataTable: function(self, tableSelector) {
			
			self.dataTable = ($(tableSelector).DataTable( {
			
				// Change the values here, 
				// ROW 1: Number Values
				// ROW 2: Display Names
				"aLengthMenu": [
					[5, 15, 20, 40, 60, 100, -1],
					[5, 15, 20, 40, 60, 100, "R.I.P. HERE LIES YOUR PROCESSOR!"] // change per page values here
				],
				
				// Set the initial values
				"iDisplayLength": 5,
				"sDom": "C<'row'<'col-lg-6'l><'col-lg-6'f>r>t<'row'<'col-lg-6'i><'col-lg-6'p>>",
				"sPaginationType": "full_numbers",

				"aoColumnDefs": {
					'bSortable': false,
					'aTargets': [0]
				},
				
				colReorder: false,
				responsive: true,
				
				keys: true,

				"processing": true,
				"serverSide": true,
				"ajax": {
					"url":      "SomeDataTableSomeWhere.php",
					
					dataFilter: function(data){
						var json = jQuery.parseJSON( data );

						/*/ Uncomment for debugging (remove the astrix)
						
						console.log(json);
						
						console.log("SQL Queries: ");
						
						json.sql.forEach(function(entry) {
							// Removes any whitespace from the sql query
							var str = entry.replace(/\n|\t|\r/g, " ").trim();
							str = str.replace(/\s\s+/g, ' ');
							
							// Prints the query string
							console.log(str);
						});
						
						//*/
						
						// Allow for filtering {Function} Rows
						for(var x = 0; x < json.data.length; x++) {
							var itm = json.data[x];

							for(var y = 0; y < itm.length; y++) {
								var isCust = (itm[y] + "").startsWith("{") && (itm[y] + "").endsWith("}");
								var isFunc = isCust ? is_function(UsersTable_FilterReplace[itm[y]]) : false;

								if(isFunc) itm[y] = UsersTable_FilterReplace[itm[y]](itm);
							} json.data[x] = DataTable.postProcessData(itm);
						}

						return JSON.stringify( json ); // return JSON string
					}
				}
			} )); 
		}
	};

	// Init our dataTable
	UsersTable.InitDataTable(UsersTable, '#employee-grid');
});
</script>


PHP Code:

<?php
class DataTables {
	// call this method from a file or through a mvc
	// framework, this will handle all the inputs
	// all you need to do is ensure that the request
	// is from datatables or just say yolo and call this 
	// function. PLEASE MAKE SURE YOU HAVE NOT PRINTED
	// ANYTHING INTO THE BUFFER!
	public static function start() {
		// Change this line to a function 
		// that gets your database
		$db = Database::GetPDO();
		
		///
		/// 	Setup PDO Settings
		///
		$db->setAttribute(\PDO::ATTR_EMULATE_PREPARES, true);
		
		
		///
		/// 	Server processing
		///
		
		// Set default variables
		$aOutput 			= array();
		$aQuery				= array();
		$aColumns			= array();
		$aColumnsSort		= array();
		$aDataColumns		= array();
		
		$iColCount			= 0;
		$iColCustCnt		= 0;
		$iFilteredTotal		= 0;
		$iTotal 			= 0;
		$iResultAmount      = 0;
		
		$sQuery				= "";
		$sColumns			= "";
		$sLimit 			= "";
		$sOrder				= "";
		$sIndexColumn 		= "";
		$sCollate			= "COLLATE utf8_general_ci";
		
		/* Settings */ {
			$sTable 	= "users";
			
			// Use braces {} to indicate a replaced object
			// Please note that currently you can only add filtered columns at the end!
			$aColumns 		= array( 'id', 'level', 'username', '{Manage}');	// The columns that will be used in the query
			$aColumnsSort	= array( true, true,	true,		false);			// Indicate if the columns are sortable
			
			// Indexed column (used for fast and accurate table cardinality)
			$sIndexColumn = "id";
		}

		// Setup our columns!
		$iColCount    = self::countActualCols($aColumns);
		$iColCustCnt  = self::countCustomCols($aColumns);
		$aDataColumns = self::removeCustomCols($aColumns);
		
		
		/* Querying stuff */ {
			
			/* Setup Limits */ {
				if (isset($_GET['start']) && isset($_GET['length']) && $_GET['length'] != '-1')
					$sLimit = "LIMIT " . intval($_GET['length']) . " OFFSET " . intval($_GET['start']);
			}
			
			/* Ordering */ {
				$flag_0 = isset($_GET['order']);
				$flag_1 = ($flag_0 ? is_array($_GET['order']) 			  	: false);
				$flag_2 = ($flag_1 ? !  empty($_GET['order']) 			  	: false);
				$flag_3 = ($flag_2 ?    isset($_GET['order'][0]["column"]) 	: false); // Test if the array has items
				$flag_4 = ($flag_3 ?    isset($_GET['order'][0]["dir"]) 	: false);
				
				if($flag_4) {
					$sOrder = "ORDER BY ";
					$amt 	= count($_GET['order']) ;
					$cols 	= $_GET['order'];
					
					for($x = 0; $x < $amt; $x++) {
						$ord = $cols[$x];
						
						$id  = $ord['column'];
						$dir = $ord['dir'];
						
						// Check if the id is longer than our list
						// if it is then its either an attack or
						// an attempted sort on the client-sided rows.
						// TODO: this will be handled in the client-side
						if($id > $iColCount) {
							self::fatal_error("id < iColCount: $id < $iColCount");
							continue;
						}
						
						// Check for sql injection in the sorting direction
						$dir_is_asc  = (strtolower($dir) == "asc");
						$dir_is_desc = (strtolower($dir) == "desc");
						if(!$dir_is_asc && !$dir_is_desc){
							self::fatal_error("!dir_is_asc && !dir_is_desc. Dir = $dir");
							continue;
						}
						
						// Check if the column is sortable
						if($aColumnsSort[$x] == false) {
							self::fatal_error("Col cannot be sorted!");
							continue;
						}
						
						// Add our sorting
						$sOrder .=
							$aColumns[$id]. " ". $dir. ", ";
					}
				}
				
				$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 = "WHERE (";
				
				/* Whole table searching */ {
					// Check if any filtering is required
					$flag_0 = isset($_GET['search']);
					$flag_1 = ($flag_0 ? is_array($_GET['search']) : false);
					$flag_2 = ($flag_1 ? !  empty($_GET['search']) : false);
					
					if($flag_2) {
						$fVal = $_GET['search']['value'];
						$fReg = $_GET['search']['regex'];
						
						if(!self::str_isNullOrWhitespaced($fVal)) {
							
							// Add our value
							$sql = "SET @filterAll = :term";
							
							try {
								$stmt = $db->prepare($sql);
								$stmt->bindValue(":term", "%$fVal%", \PDO::PARAM_STR);
								$stmt->execute();
							} catch(\PDOException $e) {
								// error handling
								die("Could not set temp value!");
							}
							
							for($x = 0; $x < $iColCount; $x++) {
								$sWhere .= " "; {
									// column REGEXP '^ALA[0-9]'
									if ($fReg == "true")
										 $sWhere .= $aDataColumns[$x] . " REGEXP @filterAll $sCollate";
									else $sWhere .= $aDataColumns[$x] . " LIKE @filterAll $sCollate";
								} $sWhere .= " OR ";
							}
						}
					}
				}
				
				/* Specific column searching */ {
					
					// Check if any filtering is required
					$flag_0 = isset($_GET['columns']);
					$flag_1 = ($flag_0 ? is_array($_GET['columns']) 						: false);
					$flag_2 = ($flag_1 ? !  empty($_GET['columns']) 						: false);
					$flag_3 = ($flag_2 ? !  empty($_GET['columns'][0]["search"]) 			: false);
					$flag_4 = ($flag_3 ?    isset($_GET['columns'][0]["search"]["value"]) 	: false);
					
					if($flag_4) {
						$iRequestColCount = count($_GET['columns']) ;
						
						for($x = 0; $x < $iRequestColCount; $x++) {
							// Check if the id is longer than our list
							// if it is then its either an attack or
							// an attempted sort on the client-sided rows.
							// TODO: this will be handled in the client-side
							if($x > $iColCount)
								continue;
							
							$aFilter 	= $_GET['columns'][$x];
							
							// Check if the filter is valid!
							if(!is_array($aFilter)) 	  continue;
							if(!isset($aFilter['value'])) continue;
							if(!isset($aFilter['regex'])) continue;
							
							$val 		= $aFilter['value'];
							$reg		= $aFilter['regex'];
							
							// If null/whitespace then no filter
							if(self::str_isNullOrWhitespaced($val))
								continue;
							
							$colKey 			= ":filter-". $x;
							$aQuery[$colKey] 	= $val;
							
							$sWhere .= " ";
							
							// column REGEXP '^ALA[0-9]'
							if ($reg == "true")
								 $sWhere .= $aDataColumns[$x] . " REGEXP '$colKey'";
							else $sWhere .= $aDataColumns[$x] . " LIKE concat('%', $colKey , '%')";
							
							$sWhere .= " OR ";
						}
					}
				}
				
				if($sWhere == "WHERE (") {
					/* $aOutput['draw']  = $_GET['draw'];
					$aOutput['error'] = 'Could not create SQL WHERE CLAUSE!';
					header('Content-type: application/json');
					ob_get_clean();
					die(json_encode( $aOutput )); /*/
					
					$sWhere = "";
				} else {
					$sWhere = substr_replace( $sWhere, "", -3 );
					$sWhere .= ')';
				}
			}
			
			/* Run Query's */ {
				$sQuery = "
					SELECT COUNT(*) ". "
					FROM   $sTable
				"; //$aOutput['sql'][] = $sQuery; // Uncomment for debugging (SHOW SQL IN CONSOLE)
				
				try {
					$stmt  	 = $db->prepare($sQuery);
					$stmt->execute($aQuery);
					$iTotal = $stmt->fetch(\PDO::FETCH_COLUMN);
				} catch (\PDOException $ex) {
					fatal_error("(0) MSQL ERROR ". $ex->getMessage());
				}
				
				/* Fill our data */ {
					$sQuery = "
						SELECT ".str_replace(" , ", " ", implode(", ", $aDataColumns))."
						FROM   $sTable
						$sWhere
						$sOrder
						$sLimit
					"; $aOutput['sql'][] = $sQuery;
					
					
					
					try {
						$stmt  	 = $db->prepare($sQuery);
						$stmt->execute($aQuery);
						
						$aOutput['data'] = array();
						//$aOutput['data'][] = $stmt->fetchAll();
						
						$result = $stmt->fetchAll();
						foreach($result as $key) {
							$tmp = array();
							foreach($key as $r)
								$tmp[] = $r;
							
							for($x = 0; $x < $iColCustCnt; $x++)
								$tmp[] = $aColumns[$iColCount+$x];
							
							
							$iResultAmount++;
							$aOutput['data'][] = $tmp;
						}
					} catch (\PDOException $ex) { self::fatal_error("(3) MSQL ERROR SQL: ". $sQuery. " ERROR:". $ex->getMessage()); }
				}
				
				/* Get the query result */ {
					$sQuery = "
						SELECT COUNT(id) 
						FROM   $sTable
						$sWhere
					"; $aOutput['sql'][] = $sQuery;
					
					try {
						$stmt = $db->prepare($sQuery);
						$stmt->execute($aQuery);
						$iFilteredTotal = $stmt->fetch(\PDO::FETCH_COLUMN);
					} catch (\PDOException $ex) {
						self::fatal_error("(1) MSQL ERROR ". $ex->getMessage());
					}
					
				}
			}
		}
		
		$iTotal         = $iTotal - $iResultAmount;
		$iFilteredTotal == 0 ? "0" : ($iFilteredTotal - $iResultAmount);
		
		$aOutput['draw'] 				 = $_GET['draw'];
		$aOutput['recordsTotal'] 		 = $iTotal;
		$aOutput['recordsFiltered'] 	 = $iFilteredTotal;
		$aOutput['request']              = $_GET;
		return $aOutput;
	}
	
}
?>