Auto insert MySQL using Events in phpMyAdmin

Question

I have table Schedule.

CREATE TABLE Schedule (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
Program VARCHAR(30) NOT NULL,
Time DATETIME NOT NULL)

In one day there are many programs, for example:

Monday

program x || 17:00
program y || 18:00

.....

Tuesday

Program a || 10:00
Program b || 12:00

.....

until sunday.

I want to trigger every day, it will check if there is a data in that day next week. So in monday 14/09/2015 will check the next monday 21/09/2015. If there is no data in that day, it will insert copy of data in last monday.

Maybe the logic or the psuedo code will be like this

IF (Current date + 7 days IS NULL)
Then
INSERT INTO SCHEDULE (program, time)
VALUES ( (Select Program FROM SCHEDULE Where Day(Sysdate()) = Day(Time)),
         (select Time FROM SCHEDULE Where Day(sysdate()=Day(Time))+7 Day) )

My question is i don't know the proper query to insert copy of today program with same time(HH:MM) but different Date.BTW Im Using php MyAdmin Event like this

are you looking for a strategy to initiate the daily event also ? Ooops I see you have Create Event covered. — Drew, Sep 15 '15 at 05:28
check out [Date Calculations](https://dev.mysql.com/doc/refman/5.0/en/date-calculations.html) and [Date Add](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add) — Drew, Sep 15 '15 at 05:31
If you strategy you mean is Filling event in that pic then maybe i need some. I think i need proper query that can copy data like i say in it. — Wingke, Sep 15 '15 at 05:32
Yeah i want to copy All of my program in one day to the same day in next week if there isn't data yet in that date. So in Monday 14/09/2015, it will copy programs to next monday 21/09/2015. And So on. So it will check everyday, for next week. — Wingke, Sep 15 '15 at 05:39

score 1 · Accepted Answer · edited May 23 '17 at 12:03

Consider the following

Schema

-- drop table Schedule;
create table Schedule
(   id int auto_increment primary key,
    theDate datetime not null,  -- sorry, stay away from KEYWORDS and RESERVED WORD
    Program VARCHAR(30) NOT NULL,
    counterDemo int not null,
    unique key(theDate,Program) -- prevents duplicates at the combo-level
);

-- truncate table Schedule;

-- note I am skipping the time part of the date below
insert Schedule(theDate,Program,counterDemo) values 
('2015-09-15','ProgramA',1),
('2015-09-15','ProgramB',1),
('2015-09-16','ProgramA',1),
('2015-09-16','ProgramB',1);

-- insert a row for next week all programs based on a date, presumably this week

The query

-- without aliases, we seem to get the 1052 error: Ambiguous error
insert into Schedule(theDate,Program,counterDemo)
select date_add(t2.theDate,interval 1 week),t2.Program,1 from Schedule t2 where t2.theDate='2015-09-15'
on duplicate key update Schedule.counterDemo=Schedule.counterDemo+1;

The results

select * from schedule;
+----+---------------------+----------+-------------+
| id | theDate             | Program  | counterDemo |
+----+---------------------+----------+-------------+
|  1 | 2015-09-15 00:00:00 | ProgramA |           1 |
|  2 | 2015-09-15 00:00:00 | ProgramB |           1 |
|  3 | 2015-09-16 00:00:00 | ProgramA |           1 |
|  4 | 2015-09-16 00:00:00 | ProgramB |           1 |
|  5 | 2015-09-22 00:00:00 | ProgramA |           1 |
|  6 | 2015-09-22 00:00:00 | ProgramB |           1 |
+----+---------------------+----------+-------------+

run it again:

insert into Schedule(theDate,Program,counterDemo)
select date_add(t2.theDate,interval 1 week),t2.Program,1 from Schedule t2 where t2.theDate='2015-09-15'
on duplicate key update Schedule.counterDemo=Schedule.counterDemo+1;

The results

select * from schedule;
+----+---------------------+----------+-------------+
| id | theDate             | Program  | counterDemo |
+----+---------------------+----------+-------------+
|  1 | 2015-09-15 00:00:00 | ProgramA |           1 |
|  2 | 2015-09-15 00:00:00 | ProgramB |           1 |
|  3 | 2015-09-16 00:00:00 | ProgramA |           1 |
|  4 | 2015-09-16 00:00:00 | ProgramB |           1 |
|  5 | 2015-09-22 00:00:00 | ProgramA |           2 |
|  6 | 2015-09-22 00:00:00 | ProgramB |           2 |
+----+---------------------+----------+-------------+

This utilizes the mysql feature of insert on duplicate key update feature. See Manual page here. If the row to be inserted already exists, the update occurs. That is why I showed the counterDemo column. That way, no duplicate data. The counterDemo was just a visual that it works.

The unique key(theDate,Program) at the bottom of the create table is what makes this work. When mysql sees a duplicate based on that, it forces the update (as opposed to an insert). Note again the link to the manual page in the above paragraph.

Another manual page for date_add too look at.

It's a lot to swallow at once, but it keeps your data clean. You need to work this into your event you created with phpmyadmin.

For a somewhat detailed example of Create Event, see this I wrote up here. I have done a few, there are other better ones I am sure by others.

Hey Thanks Drew, i got your point. I will try in mySql. BTW you set t2.theDate , So if i want to make it auto, i just change to GETDATE() ? And the program have Hour too ? So how can i insert that hour. Because ProgramA 2015-09-15 16:00:00 For example — Wingke, Sep 15 '15 at 06:06
the hour is naturally a part of datetime. I was keeping it simple for 00:00:00 by not specifying it. You can. BUT that complete datetime (with HH:MM:SS) matters for the above. In otherwords, 16:00:00 matters, in so far as the `insert on duplicate key update goes` .... off by 1 minute, you get a new row. Make sense ? You will only see (get it) it when it is on your screen, play with it :) — Drew, Sep 15 '15 at 06:15
Then if there is data in next tuesday, it will force to update ? sory i dont get that part — Wingke, Sep 15 '15 at 06:23
So it will work like this drew. If the user dont insert Schedule for next week, the system will auto update the schedule, but if the user already insert the schedule for the next week, so system will do nothing. maybe you will get that more simple than the code — Wingke, Sep 15 '15 at 06:31
all I know is that you said based on data this week, go jam it in for next week — Drew, Sep 15 '15 at 06:37
the point of the code is to show that it will do it auto for next week, that dupes won't happen, and that the date_add with interval works. Like I said, play with it. Like for an hour. Going to sleep. — Drew, Sep 15 '15 at 06:38
Check this fine posting about using ignore http://stackoverflow.com/a/548570/1816093 — Drew, Sep 15 '15 at 06:54

Auto insert MySQL using Events in phpMyAdmin

1 Answers1

Schema

The query

The results

run it again:

The results