[RESOLVED] Searching DB for members of specific DOB

_sh

Its been a long day and I just can't get my head around this, perhaps I need some time out, but thought I'd post ehre to see if someone can make me see the lights!!

I have a DB with a Date Of Birth column (MySQL DATE), On the site in question I'm offering a search facility to find members within an AGE range - its not going to be perfect but it'll be fairly accurate, unless someone can suggest a better way.

So, I have two variables, DateRangeFrom, DateRangeTo, consisting of an age dropdown, from 16-80 years old.

Here's the WHERE clause I devised as a start, although it doesn't take into account the month/days, but it's a rough estimate based on the year alone but its bringing back everyone still, so I'm clearly doing something wrong, I jsut cannot get my head around it!!

WHERE (MemberDOB > DATE_SUB(CURDATE(),INTERVAL 56 YEAR) AND MemberDOB > DATE_SUB(CURDATE(),INTERVAL 63 YEAR))

Hopefully someone can help with a better solution?...and one that works lol!

SunsetKnight

ashleyhall;10910379 wrote:
WHERE (MemberDOB > DATE_SUB(CURDATE(),INTERVAL 56 YEAR) AND MemberDOB > DATE_SUB(CURDATE(),INTERVAL 63 YEAR))

Your current WHERE clause can be logically thought as:
Return anything WHERE*:

MemberDOB > 1953-04-07
AND
MemberDOB > 1946-04-07

*as of today

So...
This current query is going to return all records for anyone born after 1953, this means anyone 56years or younger will be included in that return. I'm not sure what your looking for but that is what that will do.

_sh

Thanks for your reply, sorry it was late and I wasn't making a lot of sense last night lol.

Heres the SQL again along with the variables the website user can customise...

WHERE (MemberDOB > DATE_SUB(CURDATE(),INTERVAL $MemberAgeLimitFrom YEAR) AND MemberDOB > DATE_SUB(CURDATE(),INTERVAL $MemberAgeLimitTo YEAR))

So basically I'm allowing the thats searching to find all members within the limit they set via $MemberAgeLimitFrom and $MemberAgeLimitTo, but my SQL doesn't appear to be working?

For instance, here's another test query I just ran...

 WHERE (MemberDOB > DATE_SUB(CURDATE(),INTERVAL 16 YEAR) AND MemberDOB > DATE_SUB(CURDATE(),INTERVAL 18 YEAR))

So in theory that'll return everyone thats BETWEEN 16 and 18 YEARS OLD right? Well it brought back these results...

Becky 1998-01-24
Carl 2009-03-18
Danielle 1993-07-26
Joe 2008-01-01
Mark 2001-03-04
Niki 1993-08-26
Test 2007-02-02

Thats not right, especially the 'Test' row at the end?

SunsetKnight

ashleyhall;10910467 wrote:
For instance, here's another test query I just ran...
 WHERE (MemberDOB > DATE_SUB(CURDATE(),INTERVAL 16 YEAR) AND MemberDOB > DATE_SUB(CURDATE(),INTERVAL 18 YEAR))
So in theory that'll return everyone thats BETWEEN 16 and 18 YEARS OLD right? Well it brought back these results...

Becky 1998-01-24
Carl 2009-03-18
Danielle 1993-07-26
Joe 2008-01-01
Mark 2001-03-04
Niki 1993-08-26
Test 2007-02-02

Thats not right, especially the 'Test' row at the end?

No, that is going to return anyone 18 Years or younger, which as you can see it did. The problem lies in your logic. Like above, again your asking if DOB is greater then 16 AND greater then 18.

If your wanting results for in between those years, you would want greater then or equal to 16 AND less then or equal to 18.

Give this a try:

 WHERE (MemberDOB >= DATE_SUB(CURDATE(),INTERVAL 16 YEAR) AND MemberDOB <= DATE_SUB(CURDATE(),INTERVAL 18 YEAR))

_sh

Thanks again for your reply, but no that didn't work! I set the age range to 18-60 but it found no one at all!!??

Here's the complete SQL...

SELECT MemberID, MemberFirstName, MemberLastName, MemberDOB
FROM tbl_members_main
WHERE (MemberDOB >= DATE_SUB(CURDATE(),INTERVAL 18 YEAR) AND MemberDOB <= DATE_SUB(CURDATE(),INTERVAL 60 YEAR)) ORDER BY MemberFirstName ASC, MemberLastName ASC

Here's a list of some of the DOB's in the DB...

2008-01-01
1981-03-04
1983-04-25
1983-07-29
1980-04-01
2001-03-04
1962-04-27
1991-07-01
1960-09-24
1952-08-07
1980-08-08
2007-02-02
1988-01-27
1993-08-26
1974-04-06
1971-11-09
1968-11-08
1963-02-06
1972-12-25
1962-01-21

SunsetKnight

Doh! :o

That's what I get for posting before I have my morning coffee.

I have my logical tests backwards, this should work:

WHERE (MemberDOB <= DATE_SUB(CURDATE(),INTERVAL 18 YEAR) AND MemberDOB >= DATE_SUB(CURDATE(),INTERVAL 60 YEAR))

This tests:

DOB <= 1991
AND
DOB >= 1949

Returning anyone 18-60 years old

Previously I was testing:

DOB >= 1991
AND
DOB <= 1949

Returning anyone 18years or younger, but also testing for 60years and older...which doesn't make logical sense heh.

_sh

You must have had a long day, your new example's wrong too lol (the first comparison symbols are wrong way around) but I took your example's afterwards and its all working perfectly!!

Many thanks for your VERY quick help/advice!!

SunsetKnight

Haha well I edited it for further readers, your welcome. I really should had it fixed by the first post though.

Cheers!

_sh

SunsetKnight;10910510 wrote:
Haha well I edited it for further readers, your welcome. I really should had it fixed by the first post though.

Cheers!

No probs matey, you were a great help!! Thanks again!