Reservations Classrooms with different time slots

Navigation
1

the server must be able to reserve a classroom for 30 minutes, 1 hour or 1 hour and a half.

The database:

describes users; 

'id','int(11)','NO','PRI',NULL,'auto_increment'
'username','varchar(64)','NO','',NULL,''
'email','varchar(64)','NO','',NULL,''

describes classes; 

'id','int(11)','NO','PRI',NULL,'auto_increment'
'tipo','varchar(64)','NO','',NULL,''

describes reservations; 

'id','int(11)','NO','PRI',NULL,'auto_increment'
'fecha','date','NO','',NULL,''
'horaInicio','time','NO','',NULL,''
'horaFin','time','NO','',NULL,''
'clases_id_id','int(11)','NO','MUL',NULL,''
'usuarios_id_id','int(11)','NO','MUL',NULL,''

My problem, the ranges for the consultations, since I know how to do it if it was from hour to hour because I would: 

select * from reservas where clases_id_id=1 and fecha = "2016-05-17" and horaInicio >= "19:00" and horaFin <= "20:00";

And the result: 

'4','2016-05-17','19:00:00','20:00:00','1','1'

Reservations that have already been made: 

select * from reservas;

'1','2016-05-17','09:30:00','11:00:00','1','1'
'2','2016-05-17','12:00:00','13:30:00','1','1'
'3','2016-05-17','12:00:00','12:30:00','1','1'
'4','2016-05-17','19:00:00','20:00:00','1','1'

Now if I want to see the next hour, I check that there is available space: 

select * from reservas where clases_id_id=1 and fecha = "2016-05-17" and horaInicio >= "20:00" and horaFin <= "21:00";

This query does not produce results.

But if now I want to reserve half an hour in half an hour, how could I do it and then do a php to generate a JSON with the content of the reservation, because if I do: 

select * from reservas where clases_id_id=1 and fecha = "2016-05-17" and horaInicio >= "09:30" and horaFin <= "10:00";

I get it as free, we do not give any results and it is because of the condition of the end, since the 11 is not less than or equal to 10:00.

Does anyone think of how to solve this problem?

Thank you in advance.

Greetings

    
asked by Red 13.05.2016 в 21:52
source

2 answers

1

I do not know if I understood well, you enter the two hours to find if it is booked in those 30 mins. You can change to the range you need. 

select * 
from reservas 
where clases_id_id=1 
and fecha = "2016-05-17" 
AND horaInicio BETWEEN "09:30" AND "10:00"
OR horaFin BETWEEN "09:30" AND "10:00"

I hope it serves you.

    
answered by 16.05.2016 в 05:41
0

I would simply change the comparators, using the following logic: 

select * from reservas where clases_id_id=1 and fecha = "2016-05-17" and horaInicio <= "09:30" and horaFin >= "10:00";

This way I will get all the reservations that were started until 9:30 and that end after 10:00.

But, what happens if a reservation starts at 9:45 and ends at 10:15 and we want to know if the room is occupied between 9:30 and 10:00?

A quick solution (not proven) would be to add an OR of two conditions that finds the reservations that start before and end after the initial time and the same for the final time: 

select * from reservas where clases_id_id=1 and fecha = "2016-05-17" and (horaInicio <= "09:30" and horaFin >= "09:30" or horaInicio <= "10:00" and horaFin >= "10:00" or horaInicio > "09:30" and horaFin < "10:00") ;

I added a third condition to the OR, for cases in which you want to search in larger ranges, for example between 09:00 and 11:00.

In this way, you should be able to obtain, for example (for our intevalo case, enter 9:30 and 10:00):

  • A reservation that exists between 9:10 and 9:40
  • A reservation between 9:30 and 10:00
  • A reservation between 9:45 and 10:15
  • A reservation between 9:35 and 9:50 (although reservations of less than 30 minutes are not accepted in the proposed scenario)
answered by 13.05.2016 в 22:23
Send date and time to the mvc asp .net driver Do not update property in BBDD from Controller