Age from birthdate

[El Chupacodra]

I thought I'd never have to ask another date related question after the help I got from Pikachu earlier this month but I can't get this simple thing to work.

I have a table post for Date of Birth (Format YYYY-MM-DD) and a search form where you select From and To Numbers to find users between these ages.

 

I have tried a few different things - this one works best, if I choose from any number and up til current time I get results but as soon as I set the script to use the selected variable I bomb. Also, somehow I got the script to only return the users with a DOB of 0000-00-00.

 

Here is the current version (shortened to show the relevant parts):

 

$now = time(); //current timestamp
$now=(date("Y-m-d"));

if (isset($_POST['ageFr']))  //sets the stopdate for the search
$ageFr = sanitizeString($_POST['ageFr']);
$yearStop="date_sub($now, INTERVAL $ageFr YEAR)";

if (isset($_POST['ageTo']))  //sets startdate for the same search
$ageTo = sanitizeString($_POST['ageTo']);
$yearStart="date_sub($now, INTERVAL $ageTo YEAR)";


$query = "SELECT * FROM user_criteria WHERE sex='$sex' AND (age BETWEEN ($yearStart) AND ($yearStop))";	//The query

 

When I echo like this:

echo "$ageFr to $ageTo <br />";

echo "$yearStart - $yearStop";

echo "<br /> $query";

 

I get this:

18 to 101

date_sub(2012-01-24, INTERVAL 101 YEAR) - date_sub(2012-01-24, INTERVAL 18 YEAR)

SELECT * FROM user_criteria WHERE sex='Female' AND (age BETWEEN (date_sub(2012-01-24, INTERVAL 101 YEAR)) AND (date_sub(2012-01-24, INTERVAL 18 YEAR)))

 

Shouldn't that work? Am I making a big or a small mistake here? Pikachu? 

[Pikachu2000]

From here: http://ma.tt/2003/12/calculate-age-in-mysql/

 

// Where 'dob' is field containing date of birth
SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age

[El Chupacodra]

Thanks, I will gladly give that a try this afternoon.

Interesting read no matter what!

[El Chupacodra]

I eventually got it working this way, had a tough time passing my variables to it but I will give it another go shortly.

Thanks again Pikachu.