CODEWITHSUNDEEP
phpmysql
JasonK
JasonK

Reputation: 5294

Check if room is free (classroom booking)

First of all, excuse me for my poor English. I'm trying to select all rooms that are not reserved yet. The query seems to work most of the time, but sometimes it shows the room which has already been booked for the given time.

//Example input:
//$date = 2014-06-04;
//$begintime = 09:30;
//$endtime = 12:00;

$query = $mysqli->query("
    SELECT *
    FROM room
    WHERE room.id NOT IN  (
                            SELECT room_id
                            FROM reservation
                            WHERE reservation.begintime <= '{$date} {$begintime}'
                            AND reservation.endtime >= '{$date} {$endtime}'
                            )
    ");

What am I doing wrong? It's driving me crazy, just can't seem to figure it out.

Thanks in advance!

Edit

The begin and endtime are both DATETIME columns.

Upvotes: 2

Views: 1660

Answers (3)

jeroen
jeroen

Reputation: 91734

Depending on the column types of the begin and end time, your comparisons might fail, but as indicated in the comments, your logic of comparing the times is wrong.

You need to compare the begin time to the end time and vice versa:

WHERE reservation.begintime <= '{$date} {$endtime}'
AND reservation.endtime >= '{$date} {$begintime}'

Apart from that, I don't know where the data comes from but to avoid sql injection, you'd better use a prepared statement instead of injecting php variables directly in your sql statement.

Upvotes: 3

Jonathan M
Jonathan M

Reputation: 17451

The way to check for double-booking is this:

If the existing reseveration starts at B0 (beginning 0) and ends at E0 (end 0), and the time period you want to check is from B1 to E1, then you want to check all existing reservations where:

(B0 < E1) && (B1 < E0)

These are collisions, double-bookings.

Upvotes: 0

user1477388
user1477388

Reputation: 21430

As @d'alar'cop hinted at above, you need to change your query like so:

WHERE reservation.begintime >= '{$date} {$begintime}'
                            AND reservation.endtime <= '{$date} {$endtime}'

You want to find a reservation that is greater than the begin date, and less than the end date, not vis versa.

Also, instead of using separate columns, you will want to use a combined datetime column.

Upvotes: -1

Related Questions