Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
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');
}
*/
?>
@Ragowit

This comment has been minimized.

Show comment Hide comment
@Ragowit

Ragowit Jul 31, 2012

Thank you, have been looking for this!

However, pagination seems to be broken? I get "Showing 1 to 10 of 10 entries (filtered from 788 total entries)" but pagination is dead. I see a previous and next link, but they do nothing.

Ragowit commented Jul 31, 2012

Thank you, have been looking for this!

However, pagination seems to be broken? I get "Showing 1 to 10 of 10 entries (filtered from 788 total entries)" but pagination is dead. I see a previous and next link, but they do nothing.

@Ragowit

This comment has been minimized.

Show comment Hide comment
@Ragowit

Ragowit Aug 17, 2012

I must have had some old code or something, pagination works now for me. Thanks for this!

Ragowit commented Aug 17, 2012

I must have had some old code or something, pagination works now for me. Thanks for this!

@fernando-basso

This comment has been minimized.

Show comment Hide comment
@fernando-basso

fernando-basso Aug 3, 2013

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

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

@yuanhuynh

This comment has been minimized.

Show comment Hide comment
@yuanhuynh

yuanhuynh Sep 27, 2013

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

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

@morrow95

This comment has been minimized.

Show comment Hide comment
@morrow95

morrow95 Nov 21, 2013

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

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

This comment has been minimized.

Show comment Hide comment
@morrow95

morrow95 Nov 27, 2013

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

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

@morrow95

This comment has been minimized.

Show comment Hide comment
@morrow95

morrow95 Dec 30, 2013

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

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

@jjb3rd

This comment has been minimized.

Show comment Hide comment
@jjb3rd

jjb3rd 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.

Owner

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.

@mynumbercrunching

This comment has been minimized.

Show comment Hide comment
@mynumbercrunching

mynumbercrunching 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.

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

This comment has been minimized.

Show comment Hide comment
@snaquaye

snaquaye Aug 23, 2015

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

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

This comment has been minimized.

Show comment Hide comment
@sraboy

sraboy 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.

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.

@Turox

This comment has been minimized.

Show comment Hide comment
@Turox

Turox Jul 19, 2016

gr8 m8

Turox commented Jul 19, 2016

gr8 m8

@pakistanihaider

This comment has been minimized.

Show comment Hide comment
@pakistanihaider

pakistanihaider Apr 18, 2017

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

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

@ox-b

This comment has been minimized.

Show comment Hide comment
@ox-b

ox-b May 14, 2017

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

ox-b commented May 14, 2017

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

@afaaro

This comment has been minimized.

Show comment Hide comment
@afaaro

afaaro 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>

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