Редактировать расписание на основе внешнего ключа
Я хочу создать график даты для системы прикреплений с помощью 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 которые генерируют каждый понедельник до х недель.
Итак, ПРОБЛЕМА: если я редактирую столбец 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
А также, как я могу справиться, если день перед редактированием — пятница и обновление до понедельника?
Решение
Возможное решение было бы (при условии, что поля ‘date’ и ‘time’ являются типами 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 FIDDLE DEMO: http://sqlfiddle.com/#!2/6b7221/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)