[SOLVED] filtering according to age and DOB

[stephenk]

I have an age range coming in as a POST variable in the form a-b where a is the lower boundary and b is the upper one.

In my database, the DOB of various people are stored.

Can anyone tell me the BETWEEN query to select the people that are within the age range $lower and $upper, according to their date of birth?

 

I really hate working with dates!

 

Many thanks,

Stephen

[Wildbug]

(I'll assume you've been able to check the POST data for proper input and separated it into $lower and $upper.)

 

$query = "SELECT * FROM people_table WHERE dob_col BETWEEN CURDATE() - INTERVAL $upper YEAR AND CURDATE() - INTERVAL $upper YEAR";

[stephenk]

Thanks for your help.

 

However I'm running this query:

SELECT *
FROM tblpeople
WHERE `dob`
BETWEEN CURDATE( ) - INTERVAL 1 YEAR
AND CURDATE( ) - INTERVAL 10 YEAR

 

There are people in the table with DOBs ranging from 1983-05-05 to 2002-09-07 and the query isn't returning any records.

 

Stephen

 

Edit... Got it: I had got the upper and lower switched round - In your query above you had upper twice and I changed the wrong one.

 

Thanks for your help!!

[Wildbug]

Switch the numbers.  CURDATE() - INTERVAL 1 YEAR is going to be a later year than CURDATE() - INTERVAL 10 YEAR; you need to put the earlier year first.  Look where I put $upper and $lower in the query above.

 

mysql> SELECT CURDATE() - INTERVAL 10 YEAR, CURDATE() - INTERVAL 1 YEAR,'2002-09-07' BETWEEN CURDATE() - INTER
VAL 10 YEAR AND CURDATE() - INTERVAL 1 YEAR AS "Between?";
+------------------------------+-----------------------------+----------+
| CURDATE() - INTERVAL 10 YEAR | CURDATE() - INTERVAL 1 YEAR | Between? |
+------------------------------+-----------------------------+----------+
| 1997-06-25                   | 2006-06-25                  |        1 |
+------------------------------+-----------------------------+----------+
1 row in set (0.00 sec)

[stephenk]

Yeah, I just worked that out about a minute before you replied. You had actually used $upper twice but I changed the wrong one.

 

Gets a bit confusing sometimes!

 

Thanks again, you saved me alot of bother.

Stephen

[Wildbug]

Oops!  I probably typed the first $upper because I knew it should be first then typed the second one because it seemed like it should come second, having already forgotten that I alrea.....  you're right, it's confusing.