Show all rooms and reservation

Question

I have three table

Type (id, libel)
Bedroom (id, name, type)
Reservation (id, name, libel, start_date, end_date, room)

I want to make an application where you could get all the rooms and reservations between two dates. I have the following query

$sql = 'SELECT r.id, r.name, r.start_date, r.end_date, r.libel, r.statut ,c.name as room, t.libel as type '
            . 'FROM type t INNER JOIN Bedroom c ON t.id = c.type LEFT JOIN reservation r ON c.id = r.room'
            . 'WHERE ( :start_date BETWEEN r.start_date AND r.end_date '
            . ' OR :end_date BETWEEN r.start_date AND r.end_date'
            . ' OR r.start_date BETWEEN :start_date AND :end_date'
            . ' OR r.end_date BETWEEN :start_date AND :end_date) ORDER BY c.'.$orderColumn.' ASC';

But it appears that rooms be reserved between start_date and end_date.

PinkTurtle · Answer 1 · 2014-08-18T10:35:52.833

0

$sql = 'SELECT Reservation.*, Bedroom.*, Type.* FROM Reservation '
. 'RIGHT JOIN Bedroom ON Reservation.room = Bedroom.id '
. 'INNER JOIN Type ON Bedroom.Type = Type.id'
. 'WHERE $someDate BETWEEN $startDate AND $endDate'
. 'ORDER BY $orderColumn ASC;'

Does this work ? By the way you are not using AS clauses in your SELECT. What does your :start_date and :end_date refer to ?

edited Aug 18 '14 at 10:35

answered Aug 18 '14 at 08:13

PinkTurtle

6,942
3
25
44

If i use INNER JOIN it show all reserved room only. I want to show all rooms and reservations too. – titamrtn Aug 18 '14 at 08:20
Then you want to use either `RIGHT JOIN` and/or `LEFT JOIN`. See this link http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join for an explanation. I edited the query above. – PinkTurtle Aug 18 '14 at 08:28
The problem is the WHERE clause. It only finds entries with a reservation date that is entries that exist in your Reservation table. You should try adding `OR (r.start_date IS NULL AND r.end_date IS NULL)`. See if this works ? – PinkTurtle Aug 18 '14 at 10:33

score 0 · Answer 2 · answered Aug 18 '14 at 11:46

The solution is changing WHERE to AND

$sql = 'SELECT r.id, r.name, r.start_date, r.end_date, r.libel, r.statut ,c.name as room, t.libel as type '
        . 'FROM type t INNER JOIN Bedroom c ON t.id = c.type LEFT JOIN reservation r ON c.id = r.room'
        . 'AND( :start_date BETWEEN r.start_date AND r.end_date '
        . ' OR :end_date BETWEEN r.start_date AND r.end_date'
        . ' OR r.start_date BETWEEN :start_date AND :end_date'
        . ' OR r.end_date BETWEEN :start_date AND :end_date) ORDER BY c.'.$orderColumn.' ASC';

Show all rooms and reservation

2 Answers2