Willi W Willi W - 1 year ago 56
MySQL Question

Mysql - How can I transfer data from one table to another on a specific day and time in the week, every week?

I want to transfer data from one table to another table on a specific day (Friday at 20:00) every week. I also want to reset the data from the first table every Sunday.

When I run this line it sends the correct data to the right table (table1).

$sql = "INSERT INTO table1 (Name, Email, Phone, Work, Week)
VALUES ('$name', '$email', '$phone', '$work', '$weekNumber')";

When I run this line it sends one random row from table1 to table2. It will not store any of the new data in table1, only use data which is already stored in table1.

$sql = "INSERT INTO table2 (Name, Email, Work, Week) SELECT Name, Email, Work, Week FROM table1 Where Week = ('$weekNumber') ORDER BY RAND() LIMIT 1";

All of this code is written in a php-file and i view the tables in phpMyadmin.

Any sql-gurus out there with suggestions on how I can make data from table1 transferred to table2 every Friday at 20:00? Appreciate it in advance. Keep in mind i'm new to writing sql-code and just started working with databases this week, therefore I would really appreciate specific code-examples instead of general information on how I can perceive my objective. Combined would be best though.

Answer Source

You can do this easily with event scheduler in MySQL



As as example, you could use the following to do a specific task everyday:

    CREATE EVENT UpdateData ON SCHEDULE EVERY DAY STARTS '2016-10-10 00:00:00' -- This is scheduled to start from '2016-10-10' and updates data every day 
      DO BEGIN 
        UPDATE table1 SET Status = 1 WHERE Status = 0; 
        UPDATE table2 SET Status = 1 WHERE Status = 0;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download