Sort DateTime of a DataTable

0

I am having problems sorting the DateTime field of a DataTable, I am returning records from my users table via server-side. The problem is when it is time to click on the label of a column of type DateTime, the data is ordered as if it were String and not as DateTime. Below the sample image:

index.php

<!DOCTYPEhtml><html><head><metacharset="UTF-8">
    <title>Usuários</title>
</head>

<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet"/>
<link href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" rel="stylesheet"/>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script><scriptsrc="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script><scriptsrc="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>

<body>
    <h2>Usuários</h2>
    <table id="server-side" class="table table-striped table-bordered table-hover" width="100%" cellspacing="0">
        <thead>
            <tr>            
                <th>id.</th>
                <th>Cadastro.</th>
                <th>Nome</th>
                <th>Sobrenome</th>
                <th>Nível</th>
                <th>Opções</th>
            </tr>
        </thead>
    </table>

    <script>
    $(document).ready(function(e){
        $('#server-side').dataTable({
            "bProcessing": true,
            "serverSide": true,         
            "aoColumnDefs": [    
            {
                "bSearchable": false,
                "bVisible": false,
        "aTargets": [0]// aqui é a coluna oculta de 'ID

    },
    {
       "aTargets": [5], // o numero 5 é o nº da coluna de AÇÕES/OPÇÕES
       "mRender": function ( data, type, full ) { //aqui é uma funçãozinha para pegar os ids
        return '<a href="view.php?id=' + full[0] + '"  class="btn btn-sm btn-success"><i class="fa fa-eye"></i> Vizualizar</a> '+
        '<a href="edit.php?id=' + full[0] + '"  class="btn btn-sm btn-success"><i class="fa fa-pencil"></i> Editar</a> '+
        '<a href="#" class="btn btn-sm btn-success" data-toggle="modal" data-target="#delete-modal" data-usuario="' + full[0] + '"><i class="fa fa-trash"></i> Deletar</a>';
       }
   }
   ],

   language: {
    processing: "Processando...",
   },
   "ajax":{
    url :"server-side.php",
    type: "POST",
    error: function(){
        $("#post_list_processing").css("display","none");
    }
   },
    //ordenando a coluna
    "order": [ 1, "desc"],
});
});
</script>

</body>
</html>

server-side.php

<?php
// definições de host, database, usuário e senha
$host = 'localhost';
$db   = 'banco';
$user = 'root';
$pass = '';

// conecta ao banco de dados
$con = mysqli_connect($host, $user, $pass) or trigger_error(mysql_error(),E_USER_ERROR); 
$con->set_charset("utf8");
// seleciona a base de dados em que vamos trabalhar
mysqli_select_db($con,$db);

$params = $columns = $totalRecords = $data = array();

$params = $_REQUEST;

$columns = array(
    0 => 'id',
    1 => 'cadastro',
    2 => 'nome', 
    3 => 'sobrenome',
    4 => 'nivel'
    );

$where_condition = $sqlTot = $sqlRec = "";

if( !empty($params['search']['value']) ) {
    $where_condition .= " WHERE ";
    $where_condition .= " ( nome LIKE '%".$params['search']['value']."%' ";    
        $where_condition .= " OR sobrenome LIKE '%".$params['search']['value']."%' )";
}

$sql_query = "
SELECT 
id,
date_format(cadastro, '%d/%m/%Y') as cadastro,
nome,
sobrenome,
nivel
FROM usuarios";
$sqlTot .= $sql_query;
$sqlRec .= $sql_query;

if(isset($where_condition) && $where_condition != '') {

    $sqlTot .= $where_condition;
    $sqlRec .= $where_condition;
}

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

$queryTot = mysqli_query($con, $sqlTot) or die("Database Error:". mysqli_error($con));

$totalRecords = mysqli_num_rows($queryTot);

$queryRecords = mysqli_query($con, $sqlRec) or die("Error to Get the Post details.");

while( $row = mysqli_fetch_row($queryRecords) ) { 
    $data[] = $row;
}   

$json_data = array(
    "draw"            => intval( $params['draw'] ),   
    "recordsTotal"    => intval( $totalRecords ),  
    "recordsFiltered" => intval($totalRecords),
    "data"            => $data
    );

echo json_encode($json_data);
?>

Follows structure of my table "users"

id (int)
registration (datetime)
name (varchar)
surname (varchar)
level (int)

    
asked by anonymous 14.05.2018 / 16:51

1 answer

0

There are some ways to sort sorting from the DataTables library.

By far, the easiest is to use what they come from: DataTable: Sorting plug-ins

You are advised to use the Ultimate Date / Time sorting plug-in. You'll need to add in conjunction with the Moment.js plugin

<script src="http://momentjs.com/downloads/moment.min.js"></script><scriptsrc="https://cdn.datatables.net/plug-ins/1.10.16/sorting/datetime-moment.js"></script>

And, before calling the DataTable, add the parameterization:

$(document).ready(function(e){
    $.fn.dataTable.moment('dd/mm/YYYY');
    $('#server-side').dataTable({

Sources:

link

link

    
14.05.2018 / 22:40