How to insert data into mysql automatically by certain datetime

Question

I have two tables. The first is inventory_control and the second is stock.

• table inventory_control is recorded stock opname a warehouse and every receiving or dispatching event, data is always updated.

• table stock is related to accounting.

Every day at 00:00 o'clock, all data in the inventory_control table needs to be inserted into table stock automatically.

I have no idea about that. I have searched in google and I didn't find something useful. I learned about trigger and I didn't think trigger can help.

These are my tables:

CREATE TABLE 'inventory_control' (
'warehouse', varchar(100) unsigned NOT NULL,
'idItem', int(8) unsigned NOT NULL,
'Quantity' int(8) unsigned);


CREATE TABLE 'stock'(
'warehouse', varchar(100) unsigned NOT NULL,
'idItem', int(8) unsigned NOT NULL,
'Quantity' int(8) unsigned,
'value', int(11) unsigned);

Answer 1

In your cpanel you could use cron jobs by running php script file

http://awesomescreenshot.com/0613s35q6c

like this

php -f /home/your_username/public_html/script.php and ajust the time there

Answer 2

I'm borrowing from this answer, and this question, but you can create an event:

CREATE EVENT IF NOT EXISTS `daily_event`
ON SCHEDULE EVERY 00 DAY_HOUR
COMMENT 'Insert data from inventory_control to stock'
DO INSERT INTO inventory_control SELECT * FROM stock;

However, if you want to run this each night, you probably want to edit this since this query will append all of the stock table each night to the inventory_control table. This can lead to a lot of repetitive data. A better query (assuming you have a timestamp field or something similar for each record) would be:

CREATE EVENT IF NOT EXISTS `daily_event`
ON SCHEDULE EVERY 1 DAY
STARTS '2014-11-04 00:00:00'
COMMENT 'Insert data from inventory_control to stock'
DO INSERT INTO inventory_control SELECT * FROM stock 
WHERE 'timestamp' BETWEEN CURDATE() - 2 AND CURDATE() - 1;

This will run the query every night at midnight, and will only add records that were created in the last 24 hours.

EDIT: Events will not be scheduled unless the MySQL Event Scheduler is configured

EDIT2: Be sure to check out the documentation on Event Scheduling for the full list of what you can do!