Searching based on a date field

[Bhaal]

Hey all,

I have a data field defined as DATE that holds dates in the future...and I'd like to search for records based on this date - with a slight twist.

On the search form, there is a drop down with these values:
6 months or less
6 months to 1 year
1 to 2 years
2 to 5 years
5 to 10 years
10 to 15 years
15 to 20 years
20 to 25 years
25 to 30 years
Over 30 years

What I need to do is search for the date range chosen from this drop down based on the DATE field; i.e. all records where the DATE field is 6 months from now, or 3 years from now, or 23 years from now, or 50 years from now...you get the idea.

I'm guessing that the option value in the menu is the key but how should this be formatted?

<select name="retirementdate">
<option value="0-0.5">6 months or less</option>
<option value="0.5-1">6 months to 1 year</option>
<option value="1-2">1 to 2 years</option>
<option value="2-5">2 to 5 years</option>
<option value="5-10">5 to 10 years</option>
<option value="10-15">10 to 15 years</option>
<option value="15-20">15 to 20 years</option>
<option value="20-25">20 to 25 years</option>
<option value="25-30">25 to 30 years</option>
<option value="30-500">Over 30 years</option>
</select>

SELECT * from DB where DATE => '$_GET[date1]' and DATE <= '$_GET[date2]';

I dunno - just guessing here...

I've searched the forums for something like this with no luck.

Any help is greatly appreciated!

[azuka]

I'd do something like:

[code] $retirement = explode("-",$_GET['retirementdate']);

$sql = "SELECT * from DB where `DATE` >= '" . $retirement[0] . "' and `DATE` <= '" . . $retirement[1] . "'";

[/code]

Remember DATE is a mysql keyword -- always enclose in backslashes. Good luck!

[Bhaal]

OK - I'll definitely try this...when I get some sleep! Thanks for the help.

[Bhaal]

OK, I've slept some and tried the above approach. Didn't work.

I assumed that by 'DATE' you meant the field in the db that holds the future date.

The clause for the query ends up:

users.out >= '5' and users.out <= '10'

('users' is the table and 'out' is the date field.)

Since 'out' holds an actual date (like '2010/04/24'), asking it it's >= 5 and <=10 won't make sense.

I think there needs to be some sub function for calcing the number of years/months/days from now the future date is, then comparing them to the search range.

But I think that's a little beyond my quite meager skills. Any suggestions?

[Barand]

[code]$n1 = 20;
$n2 = 25;

$d1 = date('Y-m-d', strtotime ("+$n1 years"));
$d2 = date('Y-m-d', strtotime ("+$n2 years"));[/code]

then

[code]SELECT ...... WHERE out BETWEEN '$d1' AND '$d2'[/code]

[Bhaal]

Thank you SO much Barand.

The implemented solution:

[code]
if(!empty($_GET[retirementdate]))
{
    $n = explode("-",$_GET['retirementdate']);
    $d1 = date('Y-m-d', strtotime ("+$n[0] years"));
    $d2 = date('Y-m-d', strtotime ("+$n[1] years"));
    $query[] = " members.out BETWEEN '$d1' AND '$d2' ";
}
[/code]

I love PHP Freaks!