使用PHP / MySQL datetime防止双重预订检查

Question

Struggling with a lesson booking sys I am creating. I have a date in the db named lesson_date_time and it uses the mysql datetime and each lesson will last for a set number of minutes. (I am not storing the end date as minutes vary.)

To prevent any double bookings from the datetime stored plus required minutes, I can't seem to figure out the sql which will do a check to see if this time period exists

I currently have...

$mins = "60";
$date_start = "2014-10-30 12:40:00";
$date_end   = date("Y-m-d H:i:s", strtotime("$date_start + $mins minute"));

WHERE lesson_date_time BETWEEN CAST('$date_start' AS DATETIME) AND CAST('$date_end' AS DATETIME)

This does the check and kinda works but does not catch any entries which have a start date in the hour (or time period until the end date) after the $start_date.

also tried

WHERE lesson_date_time >= '$date_start' and lesson_date_time =< '$date_end

which does not find any results even though they exit.

Is it possible to do what I want to do or is it just easier if I store the lesson end time and go from there

Answer 1

Your between

WHERE lesson_date_time BETWEEN CAST('$date_start' AS DATETIME) AND CAST('$date_end' AS DATETIME)

would be equivilent to

WHERE lesson_date_time >= CAST('$date_start' AS DATETIME) AND lesson_date_time <= CAST('$date_end' AS DATETIME)

This still won't work since you are only looking for lessons that will start during this lesson. You won't get ones that are continuing into this lesson. To get those, you would have to store an end date (easier) or store the lesson length.

If all lessons were 60 minutes long, you could do something like.

$mins = "60";
$date_start = "2014-10-30 12:40:00";
$old_lesson_start = date("Y-m-d H:i:s", strtotime("$date_start - $mins minute"));
$date_end   = date("Y-m-d H:i:s", strtotime("$date_start + $mins minute"));

WHERE lesson_date_time BETWEEN CAST('$old_lesson_start' AS DATETIME) AND CAST('$date_end' AS DATETIME)

Then, it would catch lessons that started up to 60 minutes ago and so would be continuing into this lesson.

If you decide to store lengths or end dates, we can help with the SQL if you have problems with it.