Calculating age from a date field
Posted: 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:
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:
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:
Finally, in the Demographics_header() hook, add this line before the "return" statment:
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.
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);
}
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']}'");
Code: Select all
updateAge("ID='{$data['selectedID']}'");
Code: Select all
updateAge("MONTH(DOB)=" . date('n'));
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.