I have a scheduling system, where I have to return the dates and times available (which is not already registered in the database) for a new schedule with these available dates and times. I have no idea how to do this, can anyone give me a light?
I'm using PHP + MySQL.
In the absence of a date / timestamp series generator (I could not find this functionality in the MySQL documentation), I would create a table with the possible range of times (from 9am to 5pm, for example), one with dates and schedules, and would cross with each other to get the schedules available.
Edit: original response could not support multiple dates
( The principle of the thing is the same, I just changed the queries to dynamically include in schedules_possible the scheduling date to be verified )
create table horarios_possiveis (horario time not null unique);
insert into horarios_possiveis (horario) values
('09:00'),('10:00'),('11:00'),('12:00'),
('13:00'),('14:00'),('15:00'),('16:00'),
('17:00');
create table horarios_cadastrados (data date not null, horario time not null, constraint unique agendamento (data, horario));
insert into horarios_cadastrados (data, horario) values
('2017-09-15', '10:00'),('2017-09-15', '12:00'),
('2017-09-15', '13:00'),('2017-09-15', '15:00'),
('2017-09-16', '09:00'),('2017-09-16', '15:00');
select * from horarios_possiveis;
select * from horarios_cadastrados;
select p.* from (select '2017-09-15' data, horario from horarios_possiveis) p
left join horarios_cadastrados c on p.horario=c.horario and p.data=c.data
where c.data is null;
select p.* from (select '2017-09-16' data, horario from horarios_possiveis) p
left join horarios_cadastrados c on p.horario=c.horario and p.data=c.data
where c.data is null;
Result:
| horarios_possiveis |
| horario |
|--------------------|
| 09:00:00 |
| 10:00:00 |
| 11:00:00 |
| 12:00:00 |
| 13:00:00 |
| 14:00:00 |
| 15:00:00 |
| 16:00:00 |
| 17:00:00 |
| horarios_cadastrados |
| data | horario |
|------------|----------|
| 2017-09-15 | 10:00:00 |
| 2017-09-15 | 12:00:00 |
| 2017-09-15 | 13:00:00 |
| 2017-09-15 | 15:00:00 |
| 2017-09-16 | 09:00:00 |
| 2017-09-16 | 15:00:00 |
| horarios_livres_09-15 |
| data | horario |
|------------|----------|
| 2017-09-15 | 09:00:00 |
| 2017-09-15 | 11:00:00 |
| 2017-09-15 | 14:00:00 |
| 2017-09-15 | 16:00:00 |
| 2017-09-15 | 17:00:00 |
| horarios_livres_09-16 |
| data | horario |
|------------|----------|
| 2017-09-16 | 10:00:00 |
| 2017-09-16 | 11:00:00 |
| 2017-09-16 | 12:00:00 |
| 2017-09-16 | 13:00:00 |
| 2017-09-16 | 14:00:00 |
| 2017-09-16 | 16:00:00 |
| 2017-09-16 | 17:00:00 |