Sort Datetime from a Datatable

Asked

Viewed 1,180 times

0

I am having problems ordering Datetime field from a Datatable, I am returning records from my table users via server-side. The problem is time to click on the label of a column of the type Datetime, the data are ordered as if they were String and not as Datetime. Below the example image:

inserir a descrição da imagem aqui

index php.

<!DOCTYPE html>
<html>
<head>
    <meta charset="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>
<script src="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>
<script src="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)
register (datetime)
name (varchar)
surname (varchar)
level (int)

php datetime

1 answer

1

There are some ways to perform ordering (Sorting) of the kind Date through the Datatables library.

By far the easiest is to use what they provide: Datatable: Sorting plug-ins

It is advisable to use the plug-in Ultimate Date / Time Sorting. You will need to add in conjunction with the plug-in Moment js.

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

And, before the Datatable call, add the parameterization:

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

Sources:

https://datatables.net/plug-ins/sorting/datetime-moment

https://datatables.net/blog/2014-12-18

  • I tried to use this function but it’s still bringing the date messy

  • @Smokerohden update your question with the new implementation. Use the Javascript/HTML/CSS code tag and bring some data so it can be executed here directly.

Browser other questions tagged php datetime

You are not signed in. Login or sign up in order to post.