Show all rooms, and know if it is free or occupied, according to a specific date

Question

Show all rooms, and know if it is free or occupied, according to a specific date

Navigation

#1 by (0 votes)

1

According to a specific date, show all the rooms, also show if it is free or not (that can be known with the reservation).

So far I have managed to locate the reserved rooms:

SELECT  h.habitacion_numero as habitacion,
    r.inicio_fecha as fecha_inicio,
    r.fin_fecha as fecha_fin 
    FROM habitaciones h 
    inner join reservas r 
        on h.habitacion_numero=r.habitacion

mysql phpmyadmin

asked by davabun 15.12.2017 в 23:52

source

1 answer

500 Internal Server Error in AJAX and php How to get ajax response in a variable or alert

score 0 · Accepted Answer

Obtaining the status of a room at a certain date is quite simple, your query effectively gives you the reserved rooms and their dates, what you should do is refine this query so that it applies only to a date and the rooms that are in the same, will be those that are reserved and those that are not, those that are available.

set @fecha = STR_TO_DATE('2017/12/09','%Y/%m/%d');

SELECT  h.habitacion_numero,
        CASE WHEN hr.habitacion_numero is null 
              THEN 'Disponible'
              ELSE 'Reservada'
        END  AS 'Estado'      
        FROM habitaciones h
        LEFT JOIN (SELECT   h1.habitacion_numero
                            FROM habitaciones h1
                            inner join reservas r 
                                on h1.habitacion_numero=r.habitacion
                            WHERE @fecha BETWEEN r.inicio_fecha AND r.fin_fecha
          ) hr
          on hr.habitacion_numero = h.habitacion_numero;

Explanation:

First of all I set a variable @fecha with the date I'm going to consult
Your query has been parameterized to filter only the rooms where @fecha falls within the range of each reservation, with this I get the "Resevadas"
The main query is about habitaciones with a LEFT on the "Reserved", if it is obviously a "Reserved" room, it is an available one.

You can try it with this sqlfiddle