Receiving DataTables Alert due to Json error, but Json Lint validates it

hanoncs

My datatable will not load data. I am receiving the correct data back in json, but DataTables alerts me that I have a json error. If I validate that json it comes back as valid. Any ideas?

Here is my debug #: ajesok

allan

In your debug trace click the "Tables" tab and then "Server interaction". That shows what is being sent back from the server and it is most certainly not valid JSON :-)

Allan

hanoncs

Wow, this is all I see in chrome dev tools network preview of my get request...

{"sEcho":1,"recordsTotal":2161,"recordsFiltered":2161,"aoData":[["3243-1"], ["3243-2"], ["10-3"], ["3243-4"], ["10-5"], ["3084-6"], ["3282-7"], ["3282-8"], ["3282-9"], ["3282-10"]]}

hanoncs

Thank you allan. I discovered that my Header was returning the html with the json. I can now fill the datatable. My new issue is that the sorting, paging and search is not working. I will post my code below. I found an old mssql file on here so I modified for sqlsrv and now I am having these issues. I didnt change much.

 $gaSql['user']       = "---";
$gaSql['password']   = "---";
$gaSql['db']         = "---";
$gaSql['server']     = "---";

$connectionInfo = array("UID" => $gaSql['user'], "PWD" => $gaSql['password'], "Database"=>$gaSql['db'],"ReturnDatesAsStrings"=>true);
$Conn = sqlsrv_connect( $gaSql['server'], $connectionInfo);

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "ID";
$requestData= $_REQUEST;
/* DB table to use */
$sTable = "equipment.dbo.ScoutPartsList";
$aColumns = array('PartNumber', 'PartName');

/* 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] ) ]."
                    ".addslashes( $_GET['sSortDir_'.$i] ) .", ";
        }
    }
    $sOrder = substr_replace( $sOrder, "", -2 );
    if ( $sOrder == "ORDER BY" ) {
        $sOrder = "";
    }
}

/* Filtering */
$sWhere = "";
if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
    $sWhere = "WHERE (";
    for ( $i=0 ; $i < count($aColumns) ; $i++ ) {
        $sWhere .= $aColumns[$i]." LIKE '%".addslashes( $_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 '%".addslashes($_GET['sSearch_'.$i])."%' ";
    }
}

/* Paging */
$top = (isset($_GET['iDisplayStart']))?((int)$_GET['iDisplayStart']):0 ;
$limit = (isset($_GET['iDisplayLength']))?((int)$_GET['iDisplayLength'] ):10;
$sQuery = "SELECT TOP ($limit) ".implode(",",$aColumns)."
        FROM $sTable
        $sWhere ".(($sWhere=="")?" WHERE ":" AND ")." $sIndexColumn NOT IN
        (
            SELECT $sIndexColumn FROM
            (
                SELECT TOP ($top) ".implode(",",$aColumns)."
                FROM $sTable
                $sWhere
                $sOrder
            )
            as [virtTable]
        )
        $sOrder";

$rResult = sqlsrv_query($Conn, $sQuery);

$sQueryCnt = "SELECT * FROM $sTable $sWhere";
$rResultCnt = sqlsrv_query($Conn , $sQueryCnt , array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
$iFilteredTotal = sqlsrv_num_rows( $rResultCnt );

$sQuery = " SELECT * FROM $sTable ";
$rResultTotal = sqlsrv_query($Conn, $sQuery, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
$iTotal = sqlsrv_num_rows( $rResultTotal );

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

$output = array(
            "sEcho"            => intval( $requestData['draw'] ),   // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw. 
            "iTotalRecords"    => $iTotal,  // total number of records
            "iTotalDisplayRecords" => $iFilteredTotal, // total number of records after searching, if there is no searching then totalFiltered = totalData
            "data"            => array()   // total data array
            );


while ( $aRow = sqlsrv_fetch_array($rResult, SQLSRV_FETCH_BOTH ) ) {
    $row = array();
    for ( $i=0 ; $i < count($aColumns) ; $i++ ) {
        if ( $aColumns[$i] != ' ' ) {
            $v = $aRow[ $aColumns[$i] ];
            $v = mb_check_encoding($v, 'UTF-8') ? $v : utf8_encode($v);
            $row[]=$v;
        }
    }
    If (!empty($row)) { $output['data'][] = $row; }
}   
echo json_encode( $output );

allan

Are you using a legacy version of DataTables, or have you enabled the legacy mode in 1.10 as that is certainly a legacy script.

Allan

hanoncs

That is a script I fount on here. I'm using a MS SQL Database, and I thought it would be better to use that script instead of trying to modify ssp.class.php to work with sqlsrv. Do you have any recommendations for anyone using sqlsrv for datatables? Or a link to read more on enabling legacy mode.

Thank you sir.

allan

Or a link to read more on enabling legacy mode.

It is in the manual.

The other option is to update the above script to work with the 1.10 parameters - also described on the linked page.

Allan

hanoncs

Thank you sir i will give it a try.

radcorp

thanks Allan :D