Server Side Processing using mysqli not getting json response

chirathh

I am using datatables without a problem, but some pages crash due to the large amount of data that it's fetching from the database. So I tried to implement server side processing. I followed the examples on the site, but I just don't seem to receive the json data sent from the php file. Can someone please tell me what's wrong? Ripping my hair out here I'm new to coding, please bare with me.
What I get on console is: jquery.min.js:5 POST http://site.com//datatables-script.php net::ERR_EMPTY_RESPONSE
Response at Network tab says: Failed to load response data
This is my ajax call:

$(document).ready(function(){
            $('#sort').dataTable({
                "ajax": {
                    url: "datatables-script.php",
                    type: "POST",
                    data: {storeid:'<?php echo $stid; ?>'},
                    //dataType: 'json',
                    success: function(gotback) { 
                        //var JSONArray = JSON.stringify(data);
                        //console.log(JSONArray);
                        console.log(gotback);
                    }   
                },
                "processing": true,
                "serverSide": true,
                "bServerSide": true,
            });

This is my script: Sorry for the long post.

<?php

mb_internal_encoding('UTF-8');
$aColumns = array( 'Rec_Id', 'Br_Id', 'C_Id', 'SubcId', 'ProdId', 'Prodme', 'URL', 'Im0', 'Price' );
  
$sIndexColumn = 'Rec_Id';
$sTable = 'tb';
 
$gaSql['user']     = 'ss';
$gaSql['password'] = 'sss';
$gaSql['db']       = 's';
$gaSql['server']   = 's';
$gaSql['port']     = 3306; // 3306 is the default MySQL port
 
// Input method (use $_GET, $_POST or $_REQUEST)
$input =& $_POST;
 
$gaSql['charset']  = 'utf8';

//get store id 
$storeid = $_POST['storeid'];
$db = new mysqli($gaSql['server'], $gaSql['user'], $gaSql['password'], $gaSql['db'], $gaSql['port']);

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 );
}
  
$sLimit = "";
if ( isset( $input['iDisplayStart'] ) && $input['iDisplayLength'] != '-1' ) {
    $sLimit = " LIMIT ".intval( $input['iDisplayStart'] ).", ".intval( $input['iDisplayLength'] );
}

$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 = "";
}

$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 sid=$storeid ".implode(" AND ", $aFilteringRules);
} else {
    $sWhere = "";

$aQueryColumns = array();
foreach ($aColumns as $col) {
    if ($col != ' ') {
        $aQueryColumns[] = $col;
    }
}
 
$sQuery = "
    SELECT SQL_CALC_FOUND_ROWS `".implode("`, `", $aQueryColumns)."`
    FROM `".$sTable."`".$sWhere.$sOrder.$sLimit;
//echo $sQuery;
$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 = 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 );

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

Your script appears to be using the legacy server-side processing parameters for some reason.

If you need DataTables to send your server-side script the legacy parameters, see the details in the manual here.

Allan

chirathh

Legacy parameters means that it's coming from an older version right? So do you reckon it's better to use the latest version than legacy?

allan

Yes I would have said so. The server-side script you show above uses the old parameters, while the DataTables configuration you showed above would be sending the new parameters.

I would suggest you update your server-side script to use the new parameters.

If that isn't an option to can tell DataTables to send the legacy parameters, per the documentation I linked to above.

Allan