PHP：过滤：计算生日年龄，BETWEEN xx和xx

Question

After some research and little afford from myself I got this:

     $query .= "SELECT * FROM users WHERE birthday < SUBDATE(NOW(), INTERVAL BETWEEN $min AND $max YEAR)";

Although this doesn't work.

Users birthdays are stored in: 1991-01-01 in the birthday column. I have two fields in my form, that you can enter numbers in. example, 18 and 22.

then it will show all users that has the age between 18 and 22 ( 18,19,20,21,22).

^ And that is what im trying to filter out, with the query. But ive done something wrong, and maybe you cant do BETWEEN in INTERVAL, or what is wrong?

Im getting:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BETWEEN 17 AND 21 YEAR)' at line 1

Answer 1

But ive done something wrong, and maybe you cant do BETWEEN in INTERVAL, or what is wrong?

Exactly - BETWEEN is a boolean operator, which means it returns a boolean value (true or false), and INTERVAL wants an integer.

WHERE birthday
  BETWEEN SUBDATE(NOW(), INTERVAL $max YEAR)
      AND SUBDATE(NOW(), INTERVAL $min YEAR)

and a warning - you better have those variables properly escaped (with mysql_escape_string). Bobby Tables!

Also note that: using NOW() means that you would be including people born 17 years 23 hours ago, but excluding people born 18 years and 1 hour ago. That is not typically what we mean by "birthday" - you would better use TODAY() than NOW().

However, with TODAY(), there is another issue: BETWEEN is inclusive; thus, between 18 and 20, you do not have 2 years, but 2 years and a day. Overcome this by splitting BETWEEN up and doing normal comparison (birthday > ... AND birthday <= ...)