MySQL age search

luiddog

I have two fields age1 (start age) and age2 (end age). I'm trying to have users input the start age and end age and return all the users within those ages. I have the users birthday stored as month and year. So a users age is stored in a 'date' column named 'bdate' as 1975-04-00. Any ideas for the SELECT statement?

Piranha

With a 'date' column, do you mean a column with the type date? It makes a BIG difference if it is of type date (or datetime or timestamp) or something else like, say, varchar.

You could try using between.

luiddog

Yes the column type is date, not datetime or timestamp or varchar

bradgrafelman

$query = "SELECT username, whatever FROM myTable WHERE bdate BETWEEN '$age1' AND '$age2'";

Don't forget to escape age1 and age2 if they're POSTed values!

luiddog

But $age1 is something like 25 and $age2 is something like 35. The bdate column is a 'date' field as 1977-05-01. How can BETWEEN work with a date column?

bradgrafelman

Woops, guess I didn't read your post very carefully.

In that case, you might try something like:

$query = "SELECT username, whatever
FROM myTable
WHERE bdate BETWEEN DATE_SUB(CURDATE(), INTERVAL $age1 YEAR)
    AND DATE_SUB(CURDATE(), INTERVAL $age2 YEAR)";