Server side processing code using php mysqli objected oriented

DatatablesJSON.php

<?php
/*
 * Script:    DataTables server-side script for PHP and MySQL
 * Copyright: Nov, 2016 - Allan Jes
 * Copyright: 2016 - Steven Lavoie
 * Copyright: 2012 - John Becker, Beckersoft, Inc.
 * Copyright: 2010 - Allan Jardine
 * License:   GPL v2 or BSD (3-point)
 * URL:   https://gist.github.com/ajoluvya/
 */
 
$curdir = dirname(__FILE__);
require_once($curdir.'/Db.php');
class DataTable extends Db{
	
	public function get($table, $index_column, $columns) {
		// Paging
		$sLimit = "";
		if ( isset( $_POST['start'] ) && $_POST['length'] != '-1' ) {
			$sLimit = "LIMIT ".intval( $_POST['start'] ).", ".intval( $_POST['length'] );
		}
		
		// Ordering
		$sOrder = "";
		if ( isset( $_POST['order'][0]['column'] ) && $_POST['columns'][$_POST['order'][0]['column']]['orderable'] == "true" ) {
			$sOrder = "ORDER BY ".$columns[$_POST['order'][0]['column']]." ".$_POST['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($_POST['search']['value']) && $_POST['search']['value'] != "" ) {
			$sWhere = "WHERE (";
			for ( $i=0 ; $i<count($columns) ; $i++ ) {
				if ( isset($_POST['columns'][$i]['searchable']) && $_POST['columns'][$i]['searchable'] == "true" ) {
					$sWhere .= $columns[$i]." LIKE ? OR ";
				}
			}
			$sWhere = substr_replace( $sWhere, "", -3 ); //deletes the last OR
			$sWhere .= ')';
		}
		
		// Individual column filtering
		for ( $i=0 ; $i<count($columns) ; $i++ ) {
			if ( isset($_POST['columns'][$i]['searchable']) && $_POST['columns'][$i]['searchable'] == "true" && $_POST['columns'][$i]['search']['value'] != '' ) {
				if ( $sWhere == "" ) {
					$sWhere = "WHERE ";
				}
				else {
					$sWhere .= " AND ";
				}
				$sWhere .= $columns[$i]." LIKE ? ";
			}
		}
		
		// SQL queries get data to display
		$sQuery = "SELECT SQL_CALC_FOUND_ROWS ".
			str_replace(" , ", " ", implode(", ", $columns)).
			" FROM ".
			$table.
			" ".
			$sWhere.
			" ".
			$sOrder.
			" ".
			$sLimit;
		//prepare the statement
		$preparedStatement = $this->prepareStatement($sQuery);
		if($preparedStatement){
			// Bind parameters
			$data_types = '';
			$a_params = $bind_params = array();
			if ( isset($_POST['search']['value']) && $_POST['search']['value'] != "" ) {
				for ( $i=0 ; $i<count($columns) ; $i++ ) {
					if ( isset($_POST['columns'][$i]['searchable']) && $_POST['columns'][$i]['searchable'] == "true" ) {
						$data_types .= 's';
						$bind_params[]= '%'.$_POST['search']['value'].'%';
					}
				}
			}
			for ( $i=0 ; $i<count($columns) ; $i++ ) {
				if ( isset($_POST['columns'][$i]['searchable']) && $_POST['columns'][$i]['searchable'] == "true" && $_POST['columns'][$i]['search']['value'] != '' ) {
					$data_types .= 's';
					$bind_params[]= '%'.$_POST['columns'][$i]['search']['value'].'%';
				}
			}
			
			if(strlen($data_types)>0){
				/* with call_user_func_array, array params must be passed by reference */
				$a_params[] = & $data_types;
			}
			
			for($i = 0; $i < count($bind_params); $i++) {
				$a_params[] = & $bind_params[$i];
			}
			
			if(strlen($data_types)>0){
				$this->bindParam($preparedStatement, $a_params); //possible data types i - integer, d - double, s - string, b - blob 
			}
			
			$rResult = $this->fetchResult($preparedStatement);
		
			$iFilteredTotal = current($this->getFilteredTotal());
			
			// Get total number of rows in query
			$sQuery = "SELECT COUNT(".$index_column.") cnt FROM ".$table;
			$iTotal = $this->countCustom($sQuery);
			
			// Output
			$output = array(
				"draw" => intval($_POST['draw']),
				"recordsTotal" => $iTotal,
				"recordsFiltered" => $iFilteredTotal,
				//"query" => $preparedStatement,		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[ preg_replace("/`[A-Za-z]+`\.|`/", "", $columns[$i]) ];
					}
				} 
				$output['data'][] = $row;*/
				$output['data'][] = $aRow;
			}
			
			echo json_encode( $output );
		}
	}
}
?>

Db.php

<?php
class Db{
	public $server;
	public $user;
	public $password;
	public $database;
	public $sessions = array();

	//Properties for Database Transactions
	public $foreignKeys;
	public $fields;
	public $table;
	public $primaryKey;
	public $connectedToKey;
	var $conn;

	public function __construct(){
	   $this->server = "server_url";
	   $this->user = "user_name";
	   $this->password = "user_pass";
	   $this->database = "your_db";
	   //Connects to the database;
	   $this->connectDB();
	   
	}
	function connectDB(){
		$this->conn = new mysqli($this->server, $this->user, $this->password, $this->database);
		if ($this->conn->connect_errno) {
			echo "Error connection: %s\n".$this->conn->connect_error;
			exit();
		}
	}
		//returns a count of records according to the custom query passed to the function
	function countCustom($query){
		$res  = $this->conn->query($query);
		if($res){
			$q = $res->fetch_array();
			$cnt = $q{'cnt'};
			$res->close();
			return $cnt;
		}
	}
  //Query used by datatables to populate the table data
	//returns a count of records according to the custom query passed to the function
	function prepareStatement($query){
		$statement = $this->conn->prepare($query);
		if($statement === false) {
			trigger_error('Wrong SQL: ' . $query . ' Error: ' . $this->conn->errno . ' ' . $this->conn->error, E_USER_ERROR);
		}
		return $statement;
	}
	function bindParam($preparedStatement, $a_params){
		/* $preparedStatement->bind_param($data_type, $a_params);
		
		/* The problem

		$preparedStatement->bind_param() does not accept params array. So, how to bind params, if their number is variable, depending on user input in your application?

		A workaround is to use call_user_func_array to pass dynamically the params array.*/
		call_user_func_array(array($preparedStatement, 'bind_param'), $a_params);
	}
	function fetchResult($preparedStatement){
		$preparedStatement->execute();
		
		return $preparedStatement->get_result();
	}
	//returns a count of records according to the custom query passed to the function
	function getFilteredTotal(){
		return $this->conn->query('SELECT FOUND_ROWS()')->fetch_array();
	}
}
?>

server_processing.php

<?php
require_once("DatatablesJSON.php");
$table = "`table`";

	$primary_key = "`primary_key`";

	$columns = array( "`col1`", "`col2`", "`col3`" );
	// Get the data
	$data_table->get($table, $primary_key, $columns);
?>