Page 1 of 1

Select All Records Between Two Dates (Combo PHP & mySQL)

Posted: Mon Feb 06, 2006 10:38 am
by cfytable
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.

Posted: Mon Feb 06, 2006 10:44 am
by hawleyjr
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

Posted: Mon Feb 06, 2006 10:45 am
by feyd
although it likely won't fix it, look at the BETWEEN keyword.

Code: Select all

`serviceDate` BETWEEN '1986-12-09' AND '2005-11-08'
If you remove "a million critieria" to leave just the dates, does it work? If so, you have a tainting issue with that criteria.

Posted: Mon Feb 06, 2006 10:49 am
by onion2k
I'm awfully fussy about this, but && is not actually part of the SQL standard.. mysql adds is as a synonym for AND. Using it reduces the portability of your code.

Posted: Mon Feb 06, 2006 11:22 am
by cfytable
Thanks for the help--I tested it further, and it looks like the rest of the sql is tainted. I'm going to post a separate topic and see if I can get some help with that angle. Thanks again.