Help with age and dob in query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
newbie2php
Forum Commoner
Posts: 35
Joined: Wed Nov 07, 2007 4:44 pm

Help with age and dob in query

Post by newbie2php »

Hi all!

Have a few issues with a query I am trying to run on a search page.

Bqsically we have date of births in the database. We want peoeple to be able to browse people in the database of certain ages. So we pass an age range int he URL ans use the GET data to compose the query string

Here is a snippet of the code (it is all in the switch query)

Code: Select all

$start_date = (date('Y')-22).date('-m-d');
    $end_date = (date('Y')-29).date('-m-d');
    $search_string .= "AND dob >= '".$start_date."' AND dob < '".$end_date."' ";
But this will not work as the data is not numeric because of the '-' within the dob format!

I am unsure how I can get this to work?

Any ideas guys?

Thanks
User avatar
HCBen
Forum Commoner
Posts: 33
Joined: Thu Jun 22, 2006 3:15 pm
Location: Indiana

Re: Help with age and dob in query

Post by HCBen »

Look into using the CAST or CONVERT SQL function to convert the dob column to a valid date.

In your select clause, do something like this example:

Code: Select all

 
SELECT CAST(dob AS DATE) AS dateofbirth
FROM mytable
WHERE dateofbirth >= '".$start_date."' AND dateofbirth < '".$end_date."'
 
You can then use standard ("mm/dd/yyyy" or "yyyy/mm/dd") dates for your $start_date and $end_date variables.

Also, you may want to think about using the BETWEEN expression instead:

Code: Select all

 
SELECT CAST(dob AS DATE) AS dateofbirth
FROM mytable
WHERE dateofbirth BETWEEN '".$start_date."' AND '".$end_date."'
 
I may not have the syntax exactly right but this should give you the general idea of what to try. Check the documentation for your database for specifics regarding the CAST and CONVERT functions.

I hope this helps.

Prost!
-Ben
Post Reply