Search age range based on birthdate

[AshleighCo]

I have an advanced search from that has 20 fields and it works great however I'm having a problem with customising the age range search based on the $birthdate field (YYYY/MM/DD). The user inputs $agefrom and $ageto and then I don't know how to convert the birthdate field to an age to be able to echo only the users in that age range...
 

<?php
$cQuery = 'true and ';
$aSearchcriteria = array();

//more code here...(other search input that works)

if(isset($_POST['agefrom']) and trim($_POST['agefrom'])  and strip_tags ($_POST['agefrom']) and mysql_real_escape_string ($_POST['agefrom'] ))
if(isset($_POST['ageto']) and trim($_POST['ageto'])  and strip_tags ($_POST['ageto']) and mysql_real_escape_string ($_POST['ageto'] )) {
             
  $cQuery .= "$birthdate BETWEEN '$agefrom' AND '$agefrom' and ";
  $aSearchcriteria[] = $agefrom;
  $aSearchcriteria[] = $ageto; 
}

//more code here...(other search input that works)

$data = mysql_query("SELECT * FROM table WHERE $cQuery order by name, surname") or die("SELECT Error: ".mysql_error());

// more code here

// table echo here...
?>

[AshleighCo]

I tried adding:

$date1 = date("Y.M.d",time() - ($agefrom * 365 * 24 * 60 * 60));
$date1 = date("Y.M.d",time() - ($ageto * 365 * 24 * 60 * 60));

but it didn't work...

[AshleighCo]

Not to worry.....I'm looking into getting an IT guy to help onsite with my coding...thanks anyway.

[Barand]

You don't like to hang about for a reply, do you?

 

Calculate age by subtracting year of birth from year now, but if you haven't yet reached your birthday this year then subtract 1. Simples!

 

For example

SELECT thedate
, CASE WHEN DATE_FORMAT(CURDATE(),'%m%d') >= DATE_FORMAT(thedate,'%m%d')
    THEN YEAR(CURDATE()) - YEAR(thedate)
    ELSE YEAR(CURDATE()) - YEAR(thedate) - 1
    END as age
FROM dates
WHERE 
    CASE WHEN DATE_FORMAT(CURDATE(),'%m%d') >= DATE_FORMAT(thedate,'%m%d')
    THEN YEAR(CURDATE()) - YEAR(thedate)
    ELSE YEAR(CURDATE()) - YEAR(thedate) - 1
    END 
    BETWEEN $agefrom AND $ageto

[Psycho]

Wouldn't it be simpler to just modify today's date using the age_min and age_max values to determine the date ranges and use a BETWEEN operator? Not sure if it would be more efficient to calculate those dates in PHP or MySQL.

[Barand]

There you go again, always looking for the easy way out :-)

[Psycho]

Here's some PHP code to determine the date range. It's a little odd since you use the lower age to determine the max date and the upper age to determine the min date. Also, for the min date, you have to go back age + 1 years and then increment +1 days. That's because a 40 year old person could be someone who's 40th birthday is today up to someone who is 40 years and 364 days old.

 

 

$min_age = 20;
$max_age = 40;
 
$max_date = strtotime("-{$min_age} years");
$max_date_str = date('Y-m-d', $min_date);
 
$max_age_offset = $max_age +1;
$min_date = strtotime("-{$max_age} years");
$min_date = strtotime("+1 day", $min_date);
$min_date_str = date('Y-m-d', $min_date);
 
echo "For the user to be from {$min_age} to {$max_age} years old they must
      have been born between {$min_date_str} and {$max_date_str}";