Extracting records by date.

Posted: Sat Dec 10, 2005 9:09 am

I'm trying to extract records based on expiry date 30 days and 60 days ahead. Here is the snippet from my file. Can someone please help me sort this out? Perhaps it's staring at me and I just don't see it.

Code:

$today= time();                 //stores today's date





$thirtydays = date("Y.m.d", strtotime ("30 days"));


//$sixtydays = date("Y.m.d", strtotime ("60 days"));


echo ("Thirty Days from today is: $thirtydays<br>");


//echo ("Sixty Days from today is: $sixtydays");





// The following query *does* work, BUT I need the date after AND to be a variable, ie: $thirtydays or $sixtydays





//$query="SELECT * from inner_circle WHERE future_expire BETWEEN NOW() AND '2006-01-10'";





// The following doesn't work. :(





$query="SELECT * from inner_circle WHERE future_expire BETWEEN '$today' AND '$thirtydays'";

How can I rewrite that last query so it will work?
Thank you, oh great wise mysql gurus. I've searched high and low for the answer, but haven't got anywhere useful.

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

What format is future_expire in?

Posted: Sun Dec 11, 2005 8:18 am

Thanks Raven.

future_expire is a mysql date format.

(sorry 'bout the day delay for my response. Two minutes after I clicked the submit button Christmas chaos was unleashed and I was swept out of here to spend all day at the mall.)

Posted: Sun Dec 11, 2005 9:23 am

Would you do me a favor and post a record as it looks in the database, with the schema?

Posted: Sun Dec 11, 2005 10:15 am

Here is the export for my test table.

Quote:

CREATE TABLE `inner_circle` (
`icid` int(5) NOT NULL auto_increment,
`realname` varchar(25) NOT NULL default '',
`location` text NOT NULL,
`member_number` varchar(15) NOT NULL default '',
`sysnick` varchar(25) NOT NULL default '',
`email` varchar(50) default NULL,
`orig_join` date NOT NULL default '0000-00-00',
`future_expire` date NOT NULL default '0000-00-00',
`notes` text,
`welcome` int(1) NOT NULL default '0',
`admin_nick` varchar(25) NOT NULL default '',
PRIMARY KEY (`icid`),
FULLTEXT KEY `realname` (`realname`,`member_number`,`sysnick`,`email`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

--
-- Dumping data for table `inner_circle`
--

INSERT INTO `inner_circle` VALUES (1, 'test', 'somewhere', '03TES0', 'Test', 'test@somedomain.com, '2003-05-01', '2006-05-01', 'test', 1, 'Cody');

Posted: Sun Dec 11, 2005 11:35 am

This should work

$int30 = 30;
$int60 = 60;
$query = "SELECT * from inner_circle where future_expire between now() and DATE_ADD(now(), INTERVAL $int30 DAY)";
$query = "SELECT * from inner_circle where future_expire between now() and DATE_ADD(now(), INTERVAL $int60 DAY)";

Posted: Sun Dec 11, 2005 11:59 am

It works! huge hugs

I learn so much from you.

Posted: Sun Dec 11, 2005 12:03 pm