Date comparison in MySQL and PHP

[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,'%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.