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');
}
*/
?>
@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