Datatable Server-Side error ( Invalid JSON Response ) - PHP

Question

Good afternoon, community! I’m cracking my head a little, trying to use Datatable Server-Side to solve my problem, first time I’m having contact with the same.

I’ll be dropping my code, and I’ll tell you the problems...

Script:

<script>
$(document).ready(function(){
   $('#tabela_server').DataTable({
      'processing': true,
      'serverSide': true,
      'serverMethod': 'post',
      'ajax': {
          'url':'../server.php'
      },
      'columns': [
         { data: 'nome' },
         { data: 'data_de_entrada' },
         { data: 'data_de_nascimento' },
         { data: 'convenio' },
         { data: 'data_saida' },
      ]
   });
});
</script>

php server.:

<?php
$server = "exemplo";
$username = "exemplo";
$password = "exemplo";
$dbname = "exemplo";
// Criando a conexão
try{
   $con = new PDO("mysql:host=$server;dbname=$dbname","$username","$password");
   $con->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
   die('Não foi possível conectar-se ao banco de dados');
}

## Ler valores
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Linhas exibidas por página
$columnIndex = $_POST['order'][0]['column']; // Index da coluna
$columnName = $_POST['columns'][$columnIndex]['data']; // Nome da coluna
$columnSortOrder = $_POST['order'][0]['dir']; // asc ou desc
$searchValue = $_POST['search']['value']; // Buscar valor

$searchArray = array();

## Buscar 
$searchQuery = " ";
if($searchValue != ''){
   $searchQuery = " AND (nome LIKE :nome or 
        data_de_entrada LIKE :data_de_entrada OR 
        data_de_nascimento LIKE :data_de_nascimento OR
        convenio LIKE :convenio OR
        data_saida LIKE :data_saida) ";
   $searchArray = array( 
        'nome'=>"%$searchValue%", 
        'data_de_entrada'=>"%$searchValue%",
        'data_de_nascimento'=>"%$searchValue%",
        'convenio'=>"%$searchValue%",
        'data_saida'=>"%$searchValue%"
   );
}

## Total de números de registros SEM filtros
$stmt = $con->prepare("SELECT COUNT(*) AS allcount FROM pacientes ");
$stmt->execute();
$records = $stmt->fetch();
$totalRecords = $records['allcount'];

## Total de números de registros COM filtros
$stmt = $con->prepare("SELECT COUNT(*) AS allcount FROM pacientes WHERE 1 ".$searchQuery);
$stmt->execute($searchArray);
$records = $stmt->fetch();
$totalRecordwithFilter = $records['allcount'];

## Buscar registros
$stmt = $con->prepare("SELECT * FROM pacientes WHERE 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset");

// Valores de ligação
foreach($searchArray as $key=>$search){
   $stmt->bindValue(':'.$key, $search,PDO::PARAM_STR);
}

$stmt->bindValue(':limit', (int)$row, PDO::PARAM_INT);
$stmt->bindValue(':offset', (int)$rowperpage, PDO::PARAM_INT);
$stmt->execute();
$empRecords = $stmt->fetchAll();

$data = array();

foreach($empRecords as $row){
   $data[] = array(
      "nome"=>$row['nome'],
      "data_de_entrada"=>$row['data_de_entrada'],
      "data_de_nascimento"=>$row['data_de_nascimento'],
      "convenio"=>$row['convenio'],
      "data_saida"=>$row['data_saida']
   );
}


## Response
$response = array(
   "draw" => intval($draw),
   "iTotalRecords" => $totalRecords,
   "iTotalDisplayRecords" => $totalRecordwithFilter,
   "aaData" => $data
);

echo json_encode($response);

ERROR: (Only on a few pages...)

Datatables Warning: table id=table_server - Invalid JSON Response. For more information about this error, Please see http://datatables.net/tn/1

Well, I’ve tried to find where the bug is by accessing http://datatables.net/tn/1, however I could not, I am a little layy in the subject, I will be leaving a print below shown what appears in the browser, in the pages that are with error...

Obs: Data is normally displayed in table, only it is not displayed some pages that are with this error...

Answer 1

I know it has been a while since the last update of this doubt, in this case in specific as the friend has already performed the test in analyzing the page in developer mode and did not get any feedback "This request has no data available" this means that your script that has been responsible for collecting the database data is not even able to convert the payload into json.

For example most scripts (PHP) that are next to the server end with something like:

echo json_encode($response);
exit;

This is the reason why you don’t have any answers in the browser, nothing appears to you because you didn’t even assemble the answer as JSON.

Usually this type of problem happens because working on the localhost we set charset UTF8 /uft8_unicode_ci for the database that is being tested and when we consume some database that is already on the server its default may be different from the localhost or vice-hostversa, the application will not be able to convert the query return to JSON because its contents are not converted to UTF8 and then will give this error - Invalid JSON Response

Do not need to change the default of your bank if it is different, just convert to UTF8, in the example below I will not delve into the variable $con this variable would be like the settings of your bank:

$query = "select * from TESTE"; 
$runQuery = mysqli_query($con, $query);
$data = array();

while ($row = mysqli_fetch_assoc($runQuery)) {

    $data[] = array(
            "NOME" => utf8_encode($row['NOME']),
            "DESCRICAO" => utf8_encode($row['DESCRICAO']),
            "ID" => $row['ID']
        );
}

$response = array(
    "draw" => intval($draw),
    "iTotalRecords" => $totalRecords,
    "iTotalDisplayRecords" => $totalRecordwithFilter,
    "aaData" => $data
);
            
echo json_encode($response);
exit;

This solution is not always the most viable, I’ve seen cases where accessing the browser dev mode you can get a clearer return of the problem, SQL errors for example, but in this case the problem was that nothing appeared in the console response, then it is very likely that never came to mount any answer because it failed to convert the data into JSON.