Page 1 of 1

Calculating age from a date field

Posted: 2014-03-17 11:49
by a.gneady
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.

Re: Calculating age from a date field

Posted: 2014-03-31 19:06
by toconnell
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);
}


Re: Calculating age from a date field

Posted: 2014-04-01 19:18
by toconnell
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.

Re: Calculating age from a date field

Posted: 2014-04-04 19:09
by toconnell
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?

Re: Calculating age from a date field

Posted: 2015-03-12 19:28
by danoshea
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.

Re: Calculating age from a date field

Posted: 2015-11-09 19:23
by sueirna
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

Re: Calculating age from a date field

Posted: 2016-11-19 10:20
by Nicolas
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!

Re: Calculating age from a date field

Posted: 2017-01-16 06:26
by Eren24
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. :)

Re: Calculating age from a date field

Posted: 2017-02-28 06:18
by dilitimor
@a.gneady.. it work like a charm
Thank you very much

Re: Calculating age from a date field

Posted: 2020-06-14 05:55
by SkayyHH
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?