-
-
Save snaquaye/4d68079ef98cdc2e7937 to your computer and use it in GitHub Desktop.
An updated server-side processing script for DataTables
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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'); | |
} | |
*/ | |
?> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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