[SOLVED] Calculate DATE OF BIRTH by given AGE

[john010117]

First of all, I've scoured Google, but they only gave me how to calculate age by given birth. I need the opposite.

 

So, here's the thing. I have a pretty simple site that has the basic user database, and a search engine. When a user registers, their date of birth (that they have provided) is converted into a UNIX timestamp and is stored in the database. Now, the search engine is designed so that a user can give a range of ages to search for (ex: can find all users between the ages of 18 to 30). Since I have users' date of birth stored in the database, not their ages, how will I design the script to work?

 

Will converting the given ages to UNIX timestamps first, then using MySQL's "<" and ">" operators to search the database work? Or is there a different way?

[Ken2k7]

First of all, I've scoured Google, but they only gave me how to calculate age by given birth. I need the opposite.

 

So, here's the thing. I have a pretty simple site that has the basic user database, and a search engine. When a user registers, their date of birth (that they have provided) is converted into a UNIX timestamp and is stored in the database. Now, the search engine is designed so that a user can give a range of ages to search for (ex: can find all users between the ages of 18 to 30). Since I have users' date of birth stored in the database, not their ages, how will I design the script to work?

 

Will converting the given ages to UNIX timestamps first, then using MySQL's "<" and ">" operators to search the database work? Or is there a different way?

Use the time() function to get the current UNIX time and then subtract it from the one in the database. That would give you the difference in seconds. You can convert that to years.

[john010117]

So that means I'll have to first SELECT every record from the database and check each record using your method?

[Ken2k7]

So that means I'll have to first SELECT every record from the database and check each record using your method?

No, you can use SQL query to do math calculations so you don't have to select every record.

[john010117]

Hm... I'm not very familiar with doing math inside queries... will it be something like this? (Assuming that "dob" is the field name and

$age1

and

$age2

are the submitted ages)

 

<?php
$query = 'SELECT *
	FROM users
	WHERE ' . time() . ' - dob > ' . $age1 . '
		AND ' . time() . ' - dob < ' . $age2;
?>

[Ken2k7]

Well with the WHERE clause, what column are you using?

 

The logic is: select users from the users table where the user's age is greater than (or equal to) time() minus $age1 and less than (or equal to) time() minus $age2. Right?

[PFMaBiSmAd]

The mysql manual contains an AGE calculation example -

 

http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html

 

Depending on php version and operating system, a UNIX timestamp cannot be used for ages because it cannot represent dates before 1970.

[john010117]

The mysql manual contains an AGE calculation example -

 

http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html

 

Thanks for the link.

 

Depending on php version and operating system, a UNIX timestamp cannot be used for ages because it cannot represent dates before 1970.

 

Hm, then what would be a better way to store date of births?

 

Well with the WHERE clause, what column are you using?

 

The logic is: select users from the users table where the user's age is greater than (or equal to) time() minus $age1 and less than (or equal to) time() minus $age2. Right?

 

That wouldn't be it because I have date of births stored in the database, not ages.

[kratsg]

//Search the database for all users equal to or less than 30 and equal to or greater than 18 (in this example)

$age1 = 30;
$age2 = 18;

//the following will handle the distinction between ages (age1 or age2 could be the greater age)
if($age1 > $age2){$ages1 = $age1;$ages2 = $age2;}
if($age1 < $age2){$ages1 = $age2;$ages2 = $age1;}
if($age1 = $age2){$ages1 = $age1;$ages2 = $age2;}

$age1_ts = $ages1*365.25*3600;//convert to seconds
$age2_ts = $ages2*365.25*3600;//convert to seconds

$query = 'SELECT * FROM users WHERE '.$age1_ts.' >= `dob` AND '.$age2_ts.' <= `dob` ORDER BY `dob` ASC';

 

Is that what you're looking for?

[john010117]

Ok, thank you both of you. I believe I got it.

[Barand]

If it is solved then that's a neat trick.

 

It's Jan 6th, 2008 and I'm 58.

 

But I could have become 58 today or I might become 59 tomorrow.

 

So all you know is that I was born sometime between Jan 7th 1949 and Jan 6th 1950

[Ken2k7]

If it is solved then that's a neat trick.

 

It's Jan 6th, 2008 and I'm 58.

 

But I could have become 58 today or I might become 59 tomorrow.

 

So all you know is that I was born sometime between Jan 7th 1949 and Jan 6th 1950

??? Huh? I don't even get this logic? 

[john010117]

If it is solved then that's a neat trick.

 

It's Jan 6th, 2008 and I'm 58.

 

But I could have become 58 today or I might become 59 tomorrow.

 

So all you know is that I was born sometime between Jan 7th 1949 and Jan 6th 1950

 

Yes, I'm aware of that fact. Just knowing how old someone is doesn't give you the exact birthdate.

[Barand]

If it is solved then that's a neat trick.

 

It's Jan 6th, 2008 and I'm 58.

 

But I could have become 58 today or I might become 59 tomorrow.

 

So all you know is that I was born sometime between Jan 7th 1949 and Jan 6th 1950

??? Huh? I don't even get this logic? 

 

OK, I'll type slowly

 

If I were 58 today my DOB would be 1950-01-06

If I am 59 tomorrow my DOB would be 1949-01-07

Either way I am 58 right now.

So my DOB must be somewhere between these two dates but there is no way of knowing where just from my age in years.

[redarrow]

So you need to no the users day month year to be percise...........

 

10/10/2008 example..........

[Ken2k7]

Oh Thank you Barand. Guess it was a bit confusing at first. Sorry to bother you like that.

[Barand]

That's OK.

 

But if you knew my age was 58yrs 11mths 15days then you could send me a birthday card

[Ken2k7]

Nope, I would require an address.