<?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'); | |
} | |
*/ | |
?> |
This comment has been minimized.
This comment has been minimized.
I must have had some old code or something, pagination works now for me. Thanks for this! |
This comment has been minimized.
This comment has been minimized.
Thank you very much for this pdo version of datatables ajax source. Greatly appreciated! |
This comment has been minimized.
This comment has been minimized.
This works great, thank you :) how would you use the function multiple times to UNION the tables? |
This comment has been minimized.
This comment has been minimized.
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 |
This comment has been minimized.
This comment has been minimized.
I am setting emulate prepares to false which is causing the problem above... any ideas? |
This comment has been minimized.
This comment has been minimized.
Does anyone monitor this page? Still would like to know about the error above when settings emulate prepares to false... |
This comment has been minimized.
This comment has been minimized.
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. |
This comment has been minimized.
This comment has been minimized.
my db's are getting too big and I now have to start to use server-side, not going well. |
This comment has been minimized.
This comment has been minimized.
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 |
This comment has been minimized.
This comment has been minimized.
@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. |
This comment has been minimized.
This comment has been minimized.
gr8 m8 |
This comment has been minimized.
This comment has been minimized.
How to add where conditions in request. Means i want my custom where to be added along with the datatables request.?? |
This comment has been minimized.
This comment has been minimized.
# thanks john that's really usefull, keep on update .. |
This comment has been minimized.
This comment has been minimized.
This is really usefull thank you, but how can i call it. i tried this
|
This comment has been minimized.
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.