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)