Created
July 21, 2012 17:49
-
-
Save jjb3rd/3156545 to your computer and use it in GitHub Desktop.
An updated server-side processing script for DataTables
This file contains hidden or 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) | |
| */ | |
| 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'); | |
| } | |
| */ | |
| ?> |
@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.
gr8 m8
How to add where conditions in request. Means i want my custom where to be added along with the datatables request.??
# thanks john that's really usefull, keep on update ..
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
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