Page 1 of 1

Calculating Age

Posted: Fri Aug 14, 2009 3:41 pm
by DAVID87
Hi All,

I have produced a function to calculate the age of an input from a date in the format YYYYMMDD.

What I want to achieve is that there is a drop down list that sets the values for the input date (currently static as for testing but will be dynamic when I complete the page.)

the selection box is named chgala and has an onchange command which changes the selection. The rest of the code is shown here:

Code: Select all

 
if (isset($_POST['chgala'])) { $selectedyear = "$_POST[chgala]"; } else { $selectedyear = date("Y")."1231"; }
 
include 'dbc.php';
 
$sql="SELECT * FROM membership WHERE member = 'Y' ORDER BY DOB DESC";
$result = mysql_query($sql);
 
$GalaDate = $selectedyear;
$gdy = substr($GalaDate,0,4);
$gdm = substr($GalaDate,4,2);
$gdd = substr($GalaDate,6,2);
echo "gdy = ".$gdy." gdm = ".$gdm." gdd = ".$gdd;
 
function DetermineAgeFromDOB ($YYYYMMDD_In) 
{ 
// Parse Birthday Input Into Local Variables 
// Assumes Input In Form: YYYYMMDD 
$yIn=substr($YYYYMMDD_In, 0, 4); 
$mIn=substr($YYYYMMDD_In, 4, 2); 
$dIn=substr($YYYYMMDD_In, 6, 2); 
// Calculate Differences Between Birthday And Date Set 
// By Subtracting Birthday From Date Set 
$ddiff = $gdd - $dIn; 
$mdiff = $gdm - $mIn; 
$ydiff = $gdy - $yIn; 
// Check If Birthday Month Has Been Reached 
if ($mdiff < 0) 
{ 
// Birthday Month Not Reached 
// Subtract 1 Year From Age 
$ydiff--; 
} elseif ($mdiff==0) 
{ 
// Birthday Month Currently 
// Check If BirthdayDay Passed 
if ($ddiff < 0) 
{ 
//Birthday Not Reached 
// Subtract 1 Year From Age 
$ydiff--; 
} 
} 
return $ydiff; 
} 
 
Everything that I can see has a description by it to show what it is doing.

Following this I have the table layout to display the data and then a while statement to print the data in the table. within this there is the function caption that calculates the age from a date of birth (in the format YYYYMMDD)

Code: Select all

while($rows=mysql_fetch_array($result)){
// Example Age Input 
$DateOfBirth= $rows['DOB']; 
// Calculate Age Using Function: 
$Age= DetermineAgeFromDOB ($DateOfBirth);
$doby=substr($DateOfBirth,0,4);
$dobm=substr($DateOfBirth,4,2);
$dobd=substr($DateOfBirth,6,2);
 
and then when I want to display the age I just echo or print '$Age'

This seems to work if I take out the date function at the start to change the date and have it work off a current date, but now with this changing of date no matter which I select it seems to calculate their and and then minus 2009 (current year).

i.e. if someone should be 4 years old today then this will show as -2005.

Can anyone see where this is going wrong, why it is taking off the current year and why it doesnt change when I change the selection.

If anyone can see where I am going wrong and correct me I will be very grateful or if someone can offer an alternative for me yo use that would also be appreciated.

Thanks in advance

Re: Calculating Age

Posted: Fri Aug 14, 2009 3:52 pm
by Eran
You'd have a much easier time if you stored the date in a native MySQL date column (such as timestamp or datetime)

Re: Calculating Age

Posted: Fri Aug 14, 2009 3:57 pm
by DAVID87
I can get the stored DOB (the date) in the format YYYY-MM-DD (a native date). but how could I write a function that would:

a, calculate the current age in years from this DOB from a set date.
b, get the set date to change what I change it in a selection box.

If you look back at the start of my first snippet of code here:

Code: Select all

echo "gdy = ".$gdy." gdm = ".$gdm." gdd = ".$gdd;
I have get the selection to echo onto the page and this changed with my selection change but nothing else was updated.

Any ideas?

Re: Calculating Age

Posted: Fri Aug 14, 2009 4:17 pm
by Eran
If the date is stored in a native date column (and not just a field that emulates the format), you can use mysql date functions to do the work for you.

Code: Select all

SELECT DATEDIFF(date_col,'2010-12-31') WHERE member='Y'
Replace 2010-12-31 with your user input of course. This returns the difference in days (integer), divide by 365 and floor to get the years (age)

Re: Calculating Age

Posted: Fri Aug 14, 2009 4:35 pm
by DAVID87
Thats great (alot shorter code)

you say that I should just divide the result of the DATEDIFF by 365 (days in the year) to work out the age.

However I do not know hoe to pull this back from the original select query.

Am I right in thinking that this will work as my select query or do I have to put your suggested snippet in the while statement?

Code: Select all

$sql="SELECT *, DATEDIFF(new_DOB, $selectedyear) FROM membership WHERE member = 'Y' ORDER BY DOB DESC";
If this is correct how do I pull this value back so that I can work with it (display/calculate)??

Re: Calculating Age

Posted: Fri Aug 14, 2009 4:43 pm
by Eran
You should probably give it an alias so it'd be easy to retrieve.

Code: Select all

$sql="SELECT *, DATEDIFF(new_DOB, $selectedyear) AS days_diff FROM membership WHERE member = 'Y' ORDER BY DOB DESC";
$result = mysql_query($sql);
if($result) {
    $row = mysql_fetch_array($result);
    $age = floor($row['days_diff'] / 365);
}

Re: Calculating Age

Posted: Sat Aug 15, 2009 2:41 am
by DAVID87
Thanks for the help.

This in logic would work except now it returns a value of 0 for ALL the ages. and again does not apear to show any change when I change my selection.

I have my dates set as YYYY-MM-DD that it is looking at and then also the "DOB" that it is returning is YYYY-MM-DD from a date field in my Database.

I have echoed the stages up to this and found that the DATEDIFF(new_DOB, $selectedyear) AS Age is not actually showing in a echo $rows[Age]; statement.

It is apearing that the DATEDIFF is doing noting or is returning nothing. what does DATEDIFF actuall do as I cant find it documented anywhere.

Any ideas?

Re: Calculating Age

Posted: Sat Aug 15, 2009 7:04 am
by Eran
What is the type of the field that stores the date

Re: Calculating Age

Posted: Sat Aug 15, 2009 7:13 am
by DAVID87
it is a "DATE" field with a max length of 10 so it will be stored as YYYY-MM-DD and no more.

Re: Calculating Age

Posted: Sat Aug 15, 2009 7:22 am
by Eran
A date column has no length, regardless of what you think you set to it. Are you sure its a date column?

since you don't show your actual code I can't really help you, but running the query I gave with you I get:

Code: Select all

SELECT DATEDIFF('2009-08-15',new_DOB) AS days_diff FROM membership WHERE member = 'y' ORDER BY new_DOB DESC

Code: Select all

days_diff
10237
as expected.