Server side processing paging, sort and search not working

marco.canhoto

Hello!
I am using dataTables with processing via serverSide and after the data returned, paging, searching and sorting do not work. I've read several cases here on the forum of similar or equal problems, but I still haven't been able to solve mine. Could you look at my code, please?
Thank you very much in advance!

Javascript:

tabelaMae = $('#tabelaMae').DataTable( {
        processing: true,
        serverSide: true,
        ajax: {
            url: "../scr/baixaExamesRequisicao.php",
            type: 'POST',
            data:function(d) {
                d.dataInicial = dataInicial;
                d.dataFinal = dataFinal;
            }
        },
        columns: [
           { data: "codigocliente", name:"codigocliente"},
           { data: "codigounidade", name:"codigounidade" },
           { data: "cpf", name:"cpf" },
           { data: "nomefuncionario", name:"nomefuncionario" },
           { data: "natureza", name:"natureza" },
           { data: "dataemissao", name:"dataemissao" },
           { data: "nomeexame", name:"nomeexame" },
        ],
        
    });

PHP:

$codigoCredenciado = $_SESSION['u_cred'];
$unidadeCredenciado = $_SESSION['u_cred_loja'];
$dataInicial = $_POST['dataInicial'];
$dataFinal = $_POST['dataFinal'];

$sql = "with grupoImpressao as (select distinct grupo as idKit from  eol_fecha_exames_teste where 
cred = '" . 
$codigoCredenciado.$unidadeCredenciado . "' and data between '" . 
$dataInicial . "' and '" . $dataFinal . "' and sobreposto is null) 
select id, idKit, emp as codigocliente, uni as codigounidade, cpf, func_nome as nomefuncionario, natureza, 
data as dataemissao, exame as codigoexame, 
tm4_nomexa as nomeexame, comp as compareceu, exame_data as dataresultado, 
tnp_emiten as codigomedico, tnp_nome as nomemedico, 
aso_resultado as parecer, faturado, exportado as esocial from 
eol_fecha_exames_teste left join eol_tm40101 on tm4_exame = 
exame 
left join eol_tnp0101 on tnp_emiten = medico_crm or tnp_emiten = medico_id 
inner join grupoImpressao on grupo = idKit
order by emp, uni, cpf, data desc, idKit"; 

$listaResultados = $mdb2->query($sql);

if (!empty($listaResultados)) {
    while ($linha = $listaResultados->fetchrow(MDB2_FETCHMODE_ASSOC)) {
        
        $dadosExames[] = [
            "DT_RowId"=> "row_".strval($id),
           'codigocliente'         => $linha['codigocliente'],
            'codigounidade'     => $linha['codigounidade'],
            'cpf'                        => $linha['cpf'],
            'nomefuncionario'   => utf8_encode($linha['nomefuncionario']),
            'natureza'                => $linha['natureza'],
            'dataemissao'          => date('d/m/Y',strtotime($linha['dataemissao'])), 
            'nomeexame'           => utf8_encode($linha['nomeexame']),
          ];

       }

    $dados = [
        "draw"              => isset ( $_POST['draw'] ) ? intval( $_POST['draw'] ) : 0,
        "recordsTotal"      => intval( count($dadosExames) ),
        "recordsFiltered"   => intval( count($dadosExames) ),
        "data"              => $dadosExames
    ];

} else {
    echo json_encode(['status' => false]);
    die();
}

echo json_encode($dados);

allan

Your SQL statement has a fixed order by. So it can never change based on that the client-side is asking for.

The first question is - do you really need server-side processing? Do you have 50k+ rows? If you do, and you want ordering, then you'll need to take account of the order property that is sent by the client-side.

It loops like you'll need to do the same with filtering as well. I also don't see a limit in your SQL, so I think a lot of the problems you are having can be resolved, but just removing the serverSide option in your Javascript init for the table.

Allan

marco.canhoto

Thanks for the answer, Allan!
I read the server side manual carefully and noticed the errors you mentioned.
In addition, the amount of records at the moment does not require a server side, as you also mentioned.
I am now using client side. Thanks again!