Skip to content

Instantly share code, notes, and snippets.

@snaquaye
Forked from jjb3rd/data.php
Last active June 11, 2024 22:26
Show Gist options
  • Save snaquaye/4d68079ef98cdc2e7937 to your computer and use it in GitHub Desktop.
Save snaquaye/4d68079ef98cdc2e7937 to your computer and use it in GitHub Desktop.
An updated server-side processing script for DataTables
<?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)
*/
namespace Common\Utilities;
require_once __DIR__.'/../../../generated-conf/db.properties.php';
class TableData {
private $_db;
public function __construct() {
try {
$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));
} catch (PDOException $e) {
error_log("Failed to connect to database: ".$e->getMessage());
}
}
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['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(
"draw" => intval($_GET['draw']),
"recordsTotal" => $iTotal,
"recordsFiltered" => $iFilteredTotal,
"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_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');
}
*/
?>
@sraboy
Copy link

sraboy commented Jul 17, 2016

@georgekaratsiolis, the issue, I believe, is the use of sSearch. I've made a few updates that work for me for server-side processing; it looks like a bunch of the parameters have changed names again. I didn't really know anything about PHP, JS or AJAX before yesterday so YMMV, but here are my changes: https://gist.github.com/sraboy/3102387f44248a80cb9e9e1fe851307d.

Neither searching nor ordering worked until these parameter names were updated. They both appear to work great now. The only thing I haven't tested is changing the ordering at runtime (I just have a single column ordered by default). This was tested with DataTables-1.10.12 and JQuery-3.1.0, with no other plug-ins/extensions.

@zaibtabs
Copy link

zaibtabs commented Jan 2, 2019

Hi Everyone,

Please can someone explain the above script or this line
namespace Common\Utilities;

Why we are not using POST instead of GET.

and any sample code, on how to integrate this API.

Any what if, we got a image in select statement, how do we achieve this?

Kind regards,
Zaib Tabs

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment