Scheduling System by Date

Asked

Viewed 1,874 times

2

I have a scheduling system, where I have to return the dates and times available (not registered at the bank obviously) for a new schedule with those available dates and times. I don’t have a sense of how to do this, someone can give me a light?

I’m using PHP + Mysql. Algo como esta imagem!

php html mysql phpmyadmin

  • What have you tried? How is your table scheme? Scheduling is hourly?

  • @acklay still not imagined anything about the bank. But it would be an intercalated time, may be 1 hour ago 2 hours

  • Nunks can you help me? https://answall.com/questions/369467/data-e-hor%C3%A1rio-para-atendimento-codeigniter? noredirect=1#comment736463_369467

1 answer

3


In the absence of a date/timestamp serial generator (I couldn’t find this feature in the Mysql documentation), I would create a table with the possible time range (from 9am to 5pm, for example), one with registered dates and times, and would cross with each other to get the available schedules.

Edit: The original answer did not support multiple dates

(The principle of the thing is the same, I only changed the queries to include dynamically in schedules)

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;

Upshot:

| 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 |

Example in SQL Fiddle

Browser other questions tagged php html mysql phpmyadmin

You are not signed in. Login or sign up in order to post.