See age instead of date of birth
See age instead of date of birth
ClaudioGS
Posts: 22Questions: 0Answers: 0
I have a table with the field f_nacimiento, and I want to show the age instead of the date in the column.
I have the following query in the model:
"SELECT * FROM habitantes WHERE DATE_SUB(NOW(), INTERVAL 60 YEAR) >= f_nacimiento";
I have the following codes:
adultos.js:
tblAdultos = $('#tblAdultos').DataTable({
ajax: {
url: base_url + "/Adultos/listar",
dataSrc: ''
},
columns: [
{ 'data': 'id_habitante' },
{ 'data': null,
render: function ( data, type, row) {
return row.apellidos + ' ' + row.nombre;} },
{ 'data': 'rut' },
{ 'data': 'nro_depto' },
{ 'data': 'fono' },
{ 'data': 'parentesco' },
{ 'data': 'f_nacimiento'},
{ 'data': 'estado' }
],
language: {
"url": "//cdn.datatables.net/plug-ins/1.10.11/i18n/Spanish.json"
},
});
index.php:
<table class="table table-light table-bordered table-hover" id="tblAdultos">
<thead class="table-dark">
<tr>
<th>Id</th>
<th>Nombres</th>
<th>Rut</th>
<th>Depto</th>
<th>Teléfonos</th>
<th>Parentesco</th>
<th>Edad</th>
<th class="text-center">Estado</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
How can I put the age in the js file?
I have only put the code that I think is necessary
Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.
Replies
Two options:
Allan
@allan: compute it in your SQL query
I already have the query:
"SELECT * FROM habitantes WHERE DATE_SUB(NOW(), INTERVAL 60 YEAR) >= f_nacimiento";
What I need is to show the age instead of f_birth in datatable
Use
columns.renderto calculate and display the age. See theComputing valuesexample in this page. Also see this example.Kevin
You have it as a condition in your SQL. But the field selected are just the table fields.
You'd need to so:
If you want to be able to access the value. Note that with this method you can update the WHERE statement to be
age >= f_nacimiento.Or use a client side renderer as Kevin says.
Allan
Thanks, I did the following:
"SELECT *, DATE_SUB(NOW(), INTERVAL 18 YEAR) AS edad FROM habitantes WHERE edad <= f_nacimiento";I tried it in the mysql console and it gives the following error:
ERROR 1054 (42S22): Unknown column 'age' in 'where clause'
in the table I don't have an age field, only f_nacimiento
It was aliased in my query (
as age) which should make it available in the condition. However, I see you usededadinstead, and I presume that works and you've got the data you need in the table now?Allan
@allan No, I still don't get what I want, the alias is edad.
What happens is that to write the questions and answers I use the google translator and somewhere it changes to age
What SQL server are you using (and version)?
@allan, Server version: 10.11.2-MariaDB-1 Debian
Ah! Sorry. It appears that MiraDB / MySQL doesn't allow that. See this SO thread.
You could use
HAVINGinstead, or just repeat the calculation in the condition.Allan