Jump to content


Photo

Searching based on a date field


  • Please log in to reply
5 replies to this topic

#1 Bhaal

Bhaal
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 22 March 2006 - 01:16 PM

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!

#2 azuka

azuka
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts
  • LocationNigeria

Posted 22 March 2006 - 02:17 PM

I'd do something like:

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

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


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



Given enough eyeballs all bugs are shallow. My Website

#3 Bhaal

Bhaal
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 22 March 2006 - 03:44 PM

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

#4 Bhaal

Bhaal
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 22 March 2006 - 10:22 PM

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?

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 23 March 2006 - 08:06 AM

$n1 = 20;
$n2 = 25;

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

then

SELECT ...... WHERE out BETWEEN '$d1' AND '$d2'

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 Bhaal

Bhaal
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 24 March 2006 - 02:01 PM

Thank you SO much Barand.

The implemented solution:

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' ";
}

I love PHP Freaks!








0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users