Issue in column itens ordering

evillaca

Hi all,

I would like to ask your assistance in an issue to order column items by the correct column.
I am trying to do the following:

I have column A, B and C.
When I try to order items in column A nothing hapens.
Then I try to order items in column B, it orders items in column A.
When trying to order items in column C, it orders items in column B.

So we can see the functionality is skiping one column always changing the previuous one.

Would you be able to help me fixing that issue?

See front and back code bellow:

index.php

<script type="text/javascript">
    $(document).ready(function(){
        $('#add_button').click(function(){
            $('#course_form')[0].reset();
            $('.modal-title').text("Adicionar novo job");
            $('#action').val("Add");
            $('#operation').val("Add")
        });

//Selecionar Todos os registros
     var dataTable = $('#course_table').DataTable({
        "paging":true,
        "processing":true,
        "serverSide":true,
        "rowGroup": false,
        "order": [[2, "asc"]],
        "info":true,
        "ajax":{
            url:"fetch.php",
            type:"POST"
        },
        "dom": 'Bfrtip',
        "buttons": [
            'excel'
        ],
       /*  "columnDefs":
            [
            { "orderData": [ 2, 3 ],    "targets": 2 },
            ], */
        
     });

//Inserir novo registro
     $(document).on('submit', '#course_form', function(event){
        event.preventDefault();
        var id = $('#id').val();
        var date = $('#date').val();
        var horario = $('#horario').val();
        var tempo = $('#tempo').val();
        var projeto = $('#projeto').val();
        var palavras = $('#palavras').val();
        var comentario = $('#comentario').val();
        
        if(date != '' && projeto != '')
        {
            $.ajax({
                url:"insert.php",
                method:'POST',
                data:new FormData(this),
                contentType:false,
                processData:false,
                success:function(data)
                {
                    $('#course_form')[0].reset();
                    $('#userModal').modal('hide');
                    dataTable.ajax.reload();
                }
            });
        }
        else
        {
            alert("A data e o nome do projeto são obrigatórios");
        }
    });
    



    
</script>

<!--######################## Fim Carregar os campos da tabela ##########################################-->

fetch.php

<?php 
include('conexaodb.php');
include('functions.php');
$query = '';
$output = array();
$query .= "SELECT * FROM `tb_site.controlejobs`";
if(isset($_POST["search"]["value"]))
{
    $query .= 'WHERE id LIKE "%'.$_POST["search"]["value"].'%" ';
    $query .= 'OR date LIKE "%'.$_POST["search"]["value"].'%" ';
    $query .= 'OR horario LIKE "%'.$_POST["search"]["value"].'%" ';
    $query .= 'OR tempo LIKE "%'.$_POST["search"]["value"].'%" ';
    $query .= 'OR projeto LIKE "%'.$_POST["search"]["value"].'%" ';
    $query .= 'OR palavras LIKE "%'.$_POST["search"]["value"].'%" ';
    $query .= 'OR comentario LIKE "%'.$_POST["search"]["value"].'%" ';
}

if(isset($_POST["order"]))
{
    $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
    $query .= 'ORDER BY tempo ASC ';
}

if($_POST["length"] != -1)
{
    $query .= 'LIMIT ' .$_POST['start']. ', ' .$_POST['length'];
}
$statement = $connection->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$data = array();
$filtered_rows = $statement->rowCount();
foreach($result as $row)
{
    $sub_array = array();
    $sub_array[] = $row["id"];
    $sub_array[] = $row["date"];
    $sub_array[] = $row["horario"];
    $sub_array[] = $row["tempo"];
    $sub_array[] = $row["projeto"];
    $sub_array[] = $row["palavras"];
    $sub_array[] = $row["comentario"];
    $sub_array[] = '<button type="button" name="update" id="'.$row["id"].'" class="btn btn-primary btn-sm update">Editar</button>';
    $sub_array[] = '<button type="button" name="delete" id="'.$row["id"].'" class="btn btn-danger btn-sm delete">Deletar</button>';
    $sub_array[] = '<input class="form-check-input" type="checkbox" id="gridCheck1">';
    $data[] = $sub_array;
}
$output = array(
    "draw"              =>  intval($_POST["draw"]),
    "recordsTotal"      =>  $filtered_rows,
    "recordsFiltered"   =>  get_total_all_records(),
    "data"              =>  $data
);
echo json_encode($output);
?>

allan

You have server-side processing enabled, so ordering of the data is entirely down to your PHP script.

I’d start by debugging the SQL you are generating and make sure that it has the correct ORDER BY statement for what you expect.

Allan