Problem sorting Mysql results in Datatables

Question

I am following a tutorial of Datatables, and to sort the results with his options the tutorial sends insert the following in the query to be made:

$sql1.=" ORDER BY ".$columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." , ".$requestData['length']." ";

But this query doesn’t run at all, any idea what I might be doing wrong?

For context, I’m following This tutorial, and my full code is as follows:

    <?php
include "includes/conecta_mysql.inc";

$requestData= $_REQUEST;

$columns = array(
    array( '0' => 'Matricula'),
    array( '1' => 'Data'),
    array( '2' => 'Origem'),
    array( '3' => 'Destino'),
    array( '4' => 'hac'),
    array( '5' => 'hdec'),
    array( '6' => 'hpou'),
    array( '7' => 'hcorte'),
    array( '8' => 'ttotal'),
    array( '9' => 'pousos'),
    array( '10' => 'pic'),
    array( '11' => 'sic'),
);

//Resultados sem filtro
$sql = "SELECT * FROM diario";
$resultado = mysqli_query($con, $sql);
$qnt_linhas = mysqli_num_rows($resultado);

//Resultados filtrados
$sql1  = "SELECT matricula, data, origem, destino, hac, hdec, hpou, hcorte, ttotal, pousos, pic, sic FROM diario ORDER BY data desc";
if( !empty($requestData['search']['value']) ) {   // se houver um parâmetro de pesquisa, $requestData['search']['value'] contém o parâmetro de pesquisa
    $sql1.=" AND ( nome LIKE '".$requestData['search']['value']."%' ";    
    $sql1.=" OR salario LIKE '".$requestData['search']['value']."%' ";
    $sql1.=" OR idade LIKE '".$requestData['search']['value']."%' )";
}

$resultado1 = mysqli_query($con, $sql1);

$qnt_filtrado = mysqli_num_rows($resultado1);

//Ordena
$sql1.=" ORDER BY ". $columns[$requestData['order'][0]]."   ".$requestData['order'][0]." LIMIT ".$requestData['start']." ,".$requestData['length']."   ";
$resultado1 = mysqli_query($con, $sql1);



//Cria array
$dados = array();
while ($row = mysqli_fetch_array($resultado1)){
    $dado = array();
    $dado[] = $row["matricula"];
    $dado[] = $row["data"];
    $dado[] = $row["origem"];
    $dado[] = $row["destino"];
    $dado[] = $row["hac"];
    $dado[] = $row["hdec"];
    $dado[] = $row["hpou"];
    $dado[] = $row["hcorte"];
    $dado[] = $row["ttotal"];
    $dado[] = $row["pousos"];
    $dado[] = $row["pic"];
    $dado[] = $row["sic"];

    $dados[] = $dado;
}

//Cria array a retornar pro JS
$json_data = array(
    "draw" => intval($requestData['draw']),
    "recordsTotal" =>intval($qnt_linhas),
    "recordsFiltered" =>intval($qnt_filtrado),
    "data" => $dados
);

echo json_encode($json_data);


?>

Answer 1

You need to see the Ajax you’re going through.

I’ll give you an example that works:

$query = "SELECT    a.idcharge,
                                a.cha_tag,
                                a.cha_date_entry,
                                a.cha_obs,
                                b.mod_name,
                                c.bra_name,
                                d.typ_vehicle_name,
                                IFNULL(e.mon_name, 'AVULSO') as monthly

                                FROM ".$this->mysql['prefix']."tbl_charge AS a
                                JOIN ".$this->mysql['prefix']."tbl_model b ON b.idmodel = a.id_model
                                JOIN ".$this->mysql['prefix']."tbl_brand c ON c.idbrand = a.id_brand
                                JOIN ".$this->mysql['prefix']."tbl_type_vehicle d ON d.idtypevehicle = a.id_type_vehicle 
                                LEFT JOIN ".$this->mysql['prefix']."tbl_monthly e on e.idmonthly = a.id_monthly
                                WHERE cha_date_out IS NULL AND flg_canceled IS FALSE";
        if (!empty($request['search']['value'])) {
            $query.=" AND (a.idcharge Like '".$request['search']['value']."%'";
            $query.=" OR a.cha_tag Like '".   $request['search']['value']."%' ";
            $query.=" OR a.cha_date_entry Like '".$request['search']['value']."%'";
            $query.=" OR a.cha_obs Like '".$request['search']['value']."%'";
            $query.=" OR b.mod_name Like '".$request['search']['value']."%'";
            $query.=" OR c.bra_name Like '".$request['search']['value']."%'";
            $query.=" OR d.typ_vehicle_name Like '".$request['search']['value']."%'";
            $query.=" OR mon_name Like '".$request['search']['value']."%')";
        }
        $col = array(
            0   =>  'idcharge',
            1   =>  'cha_tag',
            2   =>  'cha_date_entry',
            3   =>  'cha_obs',
            4   =>  'mod_name',
            5   =>  'bra_name',
            6   =>  'typ_vehicle_name',
            7   =>  'monthly',
        );  //create column like table in database
        //Order
        $query.=" ORDER BY ".$col[$request['order'][0]['column']]."   ".$request['order'][0]['dir']."  LIMIT ".
            $request['start']."  ,".$request['length']."  ";
        return $this->sql->select($query);

Post

<!-- page script -->
<script>
    $(document).ready(function() {
        $('#example').DataTable({
            "columnDefs":[ {
                "className": "dt-center", "targets": "_all"
            }],
            "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"
                }
            },
            "processing": true,
            "serverSide": true,
            "ajax": {
                "url": "/vehicles/out/data",
                "type": 'POST',
            },
        });
    });
</script>