I have 6 database tables which all together present projects and processes are assigned to each project. Also users are assigned to each process. Here are the tables:
User
Project
Process
Proc_leader
Proc_checker
Proc_staff
What I want is to make system copy and store copied tables in database each Sunday. Some kind of trigger which will copy all data each Sunday and then will allow user to view saved records based on date. For example:
I have 1 project with 1 process. And 3 users assigned to process as process leader, process checker and process staff.
When it come to Sunday System should copy tables and create copied tables with name
User06/11/16
Project06/11/16
Process06/11/16
Proc_leader06/11/16
Proc_checker06/11/16
Proc_staff06/11/16
Or something like this. Then user should be able to press View History button and system will show something like this:
Show Records for 23/10/16
Show Records for 30/10/16
Show Records for 06/11/16
And it should display data from that table.
I am not so experienced in sql and have not enough knowledge to put this in practice. So I am looking for any help. Any ideas how can I achieve this? Thanks
You have to write a cron job . with below steps
1 create table [like][1] master table ( Append date with name) .
2 Create sql for inserting record form master tables to New tables created at step 1 http://dev.mysql.com/doc/refman/5.7/en/insert-select.html.
Schedule the cron job as per your requirement.