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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cfytable
Forum Commoner
Posts: 29
Joined: Thu May 12, 2005 3:36 pm

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

Post 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.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
cfytable
Forum Commoner
Posts: 29
Joined: Thu May 12, 2005 3:36 pm

Post 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.
Post Reply