Problem for popular Datatable?

0

I can not find the error , just do not populate the table:

ERROR

  

DataTables warning: table id = TabEvolucao - Invalid JSON response. For more information about this error, please see link

AJAX

$('#TabEvolucao').DataTable({
    "processing" : true,        
    "ajax" : {
        "type" : "POST",
        "url" : "estrutura/tabevolucao.php",
        dataSrc : '',
        "data" : function(d){d.idcliente = 127;}
    },
    "columns" : [   {"data" : "id"}, 
                    {"data" : "data"}, 
                    {"data" : "descricao"},
                    {"data" : "nome"},
                    {"data" : "ativo"}
                ],      
    "aaSorting": [[1,'asc']],
     "iDisplayLength": 7,
     "bFilter": true,
     "aaSorting": [[1,'asc']],              
    "language": {               
        "sEmptyTable": "Nenhum registro encontrado",
        "sInfo": "Mostrando de _START_ até _END_ de _TOTAL_ registros",
        "sInfoEmpty": "Mostrando 0 até 0 de 0 registros",
        "sInfoFiltered": "(Filtrados de _MAX_ registros)",
        "sInfoPostFix": "",
        "sInfoThousands": ".",
        "sLengthMenu": "_MENU_ resultados por página ",
        "sLoadingRecords": "Carregando...",
        "sProcessing": "Processando...",
        "sZeroRecords": "Nenhum registro encontrado",
        "sSearch": "Pesquisar",
        "oPaginate": {
            "sNext": "Próximo",
            "sPrevious": "Anterior",
            "sFirst": "Primeiro",
            "sLast": "Último"
            },
        "oAria": {
            "sSortAscending": ": Ordenar colunas de forma ascendente",
            "sSortDescending": ": Ordenar colunas de forma descendente"
            }
        }   

});

PHP

    <?php include("../includes/config.php");

if ($_POST)
{
    $id = (int) $_POST['idcliente'];

    if ($_POST['acao'] == 'select' && is_int($id))
    {
        $select = "select E.id,E.data,E.descricao,U.nome,E.ativo FROM evolucao as E ";
        $select .= "inner join usuarios as U on (U.id=E.id_usuario) WHERE E.id = ".$id;
        $rs = $con->prepare($select);           
        if($rs->execute())
        {
            if($rs->rowCount() > 0)
            {
                $data = array();
                while($row = $rs->fetch(PDO::FETCH_OBJ))
                {
                    $data[] = array('success' => '1',
                                    'id' => $row->id,
                                    'data' => $row->data,
                                    'descricao' => $row->descricao,
                                    'nome' => $row->nome,
                                    'ativo' => $row->ativo,
                                    'msg' => 'Tudo certo!'
                                );
                }
            }
        }
    }
}
else
{
    $data = array('success' => '0', 'msg' => 'Ocorreu um erro, não achamos nada!');
}    
echo json_encode($data);    

ERROR

DataTables warning: table id=TabEvolucao - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1
    
asked by anonymous 24.02.2018 / 14:29

1 answer

0

You have problems with the return of your code, in the variable $data is not returning a array only the last item:

<?php include("../includes/config.php");

if ($_POST)
{
    $id = (int) $_POST['idcliente'];

    if ($_POST['acao'] == 'select' && is_int($id))
    {
        $select = "select E.id,E.data,E.descricao,U.nome,E.ativo FROM evolucao as E ";
        $select .= "inner join usuarios as U on (U.id=E.id_usuario) WHERE E.id = ".$id;
        $rs = $con->prepare($select);           
        if($rs->execute())
        {
            if($rs->rowCount() > 0)
            {
                $data = array();
                while($row = $rs->fetch(PDO::FETCH_OBJ))
                {
                    $data[] = array('success' => '1',
                                    'id' => $row->id,
                                    'data' => $row->data,
                                    'descricao' => $row->descricao,
                                    'nome' => $row->nome,
                                    'ativo' => $row->ativo,
                                    'msg' => 'Tudo certo!'
                                );
                }
            }
        }
    }
}
else
{
    $data = array('success' => '0', 'msg' => 'Ocorreu um erro, não achamos nada!');
}    
echo json_encode($data);    

Basically the last line echo json_encode($data); has to return a array in the following structure:

[
    {
        "success":'1',
        "id": 1,
        "data": "1999-01-01", ...
    },
    {
        "success":'1',
        "id": 2,
        "data": "1999-01-05", ...
    }
]

For DataTables to load the data, a gold problem also found is in javascript where you reformat the data, unnecessarily and also the data must come ready from PHP , then remove that function from dataSrc that in PHP has already been sent the data correctly:

$('#TabEvolucao').DataTable({
        "processing" : true,        
        "ajax" : {
            "type" : "POST",
            "url" : "data.php",
            dataSrc : ''
        },
        "columns" : [   {"data" : "id"}, 
                        {"data" : "data"}, 
                        {"data" : "descricao"},
                        {"data" : "nome"},
                        {"data" : "ativo"}
                    ],      
        "aaSorting": [[1,'asc']],
         "iDisplayLength": 7,
         "bFilter": true,
         "aaSorting": [[1,'asc']],              
        "language": {               
            "sEmptyTable": "Nenhum registro encontrado",
            "sInfo": "Mostrando de _START_ até _END_ de _TOTAL_ registros",
            "sInfoEmpty": "Mostrando 0 até 0 de 0 registros",
            "sInfoFiltered": "(Filtrados de _MAX_ registros)",
            "sInfoPostFix": "",
            "sInfoThousands": ".",
            "sLengthMenu": "_MENU_ resultados por página ",
            "sLoadingRecords": "Carregando...",
            "sProcessing": "Processando...",
            "sZeroRecords": "Nenhum registro encontrado",
            "sSearch": "Pesquisar",
            "oPaginate": {
                "sNext": "Próximo",
                "sPrevious": "Anterior",
                "sFirst": "Primeiro",
                "sLast": "Último"
                },
            "oAria": {
                "sSortAscending": ": Ordenar colunas de forma ascendente",
                "sSortDescending": ": Ordenar colunas de forma descendente"
                }
            }   

    });

I have in mind that they are often silly errors, so I will propose a minimal example with a fixed data without ajax so you have an idea of how it works:

$('#TabEvolucao').DataTable({
  "processing": true,
  "data": [{"id":1,"descricao":"Tiger Nixon","position":"System Architect","salary":"$320,800","data":"2011\/04\/25","nome":"Edinburgh","extn":"5421","ativo":1},{"id":2,"descricao":"Nixon","position":"Architect","salary":"$450,800","data":"2012\/12\/3","nome":"Edinburgh Peter","extn":"0151","ativo":0}],
  "columns": [{
      "data": "id"
    },
    {
      "data": "data"
    },
    {
      "data": "descricao"
    },
    {
      "data": "nome"
    },
    {
      "data": "ativo"
    }
  ],
  "aaSorting": [
    [1, 'asc']
  ],
  "iDisplayLength": 7,
  "bFilter": true,
  "aaSorting": [
    [1, 'asc']
  ],
  "language": {
    "sEmptyTable": "Nenhum registro encontrado",
    "sInfo": "Mostrando de _START_ até _END_ de _TOTAL_ registros",
    "sInfoEmpty": "Mostrando 0 até 0 de 0 registros",
    "sInfoFiltered": "(Filtrados de _MAX_ registros)",
    "sInfoPostFix": "",
    "sInfoThousands": ".",
    "sLengthMenu": "_MENU_ resultados por página ",
    "sLoadingRecords": "Carregando...",
    "sProcessing": "Processando...",
    "sZeroRecords": "Nenhum registro encontrado",
    "sSearch": "Pesquisar",
    "oPaginate": {
      "sNext": "Próximo",
      "sPrevious": "Anterior",
      "sFirst": "Primeiro",
      "sLast": "Último"
    },
    "oAria": {
      "sSortAscending": ": Ordenar colunas de forma ascendente",
      "sSortDescending": ": Ordenar colunas de forma descendente"
    }
  }

});
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script><scriptsrc="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>

<table id="TabEvolucao"></table>

As described in documentation to pass some additional layering to filters in server side is:

$(document).ready(function() {
    $('#example').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": "scripts/server_processing.php",
            "data": function ( d ) {
                d.myKey = "myValue";
                // d.custom = $('#myInput').val();
                // etc
            }
        }
    } );
} );

In your case, in the specific part of ajax :

$('#TabEvolucao').DataTable({
        "processing" : true,        
        "ajax" : {
            "type" : "POST",
            "url" : "data.php",
            "dataSrc" : "",
            "data" : function(d){
                 d.idcliente = 1; // aqui passe o número desejado.
             }
        },
    
24.02.2018 / 15:12