See age instead of date of birth

ClaudioGS

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.

allan

Two options:

1. Use a client-side renderer to calculate the age from the date of birth. You could use Moment/.js or Luxon to do that.
2. Or calculate it in your SQL query using the SQL server's date calculation functions.

Allan

ClaudioGS

@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

kthorngren

Use columns.render to calculate and display the age. See the Computing values example in this page. Also see this example.

Kevin

allan

You have it as a condition in your SQL. But the field selected are just the table fields.

You'd need to so:

SELECT *, DATE_SUB(NOW(), INTERVAL 60 YEAR) AS age
FROM ...

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

ClaudioGS

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

allan

It was aliased in my query (as age) which should make it available in the condition. However, I see you used edad instead, and I presume that works and you've got the data you need in the table now?

Allan

ClaudioGS

@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

allan

What SQL server are you using (and version)?

ClaudioGS

@allan, Server version: 10.11.2-MariaDB-1 Debian

allan

Ah! Sorry. It appears that MiraDB / MySQL doesn't allow that. See this SO thread.

You could use HAVING instead, or just repeat the calculation in the condition.

Allan