How to store Birthdate and Age so that Age can be updated daily in PHP/MySQL?

Solution 1:

The simple answer is don't; never store a persons age. It changes for each person yearly but, as you say, you have to check that it's correct for every person daily.

Only store the date of birth, and then calculate the age when selecting from the database. It's only today - date of birth so takes almost no CPUs at all.

EDIT:

To expand upon my comment in ManseUK's answer there's also the possibility of failure. What happens if your server / database is down? Or your update fails to run at its specified time? Or someone comes along and runs it manually after the update already been run for that date? Or someone turns off your scheduler? There's no danger of this happening if you calculate Age as you select from the database.

To select where age is between 25 and 30 years and assuming a DATE column dateofbirth your query would be something like:

select *
  from users
 where dateofbirth between date_add( curdate(), interval -30 year )
                       and date_add( curdate(), interval -25 year )

Ensure users is indexed on dateofbirth.

Solution 2:

No, don't store age, just calculate it in your queries. As for the birthday, I prefer to have all my date/time in unix timestamps (because I hate to deal with portability across date-format-changing locale settings)