Datatable Server Side does no paging, sorting, or searching

Question

beauty? So, I am passing the processing of my datatable to serverside, due to extreme slowness with high data volume. The data is being returned via json Find perfectly, however, paging, sorting and searching the datatable are not working. Could someone help me? Follow codes below.

Code Html + Jquery

<table id="table" class="display" cellspacing="0" width="100%">
  <thead>
    <tr>
      <th>Id Empresa</th>
      <th>Cidade</th>
      <th>Estado</th>
    </tr>
  </thead>
  <tbody>

  </tbody>
</table> 

<script>
$(document).ready(function() {
    $('#table').dataTable( {
        "bProcessing": true,
        "bServerSide": true,
        "sAjaxSource": "table_server_processing.php"
    } );
} );
</script>

PHP code of the table_server_processing.php page

<?php
/**
 * Script:    DataTables server-side script for PHP 5.2+ and MySQL 4.1+
* Notes:     Based on a script by Allan Jardine that used the old PHP mysql_* functions.
*            Rewritten to use the newer object oriented mysqli extension.
* Copyright: 2010 - Allan Jardine (original script)
*            2012 - Kari Söderholm, aka Haprog (updates)
* License:   GPL v2 or BSD (3-point)
*/

mb_internal_encoding('UTF-8');

/**
 * 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( 'id_empresa', 'cidade', 'estado' );

// Indexed column (used for fast and accurate table cardinality)

$sIndexColumn = "id_empresa";

/* DB table to use */
$sTable = "cadastro_fornec";

/* Database connection information */
$gaSql ['user'] = "connect@cubo";
$gaSql ['password'] = "4c3sso4lm0xcub0c0nn3ct";
$gaSql ['db'] = "voucher";
$gaSql ['server'] = "192.168.20.16";

// Input method (use $_GET, $_POST or $_REQUEST)

$input =& $_GET;

/** * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP server-side, there is
 * no need to edit below this line
 */

/**
 * Character set to use for the MySQL connection.
 * MySQL will return all strings in this charset to PHP (if the data is stored correctly in the database).
 */
$gaSql['charset']  = 'utf8';

/**
 * MySQL connection
 */

$db = new mysqli($gaSql['server'], $gaSql['user'], $gaSql['password'], $gaSql['db']);
if (mysqli_connect_error()) {
    die( 'Error connecting to MySQL server (' . mysqli_connect_errno() .') '. mysqli_connect_error() );
}

if (!$db->set_charset($gaSql['charset'])) {
    die( 'Error loading character set "'.$gaSql['charset'].'": '.$db->error );
}


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


/**
 * Ordering
 */
$aOrderingRules = array();
if ( isset( $input['iSortCol_0'] ) ) {
    $iSortingCols = intval( $input['iSortingCols'] );
    for ( $i=0 ; $i<$iSortingCols ; $i++ ) {
        if ( $input[ 'bSortable_'.intval($input['iSortCol_'.$i]) ] == 'true' ) {
            $aOrderingRules[] =
            "`".$aColumns[ intval( $input['iSortCol_'.$i] ) ]."` "
                    .($input['sSortDir_'.$i]==='asc' ? 'asc' : 'desc');
        }
    }
}

if (!empty($aOrderingRules)) {
    $sOrder = " ORDER BY ".implode(", ", $aOrderingRules);
} else {
    $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
 */
$iColumnCount = count($aColumns);

if ( isset($input['sSearch']) && $input['sSearch'] != "" ) {
    $aFilteringRules = array();
    for ( $i=0 ; $i<$iColumnCount ; $i++ ) {
        if ( isset($input['bSearchable_'.$i]) && $input['bSearchable_'.$i] == 'true' ) {
            $aFilteringRules[] = "`".$aColumns[$i]."` LIKE '%".$db->real_escape_string( $input['sSearch'] )."%'";
        }
    }
    if (!empty($aFilteringRules)) {
        $aFilteringRules = array('('.implode(" OR ", $aFilteringRules).')');
    }
}

// Individual column filtering

for ( $i=0 ; $i<$iColumnCount ; $i++ ) {
    if ( isset($input['bSearchable_'.$i]) && $input['bSearchable_'.$i] == 'true' && $input['sSearch_'.$i] != '' ) {
        $aFilteringRules[] = "`".$aColumns[$i]."` LIKE '%".$db->real_escape_string($input['sSearch_'.$i])."%'";
    }
}

if (!empty($aFilteringRules)) {
    $sWhere = " WHERE ".implode(" AND ", $aFilteringRules);
} else {
    $sWhere = "";
}

/**
 * SQL queries
 * Get data to display
 */
$aQueryColumns = array();
foreach ($aColumns as $col) {
    if ($col != ' ') {
        $aQueryColumns[] = $col;
    }
}

$sQuery = "
    SELECT SQL_CALC_FOUND_ROWS " . str_replace ( " , ", " ", implode ( ", ", $aColumns ) ) . "
    FROM `".$sTable;

$rResult = $db->query( $sQuery ) or die($db->error);

// Data set length after filtering

$sQuery = "SELECT FOUND_ROWS()";
$rResultFilterTotal = $db->query( $sQuery ) or die($db->error);
list($iFilteredTotal) = $rResultFilterTotal->fetch_row();

// Total data set length

$sQuery = "SELECT COUNT(`".$sIndexColumn."`) FROM `".$sTable."`";
$rResultTotal = $db->query( $sQuery ) or die($db->error);
list($iTotal) = $rResultTotal->fetch_row();


/**
 * Output
 */
$output = array(
        "sEcho"                => intval($input['sEcho']),
        "iTotalRecords"        => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData"               => array(),
);

while ( $aRow = $rResult->fetch_assoc() ) {
    $row = array();
    for ( $i=0 ; $i<$iColumnCount ; $i++ ) {
        if ( $aColumns[$i] == 'version' ) {
            // Special output formatting for 'version' column
            $row[] = ($aRow[ $aColumns[$i] ]=='0') ? '-' : $aRow[ $aColumns[$i] ];
        } elseif ( $aColumns[$i] != ' ' ) {
            // General output
            $row[] = $aRow[ $aColumns[$i] ];
        }
    }
    $output['aaData'][] = $row;
}

echo json_encode($output);

Answer 1

You need to use in your main query the parts of SQL that the script mounts and that will effectively do their proper functions, something like:

$sQuery = "
    SELECT SQL_CALC_FOUND_ROWS " . str_replace ( " , ", " ", implode ( ", ", $aColumns ) ) . "
    FROM `".$sTable . $sWhere . $sOrder . $sLimit;