OK, so I have the the following table structure: [url removed, login to view]
When I go to [url removed, login to view] and try to add a reservation with start date/time 2011-04-11 03:00:00 and end date/time 2011-04-23 05:00:00 for car 1, it shouldn't let me do it because it will conflict with the reservation that's already there.
In my PHP code, when I try to add a new reservation, the following code is executed:
$sql = 'SELECT id FROM ' . $config['tbl_prefix'] . 'reservations WHERE car_id = ' . $car_id . ' AND \'' . $start_date . ' ' . $start_time . '\' BETWEEN start_datetime AND end_datetime AND \'' . $end_date . ' ' . $end_time . '\' BETWEEN start_datetime AND end_datetime';
For the example stated above, the query would be:
SELECT id FROM corfu44_reservations WHERE car_id = 1 AND '2011-04-11 03:00:00' BETWEEN start_datetime AND end_datetime AND '2011-04-23 05:00:00' BETWEEN start_datetime AND end_datetime
It looks right but it's not doing exactly what I want.
So what I need is basically a SQL statement that will prevent me from entering any reservation that conflicts with any of the available reservations.