How to check data range?

Question

When requesting a vehicle reservation, the system inserts the scheduled departure and return time code (we prefer to work with fixed codes instead of schedules in order to facilitate programming). What happens is that when the reservation is made at times ahead (for example, I am booking in the morning an hour in the afternoon) the system allows another reservation that goes "over" the previous reservation.

For example, if I book from 9:00 to 10:00, mine select does not display this interval after confirmation, but if someone reserves from 8:00 to 11:00 on the same day he will let record. With the select below I do not display the booked schedules, any idea how to do the validation?

SELECT H.* FROM HORARIOS H
WHERE NOT EXISTS (
    SELECT * FROM RESERVAS R
    WHERE H.COD BETWEEN R.COD_HORA_SAIDA AND
          R.COD_HORA_PREVISTA-1 AND
          R.COD_VEICULO = :codveiculo AND
          DATE_FORMAT(DATA_SAIDA, \'%d-%m-%Y\') = :codcalendario AND
          r.ativa = 1)
ORDER BY H.COD

Below is an example of the situation that occurs:

Answer 1

Come on. I will try to use a pseudo code, because I have no basis in php.

Given the various:

horaEntradaBanco = registroBanco.horaEntrada;
horaSaidaBanco = registroBanco.horaSaida;
horaEntradaMarkup = $('#campoHorarioEntrada').getValue();
horaSaidaMarkup = $('#campoHorarioSaida').getValue();

You could do the following validations:

if(horaEntradaMarkup<=horaEntradaBanco && horaSaidaMarkup>=horaSaidaBanco){
    //bloquear utilização de horário
    //exemplo de entrada no if: Horas banco: 14h~15h
                                Horas Markup: 13h~17h
} else if(horaEntradaMarkup<=horaEntradaBanco && horaSaidaMarkup<=horaSaidaBanco) {
    //bloquear utilização de horário
    //exemplo de entrada no if: Horas banco: 14h~16h
                                Horas Markup: 13h~15h

    //Aqui eu não sei se já está sendo validado ou não, já que vc só falou do exemplo acima.
}

See if it works.

Answer 2

I decided as follows:

SELECT count(*) as flag FROM HORARIOS H
                     WHERE EXISTS (
                           SELECT * FROM RESERVAS R
                                WHERE H.COD BETWEEN R.COD_HORA_SAIDA AND
                                      R.COD_HORA_PREVISTA-1 AND
                                      R.COD_VEICULO = 2 AND
                                      DATE_FORMAT(DATA_SAIDA, '%d-%m-%Y') = :data_saida and
                                      (h.cod between :codsaida and :codretorno) and
                                      r.ativa = 1)
                                ORDER BY H.COD;

That way, I made an if, if the result is more than 1 has something in the range and gives error. Thank you all.