• 0
Sign in to follow this  
Followers 0

[PHP/SQL] Calculate Age

Question

Posted

I have a mySQL database and a PHP frontend. The date of birth is stored within the database and im looking for some code that calculates the age preferably as a PHP

Share this post


Link to post
Share on other sites

16 answers to this question

  • 0

Posted

Where dob is the column which holds the date of birth. :)
[code]
SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age
[/code]

Share this post


Link to post
Share on other sites
  • 0

Posted

Never done this but could be something along:

[code]
age = current_year - birth_year;

if(current_date < birth_date && current_month < birth_month){
age = age - 1;
}
[/code]

Assuming you store months with their numerical value which is the better way IMHO.

Share this post


Link to post
Share on other sites
  • 0

Posted

The PHP website is reading straight off the table called members. I havent created any views or anything. Im also using phpMyAdmin to create all the database.
Where would I type in that statement?

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age

Share this post


Link to post
Share on other sites
  • 0

Posted

You would execute it where required; it's no different to any of the other queries you're executing.
[code]
SELECT
id
, DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age
, name
FROM
member
[/code]

Share this post


Link to post
Share on other sites
  • 0

Posted

Are you not encrypting those DOBs?

Share this post


Link to post
Share on other sites
  • 0

Posted

[quote name='AnthonySterling' timestamp='1307894364' post='594065242']
You would execute it where required; it's no different to any of the other queries you're executing.
[code]
SELECT
id
, DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age
, name
FROM
member
[/code]
[/quote]

There are no queries. which means im going somethign wrong here :s

Ok... Let me explain this situation a little better.

The database has information on members and their date of birth is stored. I have created a form using PHPRunner which creates the form from the members table. Since age cannot be stored into the database. The software has the ability to input functions or PHP code snippets.
So i was thinking, rather can creating queries, i would use php code instead to calculate the age. The php website can add and view members from the database.

hope this helps

Share this post


Link to post
Share on other sites
  • 0

Posted

you can just use [url="http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_datediff"]DATEDIFF[/url], and then round the end figure down after dividing the number of days in a year

i.e. my birthday is 11 oct 1984

[code]select datediff('11-oct-1984',now())/365[/code]

that query results in 26, which is correct.. i am not a MySQL or PHP Dev so please test this first, it works in MSSQL anyway :p

[quote name='SinghKing' timestamp='1307894813' post='594065262']
There are no queries. which means im going somethign wrong here :s
[/quote]
well, how are you going to get the data out of the db? magic? :blink:

you will need to query the db in order to get the DOB, so, why not in your query convert it to the age rather than get php to do it for you on the page?

if you want to get the DOB from the db, and then convert it on the page via PHP.. try this function: http://www.bradino.com/php/calculate-age/ ;)

Share this post


Link to post
Share on other sites
  • 0

Posted

duplicate :)

Share this post


Link to post
Share on other sites
  • 0

Posted

I searched and found this: http://php.net/manual/en/datetime.diff.php

EDIT: Guy above me was faster XD.

Share this post


Link to post
Share on other sites
  • 0

Posted

I see.

[code]
function getAge($birthday){
list($day, $month, $year) = explode('/', $birthday);
$age = date('Y') - $year;
$month_diff = date('m') - $month;
$day_diff = date('d') - $day;
if($day_diff < 0 || $month_diff < 0){
$age--;
}
return $age;
}
[/code]

I was going to suggest DateTime:diff but it is 5.3 dependent.*

*date_diff is an alias for DateTime::diff

Share this post


Link to post
Share on other sites
  • 0

Posted

@Anthony:

Shouldn't that be:

[code]
if($day_diff < 0 && $month_diff < 0)
[/code]

Share this post


Link to post
Share on other sites
  • 0

Posted

Thanks guys, got it working. I used the following query to calculate the code

[code]
SELECT *, DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(aDoB, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(aDoB, '00-%m-%d')) AS age FROM members
[/code]

And then I saved it as a View. I then created the PHP table using the view and now the age automatically update.

TYVM

Share this post


Link to post
Share on other sites
  • 0

Posted

[quote name='SinghKing' timestamp='1307980536' post='594067670']
Thanks guys, got it working. I used the following query to calculate the code

[code]
SELECT *, DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(aDoB, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(aDoB, '00-%m-%d')) AS age FROM members
[/code]

And then I saved it as a View. I then created the PHP table using the view and now the age automatically update.

TYVM
[/quote]
looks a bit inefficient, not sure though, would have to test it out

glad it works :)

Share this post


Link to post
Share on other sites
  • 0

Posted

[quote name='BGM' timestamp='1307896614' post='594065306']
you can just use [url="http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_datediff"]DATEDIFF[/url], and then round the end figure down after dividing the number of days in a year

i.e. my birthday is 11 oct 1984

[code]select datediff('11-oct-1984',now())/365[/code]

that query results in 26, which is correct.. i am not a MySQL or PHP Dev so please test this first, it works in MSSQL anyway :p
[/quote]

Leap years will throw that off so it is inaccurate.

Share this post


Link to post
Share on other sites
  • 0

Posted

[quote name='rev23dev' timestamp='1307984453' post='594067860']
Leap years will throw that off so it is inaccurate.
[/quote]
hmm.. correct :(

Share this post


Link to post
Share on other sites
  • 0

Posted

[quote name='BGM' timestamp='1307990700' post='594068246']
hmm.. correct :(
[/quote]

You could always determine how many leap years have passed between the date in question and the current date and then subtract that -- but that just seems... silly ;) (and actually even then it might be wrong...)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.