Jump to content

Help converting DataTables

brandon66

By brandon66
in PHP Coding Help

 Share
Go to solution Solved by brandon66,

Recommended Posts

brandon66 Newbie

brandon66

Posted
Posted (edited)

Hello i was wondering if anyone could help convert my datatable over to the server side datatables. I attached my full code for the datatable

php
//connect to database
include 'Model/DBAdapter.php';
//create sql statement
$sql = "SELECT RMA_Number, Person_Calling, Company_Name, Unit_Serial_Number,
    CONCAT(Employee_First_Name,' ',Employee_Last_Name) AS Employee_Name,
    Call_Date,Received_Date,RMA_Status, Reason_For_Return, Notes, New_Install,
    New_Unit_Serial_Number, Terminal_ID, Account_Number, Account_Name FROM RMA
			JOIN Companies ON RMA.Company_ID  = Companies.Company_ID
			JOIN Employees ON RMA.Employee_ID  = Employees.Employee_ID";
//Retrieve data
$result = $connection->query($sql);
//process results
if ($result) {
    //loop through data
    while ($view = $result->fetch()) {
        $views[] = $view;
    }
    //display
    include 'View/View.html.php';
} else {
    //error message
    $output = "No RMA found in database.";
    include 'View/Template.html.php';
}


<body>
        <div id="wrapper">
            <h1 id="tableTitle">RMA's Submitted</h1>
            <table id="table">
                <thead>
                    <tr>
                        <th>RMA Number</th>
                        <th>Person Calling</th>
                        <th>Company ID</th>
                        <th>ISN</th>
                        <th>Employee</th>
                        <th>Call Date</th>	
                        <th>Received Date</th>
                        <th>RMA Status</th>	
                        <th>Reason for Return</th>
                        <th>Notes</th>
                        <th>New Install</th>
                        <th>New Serial Number</th>
                        <th>Terminal ID</th>
                        <th>Account Number</th>
                        <th>Account Name</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($views as $view): ?>
                        <tr>
                            <td><?php echo $view['RMA_Number']; ?></td>
                            <td><?php echo $view['Person_Calling']; ?></td>
                            <td><?php echo $view['Company_Name']; ?></td>
                            <td><?php echo $view['Unit_Serial_Number']; ?></td>
                            <td><?php echo $view['Employee_Name']; ?></td>
                            <td><?php echo $view['Call_Date']; ?></td>
                            <td><?php echo $view['Received_Date']; ?></td>
                            <td><?php echo $view['RMA_Status']; ?></td>
                            <td><?php echo $view['Reason_For_Return']; ?></td>
                            <td><?php echo $view['Notes']; ?></td>
                            <td><?php echo $view['New_Install']; ?></td>
                            <td><?php echo $view['New_Unit_Serial_Number']; ?></td>
                            <td><?php echo $view['Terminal_ID']; ?></td>
                            <td><?php echo $view['Account_Number']; ?></td>
                            <td><?php echo $view['Account_Name']; ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </body>

here is an example of the server-side with datatables  but im using pdo.

 

http://www.datatables.net/release-datatables/examples/data_sources/server_side.html

View.html.zip

Edited by brandon66
Link to comment
Share on other sites
mac_gyver Prolific Member

mac_gyver

Posted
Posted (edited)

i recommend that you first get the datatables example to work using PDO so that you will become familiar with what the server-side code is doing, then all you will need to basically do is to alter the query section in it to use your SELECT ... FROM ... JOIN  terms. the example code is responsible for producing the WHERE, ORDER BY, and LIMIT terms based on the values sent from the javascript/ajax.

 

it's unfortunate that the author of the server-side example didn't abstract the database statements so that switching to a different driver wouldn't be that hard. however, based on your previous thread, i went through the datatables server-side example and modified the server_processing.php code so that it would make use of a database wrapper. the following is the datatables example server_processing.php code and a minimal mysqli wrapper class (you would need to write/modify the wrapper class to use PDO statements) -

 

server_processing.php

<?php
include 'db.mysqli.class.php';
//include 'db.sqlite3.class.php';
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Easy set variables
     */
    
    /* Array of database columns which should be read and sent back to DataTables. Use a space where
     * you want to insert a non-database field (for example a counter or static image)
     */
    $aColumns = array( 'engine', 'browser', 'platform', 'version', 'grade' );
    
    /* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "id";
    
    /* DB table to use */
    $sTable = "ajax";
    
    /* Database connection information */
    $gaSql['user']       = "";
    $gaSql['password']   = "";
    $gaSql['db']         = "";
    $gaSql['server']     = "";
    
    //$gaSql['db']         = "{$_SERVER['DOCUMENT_ROOT']}/test1.sqlite";    // path/filename for sqlite db file
    
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * If you just want to use the basic configuration for DataTables with PHP server-side, there is
     * no need to edit below this line
     */
    
    /*
     * Local functions
     */
    function fatal_error ( $sErrorMessage = '' )
    {
        header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
        die( $sErrorMessage );
    }
    
    /*
     * database connection
     */
     $gaSql['link'] = new db(); // instance of db wrapper
    
    if ( ! $gaSql['link']->connect( $gaSql['server'], $gaSql['user'], $gaSql['password']  ) )
    {
        fatal_error( 'Could not open connection to server' );
    }

    if ( ! $gaSql['link']->select_db( $gaSql['db'] ) )
    {
        fatal_error( 'Could not select database ' );
    }

    /*
     * 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" )
            {
                $sOrder .= "`".$aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."` ".
                    ($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
            }
        }
        
        $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($aColumns) ; $i++ )
        {
            if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
            {
                $sWhere .= "`".$aColumns[$i]."` LIKE '%".$gaSql['link']->escape( $_GET['sSearch'] )."%' OR ";
            }
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ')';
    }
    
    /* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
        {
            if ( $sWhere == "" )
            {
                $sWhere = "WHERE ";
            }
            else
            {
                $sWhere .= " AND ";
            }
            $sWhere .= "`".$aColumns[$i]."` LIKE '%".$gaSql['link']->escape($_GET['sSearch_'.$i])."%' ";
        }
    }
    
    /*
     * SQL queries
     * Get data to display
     */
    
     // sqlite does not support SQL_CALC_FOUND_ROWS, removed from query - // SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $aColumns))."`
    $sQuery = "
        SELECT `".str_replace(" , ", " ", implode("`, `", $aColumns))."`
        FROM   $sTable
        $sWhere
        $sOrder
        $sLimit
        ";
    $rResult = $gaSql['link']->query( $sQuery ) or fatal_error( 'Database Error: ' . $gaSql['link']->errno() );
    
    /* Data set length after filtering */
    //sqlite does not support SQL_CALC_FOUND_ROWS, changed query to COUNT() with same where clause as above // $sQuery = "
        //SELECT FOUND_ROWS()
    //";
    $sQuery = "
        SELECT COUNT(`".$sIndexColumn."`)
        FROM   $sTable
        $sWhere
    ";
    $rResultFilterTotal = $gaSql['link']->query( $sQuery ) or fatal_error( 'Database Error: ' . $gaSql['link']->errno() );
    $aResultFilterTotal = $rResultFilterTotal->fetch_array();
    $iFilteredTotal = $aResultFilterTotal[0];
    
    /* Total data set length */
    $sQuery = "
        SELECT COUNT(`".$sIndexColumn."`)
        FROM   $sTable
    ";
    $rResultTotal = $gaSql['link']->query( $sQuery ) or fatal_error( 'Database Error: ' . $gaSql['link']->errno() );
    $aResultTotal = $rResultTotal->fetch_array();
    $iTotal = $aResultTotal[0];
    
    /*
     * Output
     */
    $output = array(
        "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );
    
    while ( $aRow = $rResult->fetch_array() )
    {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( $aColumns[$i] == "version" )
            {
                /* Special output formatting for 'version' column */
                $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
            }
            else if ( $aColumns[$i] != ' ' )
            {
                /* General output */
                $row[] = $aRow[ $aColumns[$i] ];
            }
        }
        $output['aaData'][] = $row;
    }
    
    echo json_encode( $output );
?>

db.mysqli.class.php

<?php

// minimal mysqli db wrapper class to support the jquery datatables server side example

// result class
class db_result{

    private $resource;
    
    public function __construct($resource){

        $this->resource = $resource;
        
    }
    
    public function fetch_array(){
        
        return $this->resource->fetch_array();
    
    }
}

// db wrapper class using mysqli
class db {
    private $db; // instance of the actual database class
    private $errno; // last error number
    private $error; // last error message
    
    public function __construct(){
        // do nothing
    }
    
    public function connect($host,$user,$pwd){

        $result = $this->db = mysqli_connect($host, $user, $pwd );
        $this->errno = $this->db->errno;
        $this->error = $this->db->error;
        return $result;

    }
    
    public function select_db($db){
    
        $result = $this->db->select_db($db);
        $this->errno = $this->db->errno;
        $this->error = $this->db->error;
        return $result;
    
    }
    
    public function query($query){ // result set type of query
    
        $result = $this->db->query($query);
        $this->errno = $this->db->errno;
        $this->error = $this->db->error;
        if($result){
            // query ran
            return new db_result($result);
        
        } else {
            // query failed
            return $result;
        }
    }
    
    
    public function escape($string){

        return $this->db->real_escape_string($string);

    }
    
    public function errno(){ // provide get method to permit all method access - $db->errno()

        return $this->errno;

    }

    public function error(){

        return $this->error;

    }
}
?>
Edited by mac_gyver
Link to comment
Share on other sites
brandon66 Newbie

brandon66

Posted
  • Author
Posted

This is what i came up with for the code but i get a json error when trying to run it

<?php

     //connect to database
     include 'Model/DBAdapter.php';


    $aColumns = array( 'RMA_Number', 'Person_Calling', 'c.Company_Name', 'Unit_Serial_Number', 'Employee_Name',
                        'Call_Date','Received_Date','RMA_Status','Reason_For_Return','Notes','New_Install',
                        'New_Unit_Serial_Number', 'Terminal_ID','Account_Number','Account_Name');
     
    /* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "RMA_Number";
     
    /* DB table to use */
    $sTable = "RMA";
    
    //Join to use
    $sJoin = 'JOIN Companies c ON RMA.Company_ID  = Companies.Company_ID';
    $sJoin .= ' JOIN Employees e ON RMA.Employee_ID  = Employees.Employee_ID';
     

     
    /*
     * 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" )
            {
                $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                    ".($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
            }
        }
         
        $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($aColumns) ; $i++ )
        {
            if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
            {
                $sWhere .= $aColumns[$i]." LIKE '%".  htmlspecialchars( $_GET['sSearch'] )."%' OR ";
            }
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ')';
    }
     
    /* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
        {
            if ( $sWhere == "" )
            {
                $sWhere = "WHERE ";
            }
            else
            {
                $sWhere .= " AND ";
            }
            $sWhere .= $aColumns[$i]." LIKE '%".  htmlspecialchars($_GET['sSearch_'.$i])."%' ";
        }
    }
     
     
    /*
     * SQL queries
     * Get data to display
     */
    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aColumns)) . "
        FROM   $sTable
        $sJoin
        $sWhere
        $sOrder
        $sLimit
    ";
try {
    $rResult = $connection->query($sQuery);
} catch (PDOException $e) {
    $error = 'Error getting data: ' . $e->getMessage();
    echo $error;
    exit();
}

/* Data set length after filtering */
    $sQuery = "
        SELECT FOUND_ROWS()
    ";
    try{
    $rResultFilterTotal = $connection->query($sQuery);
    $aResultFilterTotal = $rResultFilterTotal->fetchAll();
    $iFilteredTotal = $aResultFilterTotal[0];
    }catch (PDOException $e) {
    $error = 'Error getting found rows: ' . $e->getMessage();
    echo $error;
    exit();
}
    /* Total data set length */
    $sQuery = "
        SELECT COUNT(".$sIndexColumn.")
        FROM   $sTable
    ";
    $rResultTotal = $connection->query($sQuery);
    $aResultTotal = $rResultTotal->fetchAll();
    $iTotal = $aResultTotal[0];
     
     
    /*
     * Output
     */
    $output = array(
        "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );
     
    while ( $aRow =  $rResult->fetchAll())
    {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        { 
             if ( $aColumns[$i] != ' ' )
            {
                /* General output */
                $row[] = $aRow[ $aColumns[$i] ];
            }
        }
        $output['aaData'][] = $row;
    }
     
    echo json_encode( $output );
?>
Link to comment
Share on other sites
brandon66 Newbie

brandon66

Posted
  • Author
Posted

i am now getting undefined index error when trying to run this c. company_name otherwise the json is good

<?php

     //connect to database
     include '../../Model/DBAdapter.php';


    $aColumns = array( 'RMA_Number', 'Person_Calling', 'c.Company_Name', 'Unit_Serial_Number', 'CONCAT(e.Employee_First_Name, " ", e.Employee_Last_Name)',
                        'Call_Date','Received_Date','RMA_Status','Reason_For_Return','Notes','New_Install',
                        'New_Unit_Serial_Number', 'Terminal_ID','Account_Number','Account_Name');
     
    /* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "RMA_Number";
     
    /* DB table to use */
    $sTable = "RMA";
    
    //Join to use
    $sJoin = 'JOIN Companies c ON RMA.Company_ID  = c.Company_ID';
    $sJoin .= ' JOIN Employees e ON RMA.Employee_ID  = e.Employee_ID';
     

     
    /*
     * 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" )
            {
                $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                    ".($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
            }
        }
         
        $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($aColumns) ; $i++ )
        {
            if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
            {
                $sWhere .= $aColumns[$i]." LIKE '%".  htmlspecialchars( $_GET['sSearch'] )."%' OR ";
            }
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ')';
    }
     
    /* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
        {
            if ( $sWhere == "" )
            {
                $sWhere = "WHERE ";
            }
            else
            {
                $sWhere .= " AND ";
            }
            $sWhere .= $aColumns[$i]." LIKE '%".  htmlspecialchars($_GET['sSearch_'.$i])."%' ";
        }
    }
     
     
    /*
     * SQL queries
     * Get data to display
     */
    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aColumns)) . "
        FROM   $sTable
        $sJoin
        $sWhere
        $sOrder
        $sLimit
    ";
try {
    $rResult = $connection->query($sQuery);
} catch (PDOException $e) {
    $error = 'Error getting data: ' . $e->getMessage();
    echo $error;
    exit();
}

/* Data set length after filtering */
    $sQuery = "
        SELECT FOUND_ROWS()
    ";
    try{
    $rResultFilterTotal = $connection->query($sQuery);
    $aResultFilterTotal = $rResultFilterTotal->fetch();
    $iFilteredTotal = $aResultFilterTotal[0];
    }catch (PDOException $e) {
    $error = 'Error getting found rows: ' . $e->getMessage();
    echo $error;
    exit();
}
    /* Total data set length */
    $sQuery = "
        SELECT COUNT(".$sIndexColumn.")
        FROM   $sTable
    ";
    $rResultTotal = $connection->query($sQuery);
    $aResultTotal = $rResultTotal->fetch();
    $iTotal = $aResultTotal[0];
     
     
    /*
     * Output
     */
    $output = array(
        "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );
     
    while ( $aRow =  $rResult->fetch())
    {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        { 
             if ( $aColumns[$i] != ' ' )
            {
                /* General output */
                $row[] = $aRow[ $aColumns[$i] ];
            }
        }
        $output['aaData'][] = $row;
    }
     
    echo json_encode( $output );
?>
Link to comment
Share on other sites
  • Solution
brandon66 Newbie

brandon66

Posted
  • Author
  • Solution
Posted

ended up fixing it with this!!

<?php

     //connect to database
     include '../../Model/DBAdapter.php';


    $aColumns = array( 'RMA_Number', 'Person_Calling', 'Company_Name', 'Unit_Serial_Number', 'CONCAT(e.Employee_First_Name, " ", e.Employee_Last_Name)',
                        'Call_Date','Received_Date','RMA_Status','Reason_For_Return','Notes','New_Install',
                        'New_Unit_Serial_Number', 'Terminal_ID','Account_Number','Account_Name');
     
    /* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "RMA_Number";
     
    /* DB table to use */
    $sTable = "RMA";
    
    //Join to use
   $sJoin = ' JOIN Companies ON RMA.Company_ID  = Companies.Company_ID';
   $sJoin .= ' JOIN Employees e ON RMA.Employee_ID  = e.Employee_ID';

   
    /*
     * 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" )
            {
                $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                    ".($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
            }
        }
         
        $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($aColumns) ; $i++ )
        {
            if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
            {
                $sWhere .= $aColumns[$i]." LIKE '%".  htmlspecialchars( $_GET['sSearch'] )."%' OR ";
            }
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ')';
    }
     
    /* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
        {
            if ( $sWhere == "" )
            {
                $sWhere = "WHERE ";
            }
            else
            {
                $sWhere .= " AND ";
            }
            $sWhere .= $aColumns[$i]." LIKE '%".  htmlspecialchars($_GET['sSearch_'.$i])."%' ";
        }
    }
     
     
    /*
     * SQL queries
     * Get data to display
     */
    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aColumns)) . "
        FROM   $sTable
        $sJoin
        $sWhere
        $sOrder
        $sLimit
    ";
try {
    $rResult = $connection->query($sQuery);
} catch (PDOException $e) {
    $error = 'Error getting data: ' . $e->getMessage();
    echo $error;
    exit();
}

/* Data set length after filtering */
    $sQuery = "
        SELECT FOUND_ROWS()
    ";
    try{
    $rResultFilterTotal = $connection->query($sQuery);
    $aResultFilterTotal = $rResultFilterTotal->fetch();
    $iFilteredTotal = $aResultFilterTotal[0];
    }catch (PDOException $e) {
    $error = 'Error getting found rows: ' . $e->getMessage();
    echo $error;
    exit();
}
    /* Total data set length */
    $sQuery = "
        SELECT COUNT(".$sIndexColumn.")
        FROM   $sTable
    ";
    $rResultTotal = $connection->query($sQuery);
    $aResultTotal = $rResultTotal->fetch();
    $iTotal = $aResultTotal[0];
     
     
    /*
     * Output
     */
    $output = array(
        "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );
  
    while ( $aRow =  $rResult->fetch())
    {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        { 
             if (!empty($aColumns[$i]) )
            {
                /* General output */
                $row[] = $aRow[ $aColumns[$i] ];
            }
        }
        $output['aaData'][] = $row;
    }
     
    echo json_encode($output);
Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.