Searching for Age Range by Date of Birth

Hey there. I'm trying to build a script that searches for an age range based off of a user's date of birth. For some reason, people aren't showing up, and they are the people that are the extreme ends of the age ranges. For instance, I have an age range of 18 to 29 year olds, but some 29 year olds don't show up. Here's the script I'm running:

$age = cleanInput($_GET['age']);
$thisyear = date('Y');

if($age == '18-29') 
{
   $youngest = $thisyear - 18;  $oldest = $thisyear - 29;
}

$oldest = $oldest.date('-m-d');
$youngest = $youngest.date('-m-d');

PHP:

Then the query:

$query = "SELECT * FROM actors WHERE (dob <= '$youngest' AND dob >= '$oldest')";

PHP:

The dob field in the database is in the form Y-m-d. Is there a different way to do it that I might be able to use? Any help would be appreciated.

 

You could work out what the youngest person's date of birth would be ... Time-(18*365*24*60) .. then convert it to a UNIX time or something...

I can give you further information if you don't understand exactly what I mean!

 

Are you suggesting mktime? How would I implement that?

 

why not use following query?

SELECT *, (DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(dob)), '%Y') + 0) AS age FROM actors HAVING age BETWEEN $start_age AND $end_age

PHP:

where
$start_age is minimum of age range to search - e.g. 18
$end_age is maximum of age range to search - e.g. 29

 

I'm trying that query and for some reason I'm getting an error stating that the syntax is incorrect. I have additional parameters that I'm search for as well, so how would I add those in? (gender, class, etc)

 

you can use where in that case..

SELECT *, (DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(dob)), '%Y') + 0) AS age FROM actors WHERE gender = 'M' and class = 'XYZ' HAVING age BETWEEN $start_age AND $end_age;

PHP:

 

EDIT: Scratch what I just said. It's working perfectly now! Thanks for your help!

 

they should be actual age as explained in my example.
glad that it works for you

 

Was just wandering if mastermunj query can be converted to allow for multiple age groups.

For example

(18- 24) & (25 - 34) & (35 - 44).....
Or if one wanted to miss out the age group (25 - 34) WOULD have a query like...
(18-24) & (35 - 44) ....

Is this possible

 

@reubenrd, There is no direct way to prepare ranges and group them.

Following approaches would be suggested.
1. If such queries are frequent and data size is large, having a separate column is best option in all sense.
2. If you however want it to be part of query only, then its tricky and goes like this..

SELECT field1, field2, IF(age BETWEEN 18 TO 24, '18-24', IF(age BETWEEN 25 TO 34, '25-34', IF(age BETWEEN 35 TO 44, '35-44', 'Above 44'))) AS AgeGroup FROM table WHERE condition GROUP BY AgeGroup

Code (markup):

You can replace IF with CASE statement if wish to. Check out MySQL manual for that.