Skip to content

Instantly share code, notes, and snippets.

@jjb3rd

jjb3rd/data.php

Created July 21, 2012 17:49
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jjb3rd/3156545 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jjb3rd/3156545 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. jjb3rd revised this gist Aug 6, 2012. 1 changed file with 6 additions and 5 deletions.
    11 changes: 6 additions & 5 deletions data.php
    View file
    Original file line number Diff line number Diff line change
    @@ -30,8 +30,8 @@ public function get($table, $index_column, $columns) {

    // Paging
    $sLimit = "";
    if ( isset( $_GET['iStart'] ) && $_GET['iLength'] != '-1' ) {
    $sLimit = "LIMIT ".intval( $_GET['iStart'] ).", ".intval( $_GET['iLength'] );
    if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' ) {
    $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".intval( $_GET['iDisplayLength'] );
    }

    // Ordering
    @@ -83,7 +83,7 @@ public function get($table, $index_column, $columns) {
    }

    // SQL queries get data to display
    $sQuery = "SELECT `".str_replace(" , ", " ", implode("`, `", $columns))."` FROM `".$table."` ".$sWhere." ".$sOrder." ".$sLimit;
    $sQuery = "SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $columns))."` FROM `".$table."` ".$sWhere." ".$sOrder." ".$sLimit;
    $statement = $this->_db->prepare($sQuery);

    // Bind parameters
    @@ -99,6 +99,8 @@ public function get($table, $index_column, $columns) {
    $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());
    @@ -107,7 +109,7 @@ public function get($table, $index_column, $columns) {
    $output = array(
    "sEcho" => intval($_GET['sEcho']),
    "iTotalRecords" => $iTotal,
    "iTotalDisplayRecords" => strval(count($rResult)),
    "iTotalDisplayRecords" => $iFilteredTotal,
    "aaData" => array()
    );

    @@ -127,7 +129,6 @@ public function get($table, $index_column, $columns) {
    }

    echo json_encode( $output );

    }

    }
  2. jjb3rd revised this gist Aug 6, 2012. 1 changed file with 9 additions and 6 deletions.
    15 changes: 9 additions & 6 deletions data.php
    View file
    Original file line number Diff line number Diff line change
    @@ -27,11 +27,11 @@ public function __construct() {
    }

    public function get($table, $index_column, $columns) {

    // Paging
    $sLimit = "";
    if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' ) {
    $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".intval( $_GET['iDisplayLength'] );
    if ( isset( $_GET['iStart'] ) && $_GET['iLength'] != '-1' ) {
    $sLimit = "LIMIT ".intval( $_GET['iStart'] ).", ".intval( $_GET['iLength'] );
    }

    // Ordering
    @@ -88,19 +88,20 @@ public function get($table, $index_column, $columns) {

    // Bind parameters
    if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
    $statement->bindValue(':search', $_GET['sSearch'], PDO::PARAM_STR);
    $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->bindValue(':search'.$i, '%'.$_GET['sSearch_'.$i].'%', PDO::PARAM_STR);
    }
    }

    $statement->execute();
    $rResult = $statement->fetchAll();

    // Get total number of rows in table
    $iTotal = current($this->_db->query("SELECT COUNT(`".$index_column."`) FROM `".$table."`")->fetch());
    $sQuery = "SELECT COUNT(`".$index_column."`) FROM `".$table."`";
    $iTotal = current($this->_db->query($sQuery)->fetch());

    // Output
    $output = array(
    @@ -126,7 +127,9 @@ public function get($table, $index_column, $columns) {
    }

    echo json_encode( $output );

    }

    }

    header('Pragma: no-cache');
  3. jjb3rd created this gist Jul 21, 2012.
    161 changes: 161 additions & 0 deletions data.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,161 @@
    <?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 `".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();

    // Get total number of rows in table
    $iTotal = current($this->_db->query("SELECT COUNT(`".$index_column."`) FROM `".$table."`")->fetch());

    // Output
    $output = array(
    "sEcho" => intval($_GET['sEcho']),
    "iTotalRecords" => $iTotal,
    "iTotalDisplayRecords" => strval(count($rResult)),
    "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');
    }
    */
    ?>