See age instead of date of birth

See age instead of date of birth

ClaudioGSClaudioGS Posts: 22Questions: 0Answers: 0
edited April 2023 in Free community support

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

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    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

  • ClaudioGSClaudioGS Posts: 22Questions: 0Answers: 0

    @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

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794

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

    Kevin

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    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

  • ClaudioGSClaudioGS Posts: 22Questions: 0Answers: 0

    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

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    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

  • ClaudioGSClaudioGS Posts: 22Questions: 0Answers: 0

    @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

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    What SQL server are you using (and version)?

  • ClaudioGSClaudioGS Posts: 22Questions: 0Answers: 0

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

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    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

Sign In or Register to comment.