searching on age range when dob is stored as timestamp

[stijn0713]

i'm stuck with searching on age query

 

i let users register their date of birth (they give in DAY, MONTH, YEAR)  --> column 3

 

Name CalcAge Timestamp Timestamp calculated for query

Person 1 21 659948401 642754801

Person 2 39 91954801               83746801

Person 3 15 849337201 848991601

Person 4 33 281257201 281170801

Person 5 23 596876401 600850801

Person 6 87 -1422809999 -1422723599

 

I came up with the following query to search between a minimum and maximum age (using AJAX):

 

//user enters a minimum and/ or max age - getting the querystring from URL (ajax)
$minAge = $_GET['minAge'];
$maxAge = $_GET['maxAge'];

//age to unix timestamp
function agetostamp($age){
$gebjaar = date(Y) - $age;
$timestamp = mktime (0,0,1,0,0,$gebjaar);	
return $timestamp;
}

//making getvariables timestamp in order to compare with database
if (isset ($minAge)){ 
$minAge = agetostamp($minAge);
}
if (isset ($maxAge)){ 
$maxAge = agetostamp($maxAge);
}

//building query
query = "SELECT * FROM respondenten WHERE geslacht = '$sex'";

if(is_numeric($minAge) && $minAge > 0) 
$query .= " AND geboortedatum <= '$minAge'";
else if (is_numeric($minAge)) 
$query .= " AND geboortedatum >= '$minAge'";

if(is_numeric($maxAge) && $maxAge > 0) 
$query .= " AND geboortedatum <= '$maxAge'";
else if (is_numeric($minAge)) 
$query .= " AND geboortedatum >= '$maxAge'";

 

I encouter a lot of troubles working like this:

 

1) if i leave out the part of maxAge (so i only allow to set a minimum age in the search box) it works except for the fact that if i set the minimum age to 23, person 5 will not be included since 596876401 < 600850801 even though she is 23 and should be included.

 

2) if i add the part of also searching on a max age, i don't get any results

 

I know that remark 1 probably has to do with the fact that im using date(Y) - $age and that people that are already born this year will have a timestamp higher than the one calculated in the query and visa versa.

 

conclusion: i don't think i'm going to get out of this so i'm hoping sombody can help me, telling me how to build a query for selecting on age range

 

 

[xyph]

You probably want to use strtotime

 

$ageStamp = strtotime( '-14 years' );

 

... will generate the timestamp for 14 years ago today.

 

This isn't the best solution though. Ideally, you change your database to implement a MySQL datetime column, and perform all of the checks within MySQL. It's quite a bit faster.

[RussellReal]

#1, why are you storing your date values in mysql as timestamps, when you could use a DATETIME type, and use the date functions in mysql..

 

#2, why aren't you using mktime like this:

<?php
$N = 23;
$day = date('j');
$month = date('n');
$year = date('Y');
// get exactly $N years ago
$year = $year - $N;
$stamp = mktime(0,0,0,$month,$day,$year);
?>

[stijn0713]

because i'm starting to learn php and because my friends who does it for longer says it's better to use it instead of datetime() . I tried to look up the advantages of both but i dont see it yet.

 

 

[stijn0713]

Ok, eventually i just did it like this:

 

$minAge = time() - $_GET['minAge']*3600*24*356;
$maxAge = time() - $_GET['maxAge']*3600*24*356;

 

which works fine. Only if i leave the input fields blank it takes a empty string but when converting it it stamps it to 1331586280 which corresponds to 2012 (year of now) mm...

 

[xyph]

MySQL's date functions are faster than PHP's, which are notoriously slow.

 

In the end it doesn't REALLY matter for a problem this simple. Here's what it'd look like if you used a MySQL datetime column.

<?php

// Make sure variables are safe for queries
// This makes sure the value is set before trying to use it (avoid undefined key notices)
// And also makes sure it only contains digits. If either of the checks fail, the variable
// is assigned as blank.
$minAge = isset($_GET['minAge']) && ctype_digit($_GET['minAge']) ? $_GET['minAge'] : '';
$maxAge = isset($_GET['maxAge']) && ctype_digit($_GET['maxAge']) ? $_GET['maxAge'] : '';

$sex = 'male';

$query = "SELECT * FROM respondenten WHERE geslacht = '$sex'";
if( $minAge != '' )
$query .= ' AND dateofbirth < DATE_SUB(NOW(),INTERVAL '.$minAge.' YEAR)';
if( $maxAge != '' )
$query .= ' AND dateofbirth > DATE_SUB(NOW(),INTERVAL '.$maxAge.' YEAR)';

echo $query;
?>

 

You no longer have to worry about any conversions. You simply use the built in MySQL date and time functions. The bulk of my code above is sanitizing the incoming data.