Jump to content


Photo

Help converting DataTables


Best Answer brandon66, 19 November 2013 - 08:16 PM

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);

Go to the full post


  • Please log in to reply
6 replies to this topic

#1 brandon66

brandon66

    Member

  • Members
  • PipPip
  • 23 posts

Posted 19 November 2013 - 04:33 PM

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.datatable...erver_side.html

Attached Files


Edited by brandon66, 19 November 2013 - 04:46 PM.


#2 mac_gyver

mac_gyver

    Advanced Member

  • Moderators
  • 1,910 posts

Posted 19 November 2013 - 05:30 PM

did you even try to do this?

 

all you have posted is your existing code and that you want to change it to use the server side datatables. where exactly are you stuck at?


multi-purpose programming fool and resident naysayer [We try not be negative in replies, but telling someone what they're doing wrong, while staying politically correct, isn't always going to happen.]

#3 brandon66

brandon66

    Member

  • Members
  • PipPip
  • 23 posts

Posted 19 November 2013 - 05:34 PM

The part where im stuck is that im using PDO and im also joining table, just not sure where i should start with that.



#4 mac_gyver

mac_gyver

    Advanced Member

  • Moderators
  • 1,910 posts

Posted 19 November 2013 - 06:40 PM

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, 19 November 2013 - 06:42 PM.

multi-purpose programming fool and resident naysayer [We try not be negative in replies, but telling someone what they're doing wrong, while staying politically correct, isn't always going to happen.]

#5 brandon66

brandon66

    Member

  • Members
  • PipPip
  • 23 posts

Posted 19 November 2013 - 06:59 PM

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 );
?>


#6 brandon66

brandon66

    Member

  • Members
  • PipPip
  • 23 posts

Posted 19 November 2013 - 07:37 PM

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 );
?>


#7 brandon66

brandon66

    Member

  • Members
  • PipPip
  • 23 posts

Posted 19 November 2013 - 08:16 PM   Best Answer

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);






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com