I'm working with a table that contains, among other info, the date on which the record was last edited/saved. The field is a TIMESTAMP field and the date is saved in the YYYY-MM-DD HH:MM:SS format. I need to go through the table daily to see which of the records are 305 days old (60 days from expiration). I've created a variable called $target_Ymd, which is the date that I'm looking for, and am working with this query, which gets me the user number/s associated with the record/s and the record number/s - $result_exp = mysql_query("SELECT fld_usrnum, fld_listingnum FROM tbl_kaplistings WHERE ( DATE(fld_reviewed) = $target_Ymd) ORDER BY fld_usrnum ASC"); Then I will go on to use the result set to create e-mail reminders to the responsible people. I have test data which includes a record with the timestamp "2011-11-02 14:59:00" and my target date is 2011-11-02. The above query, and several other variations of it, return NO records, even though there is at least the one that should qualify. Some days will have many more than one. If I drop the WHERE clause from the query, I get all of the records in the table, and I can then use the same DATE() function in an "if dates are equal then print" routine to print the one qualifying record that exists. I know I'm overlooking something applicable to douing this in the WHERE clause, but I've not been able to figure out what. I've never used a MySQL function in PHP, and I'm no PHP wizard at that. Comments or suggestions, please? Thank you... Steve E.
Treat the date as a string. The following should work: [COLOR=#111111]$result_exp = mysql_query("SELECT fld_usrnum, fld_listingnum FROM tbl_kaplistings WHERE ( DATE(fld_reviewed) = '$target_Ymd') ORDER BY fld_usrnum ASC"); Code (markup): [/COLOR]
Thank you, Alex! Works just as intended. As much as I fiddled with this, I didn't try this solution. Steve