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.