Compare if there is already a record in BD Mysql

Question

I am creating a room reservation system, and I have the following columns in my table:

agenda_id
agenda_hora_inicial
agenda_hora_final
agenda_data_inicial
agenda_data_final
agenda_sala

My question is how to make the customer only be able to book a vacant room, and if you try to book an already reserved room return an error message.

A reservation at the bank would look something like this:

o cliente "jose"
data_inicial: 22/05/2015 
data_final 22/05/2015
horario inicial 11:00
horario final 12:00
sala 01

If another client tries to book the room after 12:00 it will be released, but from 11:00 until 12:00, no.

Answer 1

Well, come on.

First, your table has serious structuring problems, you do not keep (at least in this table) at any time what the customer is making the reservation, and also saves the final/start time and minute in 4 columns when you can easily use only 2 columns DATETIME, then, restructuring her table, she would look like this:

+-----------------+--------------+------+-----+-------------------+----------------+
| Field           | Type         | Null | Key | Default           | Extra          |
+-----------------+--------------+------+-----+-------------------+----------------+
| reserva_id      | smallint(6)  | NO   | PRI | NULL              | auto_increment |
| cliente         | varchar(100) | NO   |     | NULL              |                |
| sala            | smallint(3)  | NO   |     | NULL              |
| reservado_em    | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| datahora_inicio | datetime     | NO   |     | NULL              |                |
| datahora_fim    | datetime     | NO   |     | NULL              |                |
+-----------------+--------------+------+-----+-------------------+----------------+

_{* Note that the field cliente is text, ideal is you change this field to cliente_id and set a foreign key.}

Code CREATE table:

CREATE TABLE `reservas` (
	`reserva_id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
	`cliente` VARCHAR(100) NOT NULL,
	`sala` SMALLINT(3) NOT NULL,
	`reservado_em` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`datahora_inicio` DATETIME NOT NULL,
	`datahora_fim` DATETIME NOT NULL,
	PRIMARY KEY (`reserva_id`)
)
ENGINE=InnoDB
AUTO_INCREMENT=6
;

This way, you can register a reservation using:

insert into reservas(cliente, sala, datahora_inicio, datahora_fim) values('José', 001, '2015-05-25 11:00:00', '2015-05-25 11:59:59');

Then you can check if at the time chosen by the customer who is trying to make a reservation is already booked using:

select * 
  from reservas 
 where (
            (datahora_inicio between '2015-05-25 11:00:00' and '2015-05-25 11:59:59') or
            (datahora_fim between '2015-05-25 11:00:00' and '2015-05-25 11:59:59')
         )
   and sala = 1;

See the example in Sqlfiddle for you to test the queries.

Answer 2

SELECT count(*) FROM agenda WHERE agenda_sala = <id_da_sala> AND agenda_hora_inicial = <hora_inicial> AND agenda_hora_final = <hora_final> AND agenda_data_inicial = <data_inicial> AND agenda_data_final = <data_final>;

If return 0, it is pq has no scheduling for that room at the stipulated date and time.