DataTables warning: table id=example - Invalid JSON response

liangtp

Hi,

My datatable can retrieve and display data.

However when I key in a search string, I get this error "DataTables warning: table id=example - Invalid JSON response"

I suspect the returned number of records is different from the anticipated number of records. How to overcome this?

Thanks.

My client and server script codes is as follow:

//javascript client code
$('#example').dataTable( {
"processing": true,
"serverSide": true,
"ajax": {
"url": "myCommonDL.php",
"data": data
},
"columns": [
{ "data": "Subject" },
{ "data": "Code" }
]
} );

//PHP server-side myCommonDL.php
function getRequestJob(){

if (isset($_GET['start'])) $start = sanitizeString($_GET['start']);
if (isset($_GET['length'])) $length = sanitizeString($_GET['length']);
if (isset($_GET['draw'])) $draw = sanitizeString($_GET['draw']);
if (isset($_GET['order'])) $aOrder = $_GET['order'];
if (isset($_GET['columns'])) $aCols = $_GET['columns'];
if (isset($_GET['search'])) $search = $_GET['search'];

/** Ordering **/
$sOrder = "";
if ( isset( $aOrder ) ) 
{
    $sOrder = "ORDER BY  ";
    for ( $i=0 ; $i<count($aOrder) ; $i++ )
    {
        if ( $aCols[intval($aOrder[$i]['column'])]['orderable'] == "true" )
        {
            $sOrder .= $aCols[intval($aOrder[$i]['column'])]['data']." ".($aOrder[$i]['dir']=='asc' ? 'ASC' : 'DESC') .", ";
        }
    }
    $sOrder = substr_replace( $sOrder, "", -2 );
    if ( $sOrder == "ORDER BY" )
    {
        $sOrder = "";
    }
}

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

/** Filtering **/    
$sWhere = "";
if ( isset($search) && $search['value']!=="" )
{       
    $sWhere = "WHERE (";
    for ( $i=0 ; $i<count($aCols) ; $i++ )
    {
        if ( isset($aCols[intval($aOrder[$i]['column'])]) && $aCols[intval($aOrder[$i]['column'])]['searchable'] == "true" )
        {
            $sWhere .= $aCols[intval($aOrder[$i]['column'])]['data']." LIKE '%".mysql_real_escape_string( $search['value'] )."%' OR ";
        }
    }
    $sWhere = substr_replace( $sWhere, "", -3 );
    $sWhere .= ')';
}


$query_total="\n"     
             . "SELECT COUNT(*) AS TotalRec  \n"
             . "FROM `tblSubject` \n"
             . $sWhere; 
$result =  mysqli_query($mysqli, $query_total);
$r = mysqli_fetch_array($result, MYSQLI_ASSOC);
$recordsTotal = $r['TotalRec'];

$query_basic = "\n"     
             . "SELECT A.`Subject` AS `Subject`, \n"
             . "A.`Code` AS `Code` \n"
             . "FROM `tblSubject` AS A \n"
             . $sWhere
             . $sOrder 
             . $sLimit;
$result =  mysqli_query($mysqli, $query_basic);
while ($r = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    $recordsFiltered += 1; 
    $Rows[] =$r;
}

$returnData = array(
                        'draw' =>intval($draw),
                        'recordsTotal' => $recordsTotal,
                        'recordsFiltered' => $recordsTotal,
                        'data' => $Rows
                     );
return $returnData;

}

liangtp

Problem solved.

Error:
for ( $i=0 ; $i<count($aCols) ; $i++ )

Solution:
for ( $i=0 ; $i<count($aCols)-1 ; $i++ )