Compare if there is already a record in BD MySql

2

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 client only be able to book a vacant room, and if you try to book a room already booked return an error message.

A reservation on 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.

    
asked by anonymous 22.05.2015 / 19:18

2 answers

1

Well, come on.

First, your table has serious structuring problems, you do not save (at least on this table) at any time what the customer is booking, and also saves the final / initial time and minute in 4 columns when you can easily use only 2 columns DATETIME , then restructured your table, it 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 cliente field is text, ideally you should change this field to cliente_id and set a foreign key.

Code CREATE of 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
;

In 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');

So you can check if the time chosen by the customer trying to make a reservation is already reserved 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 queries.

    
22.05.2015 / 19:55
-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 you return 0, it is because you do not have any scheduling for that room, at the time and date stipulated.

    
22.05.2015 / 19:34