Date comparison in MySQL and PHP

This site is best viewed in a modern browser with JavaScript enabled.

whirlwind

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.

	$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)");

The dates stored in the database were saved as TIMESTAMP.

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.

Please help.

NogDog

You need to put single quotes around the two date variables in the query. (They are strings, not numbers.)

SyncUpSolutions

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 ?

whirlwind

@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".

NogDog

Is submitted a date, datetime, or timestamp type of column?

Also, you might want to consider using the BETWEEN...AND operators.

whirlwind

@NogDog

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:

$list = mysql_query("SELECT id FROM collection WHERE (category= 'subject') AND (submitted BETWEEN $startdate AND $enddate)");

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.

NogDog

Maybe try forcing the values to be treated as dates?

"...DATE(submitted) BETWEEN '$startdate' AND '$enddate'"

or...

"...DATE(submitted) BETWEEN STR_TO_DATE('$startdate', '%Y-%m-%d') AND STR_TO_DATE('$enddate', '%Y-%m-%d')"

whirlwind

@NogDog
I've tried both of these suggestions and neither of them worked for me.

Dasher

I have done date relevant queries like this;

$query = "SELECT ID, Title, Category, Priority, Sticky, Author, More, Introtext,UCASE( DATE_FORMAT(Publish_Up,'&#37;b %d, %Y %l:%i %p'))AS publishDate, Publish_Dwn, URLS 
FROM content 
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;

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.

NogDog

Afraid I'm stumped. Seems like it should work, unless there's a silly logic error we're not seeing.

criterion9

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.