Calculating age from a date field

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Calculating age from a date field

Post by a.gneady » 2014-03-17 11:49

This is a very common case: you have a date field in your database application where you store birth dates, and you need to have the age automatically calculated and inserted into another field. Let's assume that the table concerned is called "Demographics", and that the primary key field is named "ID", the birth date field is called "DOB" and the age field is called "Age".

Now, we need to write a PHP function for calculating age and updating the "Age" field based on the value stored in the "DOB" field. I assume here that you've already generated your application files using AppGini. In the "hooks" folder, open the generated "Demographics.php" file (or {tablename}.php, where tablename is the actual name of your table), add the following code at the end of the file:

Code: Select all

function updateAge($where = ''){
    $sql = "update `Demographics` set `Age` = round(datediff('".date('Y-m-d')."', `DOB`) / 365.25)";
    if($where) $sql .= "where $where";
    sql($sql, $eo);
}
Don't forget to change the table and field names above to match those of your own application. The above code uses the "datediff" MySQL function to calculate the number of days that has passed since the date stored in "DOB" field. By dividing that number of days by 365.25 (the average number of days in a year), we obtain the age value in years.

Next, we need to use this function in 3 scenarios:

1. When a new record is added to the "Demographics" table.
2. When a record is updated.
3. Periodically to update age in all records.

To satisfy all three scenarios above, we'd call updateAge() in 3 hook functions: Demographics_after_insert(), Demographics_after_update() and Demographics_header().

In the Demographics_after_insert() hook, add this line before the "return" statment:

Code: Select all

updateAge("ID='{$data['selectedID']}'");
The above line causes the age field for the newly inserted record to be updated. In the Demographics_after_update() hook, add this line before the "return" statment:

Code: Select all

updateAge("ID='{$data['selectedID']}'");
Finally, in the Demographics_header() hook, add this line before the "return" statment:

Code: Select all

updateAge("MONTH(DOB)=" . date('n'));
The above line causes the age field to be updated for all records where the month of birth is the same as the current month.

That's all we really need. Your age field should now be working fine. Please let me know if you have any questions or comments.
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

User avatar
toconnell
Veteran Member
Posts: 204
Joined: 2013-04-09 19:29
Location: Oklahoma City, OK
Contact:

Re: Calculating age from a date field

Post by toconnell » 2014-03-31 19:06

I want to do something just like this.. with a bit of a difference..

I have a foriegn key look up for school, when the school is chosen it automatically drops in the school start date.

right after that we have 'evac1_due' field. I would like evac1_due field to update automatically when the school is chosen to 6 weeks after the school start date.

that field is 'school_start'

So would that be like this??? So lost

Code: Select all

function updateevac1_due
$sql=update'evacuations' set 'evac1_due'='school_start'.date('Y-m-d').strttotime("+6 week");
if($where) $sql .= "where $where";
sql($sql, $eo);
}

Tina O'Connell
Web Dev & Appgini FAN

User avatar
toconnell
Veteran Member
Posts: 204
Joined: 2013-04-09 19:29
Location: Oklahoma City, OK
Contact:

Re: Calculating age from a date field

Post by toconnell » 2014-04-01 19:18

OK.. I then tried this when that did not work..

Code: Select all

	function evacuations_before_insert(&$data, $memberInfo, &$args){


$data['evac1_due']=date("YMD",strtotime($data['school_start'].+"6 week"));
		return TRUE;

	}

It did not work either.
Any thoughts? Not getting error messages.
Tina O'Connell
Web Dev & Appgini FAN

User avatar
toconnell
Veteran Member
Posts: 204
Joined: 2013-04-09 19:29
Location: Oklahoma City, OK
Contact:

Re: Calculating age from a date field

Post by toconnell » 2014-04-04 19:09

OK I found this to help me...
http://www.bigprof.com/appgini/tips-and ... eld-values

Here goes..

function evacuations_before_insert(&$data, $memberInfo, &$args){

$evac1date= (strtotime($school_start)+"6 week"));
return TRUE;

}
{$data['evac1_due']=$evac1date}

Does this look right?
Tina O'Connell
Web Dev & Appgini FAN

danoshea
Posts: 9
Joined: 2015-02-25 21:48

Re: Calculating age from a date field

Post by danoshea » 2015-03-12 19:28

By the way, if you don't want years and just want days, simply remove a portion where it calls for rounding and 365 days division.

Original for YEARS:
$sql = "update `Demographics` set `Age` = round(datediff('".date('Y-m-d')."', `DOB`) / 365.25)";

Modified for DAYS

$sql = "update `Demographis` set `Age` = datediff('".date('Y-m-d')."', `Date_Listed`)";

Worked for me well.

sueirna
Posts: 9
Joined: 2014-02-06 23:59

Re: Calculating age from a date field

Post by sueirna » 2015-11-09 19:23

i ve tried, it's not work for me. Here is the code on hook. My table name is 'failj' , DOB=tlahir and Age=Umur, Please help me how to do it, tq...

<?php


function failj_init(&$options, $memberInfo, &$args){
return TRUE;
}

function failj_header($contentType, $memberInfo, &$args){
$header='';

switch($contentType){
case 'tableview':
$header='';
break;

case 'detailview':
$header='';
break;

case 'tableview+detailview':
$header='';
break;

case 'print-tableview':
$header='';
break;

case 'print-detailview':
$header='';
break;

case 'filters':
$header='';
break;
}
updateAge("MONTH(tlahir)=" . date('n'));
return $header;
}

function failj_footer($contentType, $memberInfo, &$args){
$footer='';

switch($contentType){
case 'tableview':
$footer='';
break;

case 'detailview':
$footer='';
break;

case 'tableview+detailview':
$footer='';
break;

case 'print-tableview':
$footer='';
break;

case 'print-detailview':
$footer='';
break;

case 'filters':
$footer='';
break;
}

return $footer;
}

function failj_before_insert(&$data, $memberInfo, &$args){

return TRUE;
}

function failj_after_insert($data, $memberInfo, &$args){
updateAge("ID='{$data['selectedID']}'");
return TRUE;
}

function failj_before_update(&$data, $memberInfo, &$args){
return TRUE;
}

function failj_after_update($data, $memberInfo, &$args){
updateAge("ID='{$data['selectedID']}'");

return TRUE;
}

function failj_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){
return TRUE;
}

function failj_after_delete($selectedID, $memberInfo, &$args){

}

function failj_dv($selectedID, $memberInfo, &$html, &$args){

}

function failj_csv($query, $memberInfo, &$args){

return $query;
}
function failj_batch_actions(&$args){

return array();
}

function updateAge($where = ''){
$sql = "update `failj` set `Umur` = round(datediff('".date('Y-m-d')."', `tlahir`) / 365.25)";
if($where) $sql .= "where $where";
sql($sql, $eo);
}

?>

here is my form looks like

https://www.facebook.com/photo.php?fbid ... tif_t=like

when i change the year, i want it automatically calculate age on 'Umur' Field by its self.tq

Nicolas
Veteran Member
Posts: 43
Joined: 2015-12-08 08:08
Location: Barcelona
Contact:

Re: Calculating age from a date field

Post by Nicolas » 2016-11-19 10:20

Hi all, good day

I've been playing with the above code on my app and it has worked, but with a bit of twisting.. here it is... what I wanted is to get the Month value and year value on separate fields, for reporting purposes
First the functions declarations at the end of the hook file
function Year($where = ''){
$sql = "update `invoices` set `InvAno` = YEAR(`FFactura`)";
if($where) $sql .= "where $where";
sql($sql, $eo);
}
function Month($where = ''){
$sql = "update `invoices` set `InvMes` = MONTH(`FFactura`)";
if($where) $sql .= "where $where";
sql($sql, $eo);
}
and then added on each before insert, after insert before update and after update the following calls
Year("");
Month("");
and voila!!!! the fields where filled with the new data!

User avatar
Eren24
Posts: 1
Joined: 2017-01-13 00:00
Location: Brisbane, Australia
Contact:

Re: Calculating age from a date field

Post by Eren24 » 2017-01-16 06:26

Hi everyone...

Im new on using appGini but I love it now. I love the interface it created. It is user friendly regardless for some programmers like me. I am having a big problem over the age I would like to start a new thread but I love seeing a lot of answers here. I think I might give it a try. Thanks guys update you if any problem occur. :)
Sweet and happy type of person, loves to study and want to learn more about everything. Online courses help me to be a better employee and find a great job that fits my character.

User avatar
dilitimor
Veteran Member
Posts: 36
Joined: 2013-01-10 02:45
Location: Jakarta, Indonesia
Contact:

Re: Calculating age from a date field

Post by dilitimor » 2017-02-28 06:18

@a.gneady.. it work like a charm
Thank you very much

SkayyHH
Veteran Member
Posts: 425
Joined: 2015-04-27 21:18

Re: Calculating age from a date field

Post by SkayyHH » 2020-06-14 05:55

Hi,

a new "Calculated fields" feature is available in AppGini 5.80 and above.

https://bigprof.com/appgini/help/calculated-fields

How can I use this code in the "Calculated field"? What needs to be in the field?

Post Reply