2. PHPRunner General questions
  3. [SOLVED]  Calculating Age from MySQL Date Field
This topic is locked
[SOLVED]

 Calculating Age from MySQL Date Field

 		1/4/2007 1:44:54 PM
PHPRunner General questions
L
 larsonsc author

I have been serching high and low for a way to do this, but have not yet found a way to make it happen. Maybe someone on this board knows how to do this, so I'll give it a shot.
I need to take the DOB value from my table and calculating age in Years and Months if I can. I can get years to work for the most part, but the months seem to be an issue.
I was using the following to fill the field, but I was getting negative values in the months, so that's no good.

concat((YEAR(CURDATE()) - YEAR(`Date_Of_Birth`)), ' Year(s)', (MONTH(CURDATE()) - MONTH(`Date_Of_Birth`)), ' Month(s)') AS "Estimated Current Age"


Anyone know of a way I can make this work? Or maybe there is a better way to do this via PHP in an event? I'm not sure, but I'd sure love to find a way to make this calculation work if I can. Thanks for any help anyone can offer.
--Shawn
T
 		thesofa 1/4/2007

Hi

Have a look at post no 6 in this post and you can see how to get month, year and day if need be from the current date using date()

and at the bottom of the quoted text, there is the way to assemble the altered dates into a date field.

There may be some help in this for you

This post from a search on birthday in these fora also produced fruit.

If not, there is a very good reference here in w3 schools you will find the date functions in the references section.

Good luck and please post an elegant solution for us!

Cheers

HTH
L
 		larsonsc author 1/5/2007

I've been able to figure the age in years no problem. So far the major issue seems to be determinign the months as well.
For example, I can get an age field to calculate to 5 years, but not 5 years 3 months. I'll review what you linked as well as do some more searching as I just can't possibly imagine that I am the only person who has ever needed to get years and months in a calculated age field. If I figure something out, I'll be sure to share.
Thanks for the links thesofa!
T
 		thesofa 1/5/2007

I've been able to figure the age in years no problem. So far the major issue seems to be determinign the months as well.

For example, I can get an age field to calculate to 5 years, but not 5 years 3 months. I'll review what you linked as well as do some more searching as I just can't possibly imagine that I am the only person who has ever needed to get years and months in a calculated age field. If I figure something out, I'll be sure to share.
Thanks for the links thesofa!



try this page about birthdays

And about calculating diffs in dates from here.

hth
V
 		vincentwansink 3/25/2009

I've been able to figure the age in years no problem. So far the major issue seems to be determinign the months as well.

For example, I can get an age field to calculate to 5 years, but not 5 years 3 months. I'll review what you linked as well as do some more searching as I just can't possibly imagine that I am the only person who has ever needed to get years and months in a calculated age field. If I figure something out, I'll be sure to share.
Thanks for the links thesofa!


Have you found a solution to this yet? I also needs years and months as I'm writing a php app for daycares.
V
 		vincentwansink 3/25/2009

Here's what I came up with. It's very simple and easy to read. I know some people would prefer to optimize this into less lines of code but I prefer to have my code easy to read, like so.
However, take it as a starting point and modify to your heart's content.
Calculating age in years and months using php:

function getAge($birthdate) {

list($year,$month,$day) = explode("-",$birthdate);

if ($year != "" && $year != "0000") { //If year is not blank and not null then we continue
if ($year >= date("Y")-2) { //If the birthyear is within two years, then we'll only calculate the months not years

if($year == date("Y")){ //Current year

$months = date('m') - $month;

}

if($year == date("Y")-1){ //Last year

$months = date('m') - $month + 12;

}

if($year == date("Y")-2){ //Two years ago

$months = date('m') - $month + 24;

}
return $months." months";

} else {

$years = date("Y")-$year-1;
if(date("m")==$month){ // If the birth month is equal to the current month then we just return years, no months.

$years = $years + 1;

return $years." years";

}else{ // Else get the months and years

$months = date("m")+12-$month;

return $years." years ".$months." months";

}

}

}else{

return "";

}

}