使用php mysqli或pdo更新数据库中的age列

Question

How to update age automatically based on its DOB? I'm planning to write a script that fetch DOB column then calculate the age and update it. I started my idea but it still doesn't meet my objective.

Pls. help! Thanks in advance :)

Answer 1

Adding an age column when you have already date of birth, is slightly futile. Also, this means you would need to update the value once a year for every record, and at their birth date. That will start to get heavier and heavier. A lot of comments suggested to do the calculation of the age in PHP, this also would get heavy if you wanted to group people by age, you'd have to grab every records of the database and calculate their age. My suggestion is to use MySQL to calculate it, so you can use it in joins, where clauses, group bys and so on...

SELECT YEAR(NOW()) - YEAR(birthdate) - (DATE_FORMAT(NOW(), '%m%d') < DATE_FORMAT(birthdate, '%m%d')) as age FROM table

For more details, this was taken from How to get the difference in years from two different dates? which accounts for leap years.

You could easily create a MySQL function, call it calc_age or something and pass it the birthdate, that would return the current age and would be very easy to use...