Skip to content

Instantly share code, notes, and snippets.

@jjb3rd
Created July 21, 2012 17:49
Show Gist options
  • Save jjb3rd/3156545 to your computer and use it in GitHub Desktop.
Save jjb3rd/3156545 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)
*/
class TableData {
private $_db;
public function __construct() {
try {
$host = 'localhost';
$database = 'my_database';
$user = 'myusername';
$passwd = 'mypassword';
$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['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' ) {
$sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".intval( $_GET['iDisplayLength'] );
}
// 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(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => 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['aaData'][] = $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');
}
*/
?>
@fernando-basso
Copy link

Thank you very much for this pdo version of datatables ajax source. Greatly appreciated!

@yuanhuynh
Copy link

This works great, thank you :) how would you use the function multiple times to UNION the tables?

@morrow95
Copy link

Searching is not working for me and not sure why... everything else is working fine. Any ideas?

DataTables warning (table id = 'sample_1'): DataTables warning: JSON data from server could not be parsed. This is caused by a JSON formatting error.


Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number' in /home/test/public_html/assets/data-tables/test-pdo.php:99 Stack trace: #0 /home/test/public_html/assets/data-tables/test-pdo.php(99): PDOStatement->execute() #1 /home/test/public_html/assets/data-tables/test-pdo.php(143): TableData->get('accounts', 'account_id', Array) #2 {main}

thrown in /home/test/public_html/assets/data-tables/test-pdo.php on line 99

line 99 is - $statement->execute(); from Bind Parameters

@morrow95
Copy link

I am setting emulate prepares to false which is causing the problem above... any ideas?

@morrow95
Copy link

Does anyone monitor this page? Still would like to know about the error above when settings emulate prepares to false...

@jjb3rd
Copy link
Author

jjb3rd commented Mar 24, 2014

I don't really monitor the page. You may have better luck in a PDO forum on that issue. If you delve into what the code is doing it's bit limited in what it does. In other projects I've found it easier to implement the querying code and populating of DataTables manually if it requires more fine-grained control.

Copy link

ghost commented Apr 9, 2015

my db's are getting too big and I now have to start to use server-side, not going well.
how would i pass in extra variables in for the sql like "where acctID = 100"?
sorry for my rookie question. cheers.

@snaquaye
Copy link

Hello,

Thanks for the PDO class. You made factoring it into my API beautiful. I did some changes to meet the specification of the current api. https://gist.github.com/snaquaye/4d68079ef98cdc2e7937

Thanks

@sraboy
Copy link

sraboy commented Jul 17, 2016

@jjb3rd @snaquaye, a few more updates were required for me on DT 1.10.12 w/ JQ 3.1.0. Thanks for the head start! Here are my changes: https://gist.github.com/sraboy/3102387f44248a80cb9e9e1fe851307d. The documentation was pretty hard to follow (at least for someone like me who is brand-new to PHP, AJAX, JS and all this web stuff) so I'm not sure if this is really the right way but it's the way that works. I'm still a bit surprised that a working set of example scripts isn't included in the regular releases; there appear to have been a lot of breaking changes.

@ADCDS
Copy link

ADCDS commented Jul 19, 2016

gr8 m8

@haider-pw
Copy link

How to add where conditions in request. Means i want my custom where to be added along with the datatables request.??

@ox-b
Copy link

ox-b commented May 14, 2017

# thanks john that's really usefull, keep on update ..

@afaaro
Copy link

afaaro commented Apr 28, 2018

This is really usefull thank you, but how can i call it. i tried this

<script type="text/javascript"> $(document).ready(function() { $('#table').dataTable({ "bProcessing": true, "bServerSide": true, "sAjaxSource": "data.php?op=table" }); }); </script>

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