Jump to content

Archived

This topic is now archived and is closed to further replies.

Bhaal

Searching based on a date field

Recommended Posts

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!

Share this post


Link to post
Share on other sites
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!


Share this post


Link to post
Share on other sites
OK - I'll definitely try this...when I get some sleep! Thanks for the help.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
[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]

Share this post


Link to post
Share on other sites
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!



Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.