Datatables serverside processing using mysqli json

Question

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?

What I get on console is: jquery.min.js:5 POST http://example.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 );

Answer 1

Check this example, I am using the same in my project:

JS:

$('#datatable_location_details').dataTable({
    "sServerMethod": "GET",
    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "getdetails.php,
    // "aoColumns": [null, null, null, null, { "bSortable": true, "sClass": "align_center" }, { "bSortable": true, "sClass": "align_center" }, { "bSortable": false, "sClass": "align_center" }]
});

Php:

$start  = $_REQUEST['iDisplayStart'];
$length = $_REQUEST['iDisplayLength'];
$col = $_REQUEST['iSortCol_0'];

$arr = array(0 => 'state', 1 => 'city', 2 => 'institute_name', 3 => 'address', 4 => 'student_count', 5 => 'ranking');

$sort_by = $arr[$col];
$sort_type = $_REQUEST['sSortDir_0'];

$qry = "select id, institute_name, address, state, city, student_count, ranking, latitude, longitude from location_details where (state LIKE '%".$state."%' and city LIKE '%".$city."%' and institute_name LIKE '%".$name."%' and address LIKE '%".$address."%') and (address != '' and state != '' and city != '') ORDER BY ".$sort_by." ".$sort_type." LIMIT ".$start.", ".$length;

$res = mysqli_query($con, $qry);
while($row = mysqli_fetch_assoc($res))
{
    $data[] = $row;
}

$qry = "select count(id) as count from location_details";
$res = mysqli_query($con, $qry);

while($row =  mysqli_fetch_assoc($res))
{
    $iTotal = $row['count'];
}

$rec = array(
    'iTotalRecords' => $iTotal,
    'iTotalDisplayRecords' => $iTotal,
    'aaData' => array()
);

$k=0;
if (isset($data) && is_array($data))
{
    foreach ($data as $item)
    {
        $action = 'Show Map';
        $tooltip= 'Show Map';
        $class  = 'show_details';
        if($item['latitude'] == '' && $item['longitude'] == '')
        {
            $action = 'Modify Address';
            $tooltip= 'Latitude & Longitude are not available for this address';
            $class  = 'modify_address';
        }
        $rec['aaData'][$k] = array(
            0 => $item['state'],
            1 => $item['city'],
            2 => htmlspecialchars($item['institute_name'], ENT_QUOTES),
            3 => htmlspecialchars($item['address'], ENT_QUOTES),
            4 => $item['student_count'],
            5 => $item['ranking'],
            6 => '<a href="javascript:void(0);" title="'.$tooltip.'" class="'.$class.'" id="'.$item['id'].'">'.$action.'</a>',
        );
        $k++;
    }
}

echo json_encode($rec);

For more info check this Github Project