（PHP）在数据库中搜索年龄时提取正确的生日

Question

In my database I have a field with a set birthdate (e.g. 1989.08.10 (YYYY.MM.DD)). I also have a function that converts this info to the correct year (e.g. 17, 22, 30 years). In the search engine I'm making I have a query built up from the various inputs which are required by the user, whereas two of these are the age, MIN and MAX. For example, select all ages between 18-25.

My problem is that I'm not sure how I'm supposed to get the finished results as I can't imagine how to add it to a query. I presume I will have to make two WHILE loops somehow which would look like this for the finished result:

while($row = mysql_fetch_array($query){
$age = convertFunc($row['birthdate']);
while(XXXXX){
SOME CODE HERE
}
}

I'm quite lost on this, but to clarify. Exactly what I want is something similar to this in a search query against the database: SELECT * FROM users WHERE user = '$name' AND fromAge >= 18 AND toAge <= 25

This would select all the fields with the corresponding age to MIN and MAX.

Answer 1

Depending on your database structure, you could use DATEDIFF in your SQL statement. Link for reference

Answer 2

This is why it's good to save all dates as timestamps and not actual dates, as it's much easier to find a range between two numeric values.

You can try something like this with your current setup to find all people between 18-25:

$age1 = 18; //These two would be external inputs from whatever search form
$age2 = 25;

$date1 = date("Y.M.d",time() - ($age1 * 365 * 24 * 60 * 60));//Subtract the number of seconds equivalent to X years from current timestamp, Convert timestamps to appropriate dates for the format in the DB
$date2 = date("Y.M.d",time() - ($age2 * 365 * 24 * 60 * 60));//Strtotime("18 years ago") might work, not sure

$sql = mysql_query("SELECT * FROM `users` WHERE `birthdate` >= '$date1' AND `birthdate` <= '$date2'");
$result = mysql_fetch_assoc($sql);

Answer 3

try something like.

select * from users where datediff(birthdate,now()) between date_add(now(),interval -15 years) and date_add(now(),interval -25 years)