Hello,

I am once again here to get help regarding php and mysql. Anyways here's my problem:

I have 2 dates in mysql db as date format (yyyy-mm-dd) and 1 user specified date in same format.

I want to check if the user specified date is equal or is in between the start date or end date in db. The date fields in db are called: sdate (startdate) and fdate (finishdate).

Please tell me how do i go about it ? I was trying the following query which does not seems to be working:

$q1 = "SELECT * FROM reservations WHERE
        $startd >= sdate AND $startd =< fdate";

Please help me.

Thanks

    Compare the two dates in a Unix timestamp format. This format measures time in the number of seconds past the Unix Epoch (January 1st of 1969 or somewhere thereabouts I believe).

    Look at using the MySQL function UNIX_TIMESTAMP() to convert those fields to timestamps (or, better yet, store the dates in that format to begin with) for easy comparison.

    EDIT: Correction on the date; seconds since '1970-01-01 00:00:00' GMT.

      Hi,

      I selected date type field when create table in mysql. To which type i set so it stores it as unix time stamp ?

      Thanks

        Well, when I store Unix timestamps, I usually create an 'UNSIGNED INT' field, and store the timestamp like so:

        INSERT INTO myTable (datetime, name, etc) VALUES (UNIX_TIMESTAMP(), 'blah', 'blah2');

          Hi,

          Oh well i got my solution with by using the following query:

          $q1 = "SELECT * FROM reservations WHERE
        UNIX_TIMESTAMP('$startd') >= UNIX_TIMESTAMP(sdate) AND
        UNIX_TIMESTAMP('$startd') <= UNIX_TIMESTAMP(fdate)";

          Thanks.

            You can find the Unix Timestamp each time of course. I just figured if you stored em as timestamps, you wouldn't be converting them in all your queries.

            Anywho, I'm glad you found a solution to your issue. If this is indeed the case, please don't forget to MARK THIS THREAD RESOLVED by clicking on the link at the bottom of the page.

              Write a Reply...