www.webdeveloper.com
Results 1 to 11 of 11

Thread: Date comparison in MySQL and PHP

Hybrid View

  1. #1
    Join Date
    May 2009
    Posts
    17

    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.

    PHP Code:
        $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.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,334
    You need to put single quotes around the two date variables in the query. (They are strings, not numbers.)
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Dec 2010
    Posts
    33
    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 ?

  4. #4
    Join Date
    May 2009
    Posts
    17
    @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".

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,334
    Is submitted a date, datetime, or timestamp type of column?

    Also, you might want to consider using the BETWEEN...AND operators.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  6. #6
    Join Date
    May 2009
    Posts
    17
    @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:

    PHP Code:
    $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.

  7. #7
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,334
    Maybe try forcing the values to be treated as dates?
    PHP Code:
    "...DATE(submitted) BETWEEN '$startdate' AND '$enddate'" 
    or...
    PHP Code:
    "...DATE(submitted) BETWEEN STR_TO_DATE('$startdate', '%Y-%m-%d') AND STR_TO_DATE('$enddate', '%Y-%m-%d')" 
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  8. #8
    Join Date
    May 2009
    Posts
    17
    @NogDog
    I've tried both of these suggestions and neither of them worked for me.

  9. #9
    Join Date
    Jan 2006
    Location
    MN
    Posts
    440
    I have done date relevant queries like this;

    PHP Code:
    $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.

  10. #10
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,334
    Afraid I'm stumped. Seems like it should work, unless there's a silly logic error we're not seeing.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  11. #11
    Join Date
    Jan 2009
    Posts
    3,346
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles