Server Side Processing Invalid JSON response when searching

chrisjai32

My datatable displayed records when i applied the server side processing but whenever i type something to the search bar it will show "table id = example - invalid JSON response". I'm kinda stuck.

Datatable Debugger: http://debug.datatables.net/ezupem

and Here's my server side processing.php

<?php
/* Database connection start */
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";

$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());

/* Database connection end */


// storing  request (ie, get/post) global array to a variable
$requestData= $_REQUEST;


$columns = array(
// datatable column index  => database column name
 
    0 => 'CandidateCode',
    1 => 'Fname',
    2 => 'Lname',
    3 => 'Mobile1',
    4 => 'Mobile2',
    5 => 'Landline',
    6 => 'EmailAddress',
    7 => 'EmailAddress2',
    8 => 'Portal',
    9 => 'LinkedIn',
    10 => 'primary_skill',
    11=> 'YearStarted',
    12=> 'AppForm',
    13=> 'CreatedBy',
    14 => 'DateCreated',


);




// getting total number records without any search
$sql = "SELECT CandidateCode";
$sql.=" FROM candidate_view";
$query=mysqli_query($conn, $sql) or die("server_processing.php: get candidate_view");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.


$sql = "SELECT CandidateCode,Fname,Lname,Mobile1,Mobile2,Landline,EmailAddress,EmailAddress2,Portal,LinkedIn,primary_skill,YearStarted,AppForm,CreatedBy,DateCreated from candidate_view" ;
$sql.="";
if( !empty($requestData['search']['value']) ) {   // if there is a search parameter, $requestData['search']['value'] contains search parameter
    $sql.=" AND ( CandidateCode LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR Fname LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR Lname LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR Mobile1 LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR Mobile2 LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR Landline LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR EmailAddress LIKE '".$requestData['search']['value']."%' )";
    $sql.=" OR EmailAddress2 LIKE '".$requestData['search']['value']."%' )";
    $sql.=" OR Portal LIKE '".$requestData['search']['value']."%' )";
    $sql.=" OR LinkedIn LIKE '".$requestData['search']['value']."%' )";
    $sql.=" OR primary_skill LIKE '".$requestData['search']['value']."%' )";
    $sql.=" OR YearStarted LIKE '".$requestData['search']['value']."%' )";
    $sql.=" OR AppForm LIKE '".$requestData['search']['value']."%' )";
    $sql.=" OR CreatedBy LIKE '".$requestData['search']['value']."%' )";
    $sql.=" OR DateCreated LIKE '".$requestData['search']['value']."%' )";

}
$query=mysqli_query($conn, $sql) or die("server_processing.php: get candidate_view");
$totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result.
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."  LIMIT ".$requestData['start']." ,".$requestData['length']."   ";
/* $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc  */
$query=mysqli_query($conn, $sql) or die("server_processing.php: get candidate_view");


$data = array();
while( $row=mysqli_fetch_array($query) ) {  // preparing an array
    $nestedData=array();
    $nestedData[] = "<a href='new_position.php?CandidateCode=".$row['CandidateCode']."'><img src='../img/assign.png' style='width: 20px; height: 20px;' title='Assign to a Position'/></a><a href='editcandidate.php?CandidateCode=".$row['CandidateCode']."'><img src='../img/edit.png' style='width: 20px; height:20px;' title='Edit'/> <a onclick='return confirm(\"Are you sure you want to delete this data ?\");'  href='deletecandidate.php?CandidateCode=" . $row['CandidateCode'] .  "'><img src='../img/delete.png' style='width: 20px; height: 20px; ' title='Delete'/></a>";
    $nestedData[] = $row["CandidateCode"];
    $nestedData[] = $row["Fname"];
    $nestedData[] = $row["Lname"];
    $nestedData[] = $row["Mobile1"];
    $nestedData[] = $row["Mobile2"];
    $nestedData[] = $row["Landline"];
    $nestedData[] = $row["EmailAddress"];
    $nestedData[] = $row["EmailAddress2"];
    $nestedData[] = $row["Portal"];
    $nestedData[] = $row["LinkedIn"];
    $nestedData[] = $row["primary_skill"];
    $nestedData[] = $row["YearStarted"];
    $nestedData[] = $row["AppForm"];
    $nestedData[] = $row["CreatedBy"];
    $nestedData[] = $row["DateCreated"];



    $data[] = $nestedData;
}

$json_data = array(
    "draw"            => 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.
    "recordsTotal"    => intval( $totalData ),  // total number of records
    "recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
    "data"            => $data   // total data array
);

echo json_encode($json_data);  // send data as json format

Thank youuu

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

chrisjai32

CLOSED. Decided to leave the previous code and found a better one by Allan Jardine. Thanks

allan

I know you've moved on, but at a guess I would say that there is a decent chance of there being a UTF8 BOM in the return from the server which would make it invalid JSON. The data shown by the debugger does look valid, but it wouldn't pick up a BOM unfortunately. So if anyone else comes across this and you have valid JSON, check for the hidden UTF8 BOM characters.

Allan