comparing dates / times

ali_p

I have 2 table fields starttime and endtime that store date/times in datetime format. Now I am trying to run a query to check if there are any events in the table with the same start time and end time as a new record to prevent any overlap of schedule. however this is not currently working for me. Here is my syntax

$sqlcheck = "SELECT * from `listings`
        WHERE  StartTime >= '".php2MySqlTime(js2PhpTime($st))."' AND EndTime <= '".php2MySqlTime(js2PhpTime($et))."'
        ";

So I'm checking to see if there are any records which have the same starttime or greater and the same endtime or less. Not working at all.

Krik

Hopefully the problem isn't in "php2MySqlTime(js2PhpTime($st))" as no one here will know what that outputs.

But beyond that your logic has a fallacy.

If you have an event that starts 2010-08-28 12:00:00. And if you wish to enter a new event that start 2010-08-27 14:00:00. Then

WHERE  StartTime >= '".php2MySqlTime(js2PhpTime($st))."'

will find a record that is greater than the new event

And the revers holds true for end time. In fact end time is worse as all your old event end time will always be less than a new events end times.

So if that query is preventing double booking once a single event is entered it will prevent all other events form being entered. In other words, you will never be able to enter a second event.

You need your query to do 2 checks is need to see if the new records start time is less than any other records start time and greater then any other records end time or if the new records end time is greater than any other records start time and less than any other records end time.

$sqlcheck = "SELECT * from `listings`
        WHERE  StartTime >= '".php2MySqlTime(js2PhpTime($st))."' AND EndTime <= '".php2MySqlTime(js2PhpTime($st))."' OR StartTime >= '".php2MySqlTime(js2PhpTime($et))."' AND EndTime <= '".php2MySqlTime(js2PhpTime($et))."'
        ";

But even this will not work as both end times checks will always return true as well as any record that starts further in the future than a new record will cause the start time checks to return true.

I recommend that you use BETWEEN

$sqlcheck = "SELECT * FROM `listings`
        WHERE  StartTime BETWEEN '".php2MySqlTime(js2PhpTime($st))."' AND '".php2MySqlTime(js2PhpTime($et))."' OR EndTime BETWEEN '".php2MySqlTime(js2PhpTime($st))."' AND '".php2MySqlTime(js2PhpTime($et))."'
        ";