Translating date to age

[Guest]

Hello forum buddies.

I am trying to translate a date of birth in php. I collect a users date of birth and insert it into a php database as 30 character VARCHAR. I would like to query the date of birth and translate them into an age number how could I do this. The date of birth is collected using select menus like day month year and inserted as a varchar. How could I then translate that to a age number.

Thank you.

[Guest]

What is the format of the birth date when it is sent to the database?

yyyy-mm-dd

[bregent]

>I collect a users date of birth and

>insert it into a php database as

>30 character VARCHAR.

It's generally a bad idea to store date data as anything other than date datatypes. The way the user interface works here is irrelevent. Take the user input, convert and store in the DBMS as a date.

[Guest]

Hi

I stored the date as a var because i was using 3 drop down menus day mon year and did not know how to pass the data through the date function

/* $date=date('','',''); */

So I just took the data from the drop down menus and inserted it into the varchar.

[ultrasuite]

I think the advice given above to use date datatype is sound.  You don't need to use varchar.  Use concatenation to convert the three values into date type format.

J.S.

http://www,ultrasuite.com/

[David_Powers]

Store the date in a DATE column in the database. As has already been suggested, concatenate the three values before inserting them.

$dob = $_POST['year'] . '-' . $_POST['month'] . '-' . $_POST['date'];

Insert $dob into the database.

To get the person's age, use the following SQL query

SELECT CURDATE() AS today,
YEAR(CURDATE()) - YEAR(dob) - IF(RIGHT(CURDATE(), 5) < RIGHT(dob,5),1,0)
AS age FROM mytable

The second line in that SQL query subtracts the year of birth from the current year, and then subtracts 1 if the person's birthday hasn't occurred this year. The result is assigned to the alias "age".

[Guest]

Hi

Thanks alot

I believe the first part code would enable me to insert as date.

The second part is abite confusing.

SELECT CURDATE() AS today,
YEAR(CURDATE()) - YEAR(dob) - IF(RIGHT(CURDATE(), 5) < RIGHT(dob,5),1,0)
AS age FROM mytable

So I wrote it as when operating

$collectdate=mysql_query("

SELECT CURDATE() AS today,
YEAR(CURDATE()) - YEAR(dob) - IF(RIGHT(CURDATE(), 5) < RIGHT(dob,5),1,0)
AS age FROM mytable

");

Does the variable (dob) in the code represent the date of birth row that the date of birth was inserted. I guess that what its supposed to be.

On As age in the code does this mean.  echo $row['age']; would provide the age number of the User even though they is no age row in the table?

Thanks again for the help.

Greatly appreciate.

[David_Powers]

Yes, "dob" is the name of the column that holds the date of birth, and "mytable" is the name of the table that you're using.

The AS keyword in a SQL query creates an alias. It's used twice in the query. The first time, the value of CURDATE() is assigned to "today". The second time, the result of the calculation is assigned to "age". Both aliases can be retrieved from your result as $row['today'] and $row['age'] (assuming you assign the current record to $row).

I admit that the SQL query is complex, and I take no credit for creating it myself. It comes from "MySQL Cookbook" by Paul DuBois. He's probably the best writer on MySQL.

[Guest]

Thanks again for the reply

Just for verification does the code below  then give a single  number age of the user i.e 25.

<?Php

/*The translation code then results*/

$userage=$row['age'];

echo $userage;

?>

Thanks again I will check out the cookbook when I can.

[David_Powers · Accepted solution]

Tony404 wrote:

Just for verification does the code below  then give a single  number age of the user i.e 25.

Yes.