Редактировать расписание на основе внешнего ключа
Я хочу создать расписание дат для системы обслуживания с помощью PHP.
Итак, у меня есть:
Таблица SCHEDULE
id | room | day | class_id
1 | 11 | Mon | 2
3 | 12 | Tue | 3
5 | 11 | Mon | 4
Таблица MEETING
id | date | time | schedule_id
1 | 06 Oct 2014 | 9:00 | 1
2 | 13 Oct 2014 | 9:00 | 1
3 | 20 Oct 2014 | 9:00 | 1
Таким образом, записи о собраниях были сгенерированы на основе записи SCHEDULE, в этом примере MEETING id: 1,2,3 основан на SCHEDULE id: 1, которые генерируются каждый понедельник до x недель.
Итак, ПРОБЛЕМА в следующем: ЕСЛИ я отредактирую столбец DAY из SCHEDULE, как я могу обновить столбец даты?
Т.Е.: Я обновляю SCHEDULE set day = "Fri" Where id = 1,
Таким образом, таблица MEETING становится
id | date | time | schedule_id
1 | 10 Oct 2014 | 9:00 | 1
2 | 17 Oct 2014 | 9:00 | 1
3 | 24 Oct 2014 | 9:00 | 1
А также как я могу справиться, если за день ДО редактирования была пятница и обновлена до понедельника?
2 answers
Возможным решением было бы (при условии, что поля "дата" и "время" являются типами varchar) с использованием функций STR_TO_DATE и DATE_FORMAT. Затем по следующим sql-запросам:
SET @schedule_id_to_modify = 1;
UPDATE MEETING SET date =DATE_FORMAT(STR_TO_DATE(date,'%d %b %Y')
+ INTERVAL 4 DAY,'%d %b %Y')
WHERE schedule_id = @schedule_id_to_modify;
UPDATE SCHEDULE SET day = DATE_FORMAT((
SELECT STR_TO_DATE(date,'%d %b %Y')
FROM MEETING where schedule_id = @schedule_id_to_modify LIMIT 1) ,'%a')
WHERE id = @schedule_id_to_modify;
Просто измените интервал и идентификатор для изменения. Например,
Изменить пн->пт:
SET @schedule_id_to_modify = 1;
UPDATE MEETING SET date =DATE_FORMAT(STR_TO_DATE(date,'%d %b %Y')
+ INTERVAL 4 DAY,'%d %b %Y')
WHERE schedule_id = @schedule_id_to_modify;
UPDATE SCHEDULE SET day = DATE_FORMAT((
SELECT STR_TO_DATE(date,'%d %b %Y')
FROM MEETING where schedule_id = @schedule_id_to_modify LIMIT 1) ,'%a')
WHERE id = @schedule_id_to_modify;
Изменить пт->пн:
SET @schedule_id_to_modify = 1;
UPDATE MEETING SET date =DATE_FORMAT(STR_TO_DATE(date,'%d %b %Y')
- INTERVAL 4 DAY,'%d %b %Y')
WHERE schedule_id = @schedule_id_to_modify;
UPDATE SCHEDULE SET day = DATE_FORMAT((
SELECT STR_TO_DATE(date,'%d %b %Y')
FROM MEETING where schedule_id = @schedule_id_to_modify LIMIT 1) ,'%a')
WHERE id = @schedule_id_to_modify;
ДЕМОНСТРАЦИЯ СКРИПКИ SQL: http://sqlfiddle.com /#!2/6в7221/2
Примечание: Эти запросы были бы более простыми, если бы были определены типы полей date и time как Date и Time... Тогда сценарий будет только таким:
SET @schedule_id_to_modify = 1;
UPDATE MEETING SET date = date + INTERVAL 4 DAY
WHERE schedule_id = @schedule_id_to_modify;
UPDATE SCHEDULE SET day = DATE_FORMAT((
SELECT date FROM MEETING where schedule_id = @schedule_id_to_modify
LIMIT 1) ,'%a')
WHERE id = @schedule_id_to_modify;
Это можно сделать с помощью триггера, и если день всегда является днем вперед. Это означает, что если Пн будет изменен на пятницу, то даты встреч будут перенесены на следующую пятницу и так далее.
Для этого я бы создал таблицу для хранения названия дня и числовых значений примерно как
mysql> create table days (day varchar(10),day_number int);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into days values ('Sun',0),('Mon',1),('Tue',2),('Wed',3),('Thu',4),('Fri',5),('Sat',6);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
Далее будет создан следующий триггер, который сработает после обновления расписания
DELIMITER //
create trigger update_schedule after update on schedule
for each row
begin
declare existing_day varchar(10);
declare updated_day varchar(10);
declare diff_day int;
if new.day <> old.day then
select day_number into existing_day from days where LOWER(day) = LOWER(old.day);
select day_number into updated_day from days where LOWER(day) = LOWER(new.day);
set diff_day = updated_day - existing_day ;
if diff_day < 0 then
set diff_day = diff_day * -1 ;
end if ;
update meeting set date = DATE_ADD(date , interval diff_day day) where schedule_id = new.id;
end if;
end; //
delimiter ;
Теперь давайте проведем некоторый тест
mysql> create table schedule (id int, room int, day varchar(100),class_id int);
Query OK, 0 rows affected (0.08 sec)
mysql> insert into schedule values (1,11,'Mon',2),(2,12,'Tue',3),(5,11,'Mon',4);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> create table meeting (id int, date date, time time,schedule_id int);
Query OK, 0 rows affected (0.08 sec)
mysql> insert into meeting values (1,'2014-10-06','9:00',1),(2,'2014-10-13','9:00',1),(3,'2014-10-20','9:00',1);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
Теперь давайте обновим расписание
mysql> update schedule set day = 'Fri' where id = 1 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Теперь, если мы выберем данные собрания, мы увидим изменение, как показано ниже -
mysql> select * from meeting ;
+------+------------+----------+-------------+
| id | date | time | schedule_id |
+------+------------+----------+-------------+
| 1 | 2014-10-10 | 09:00:00 | 1 |
| 2 | 2014-10-17 | 09:00:00 | 1 |
| 3 | 2014-10-24 | 09:00:00 | 1 |
+------+------------+----------+-------------+
3 rows in set (0.00 sec)