In my mySQL database, I have a table with a number of records; each record has a "serviceDate," whose data type is "date".
In my PHP code, I want to build a sql statement that will select all the records from that table that fall between two dates. The start and end dates are dynamic and thus are set it in the PHP code. I currently do so in the following fashion:
$date01 = date("Y-m-d",strtotime("-90 days"));
$date02 = date("Y-m-d",strtotime("-7000 days"));
After building the sql in PHP, here's what it would look like, if outputted:
SELECT * FROM table WHERE ((a million different criteria) && ((serviceDate <= '2005-11-08') && (serviceDate > '1986-12-08')) ORDER BY serviceDate DESC
When I execute the sql, however, it doesn't pick up the right dates. For instance, I get records for 2006 showing up in the results.
Do I have to format the $date01, $date02 variables differently in PHP before I can do such a compare in sql? Any help would be appreciated.
Select All Records Between Two Dates (Combo PHP & mySQL)
Moderator: General Moderators
No need for PHP 
Code: Select all
SELECT * FROM table WHERE
((a million different criteria) &&
(serviceDate <= DATE_SUB(CURDATE(),INTERVAL 90 DAY) &&
serviceDate > DATE_SUB(CURDATE(),INTERVAL 7000 DAY) )
ORDER BY serviceDate DESC- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
although it likely won't fix it, look at the BETWEEN keyword.
If you remove "a million critieria" to leave just the dates, does it work? If so, you have a tainting issue with that criteria.
Code: Select all
`serviceDate` BETWEEN '1986-12-09' AND '2005-11-08'