Date comparison in MySQL and PHP
I'm sure my issue is simple to resolve, but I've had no luck. I want to retrieve a set of information from a database for rows that were submitted between two dates. These two dates will be provided by the user in a form and saved in the variables $start and $end.
The dates stored in the database were saved as TIMESTAMP.
$start = strtotime($_POST['start']);
$startdate = date("Y-m-d", $start);
$end = strtotime($_POST['end']);
$enddate = date("Y-m-d", $end);
$list = mysql_query("SELECT id FROM collection WHERE (category= 'subject') AND ($startdate < submitted) AND (submitted < $enddate)");
Echoing out the answer gives me 0 every time regardless of the answer I'm expecting. I really can't see what I'm doing wrong.
You need to put single quotes around the two date variables in the query. (They are strings, not numbers.)
Have you echoed the POST data after you convert them with the strtotime() to see what they are, then compare them to the ones in the database manually ?
@NogDog; I've tried putting single quotes around $startdate and $enddate and that doesn't work.
@SyncUpSolutions; I've echoed out the dates elsewhere so I can check the range and they show up as I expect them to, with the entered strings converted into dates, e.g. "tomorrow" becomes "08-12-2010".
Is submitted a date, datetime, or timestamp type of column?
Also, you might want to consider using the BETWEEN...AND operators.
The database column submitted is set to store data as TIMESTAMP.
I was originally using BETWEEN and AND for my query, but they didn't work for me either, so I swapped them for the greater than and less than operators so that I as I edited the two separate statements the would be completely independent of each other and I could clearly see if it made any difference.
It previously looked like this:
My problem seems to be that the script is reading the value in the database as later than both my submitted dates even when it isn't. If I remove one of the entered values completely, all of the db information where category= 'subject' shows up, regardless of dates.
$list = mysql_query("SELECT id FROM collection WHERE (category= 'subject') AND (submitted BETWEEN $startdate AND $enddate)");
Maybe try forcing the values to be treated as dates?
"...DATE(submitted) BETWEEN '$startdate' AND '$enddate'"
"...DATE(submitted) BETWEEN STR_TO_DATE('$startdate', '%Y-%m-%d') AND STR_TO_DATE('$enddate', '%Y-%m-%d')"
I've tried both of these suggestions and neither of them worked for me.
I have done date relevant queries like this;
The DATE_ADD compensates for the server time zone which is one hour than local time. I have the date fields specified as "datetime" however.
$query = "SELECT ID, Title, Category, Priority, Sticky, Author, More, Introtext,UCASE( DATE_FORMAT(Publish_Up,'%b %d, %Y %l:%i %p'))AS publishDate, Publish_Dwn, URLS
WHERE Category = 1 AND Publish_Up <= DATE_ADD(NOW(),INTERVAL 1 HOUR) AND (Publish_Dwn > DATE_ADD(NOW(),INTERVAL 1 HOUR) OR Sticky=1) AND Priority > 0
ORDER BY sticky DESC, priority ASC, id DESC LIMIT ".$show;
Afraid I'm stumped. Seems like it should work, unless there's a silly logic error we're not seeing.
Can you provide the schema and a few sample rows? I'm curious that if by replicating the problem with a much smaller dataset the answer will be easier to spot.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread