scheduled tasks - How to schedule a MySQL query? -

can schedule task run @ specified interval in mysql?

i have inventory mysql database. structure follows:

table_1            fields: itmcode, avgcost table_2            fields: itmcode(fk table_1(itmcode)), quantity 

the basis of report when want inventory valuation details item wise past date.

the avgcost , quantity fields changed when new purchase posted system. can run query see current stock valuation, want able see stock valuation @ previous date well. how do that? quantity, can add sales , deduct purchases backwards current date until whatever date report requires, avgcost current since gets updated each time purchase posted.

i wondering if automatic daily dump executed, similar this:

select itmcode, quantity, avgcost, (avgcost * quantity) ttlval table_1  join table_2 on table_1.itmcode = table_2.itmcode 

is task possible schedule directly in mysql or there other solution?

you have 2 basic options (at least):

1, take @ event scheduler

first create table eg. stock_dumps fields

itemcode, quantity, avgcost, ttlval,dump_date (datetime)

create event `dumping_event` on schedule         every 1 day     on completion not preserve     enable     comment ''     begin insert stock_dumps(itemcode, quantity, avgcost, ttlval,dump_date) select itmcode, quantity, avgcost, (avgcost * quantity)as ttlval, now()   table_1 join table_2 on table_1.itmcode = table_2.itmcode; end 

please follow instructions how enable scheduler on link posted above. note : old versions of mysql don't have event scheduler

2, create cron job/windows scheduled job:

create sql file:

insert stock_dumps(itemcode, quantity, avgcost, ttlval,dump_date) select itmcode, quantity, avgcost, (avgcost * quantity)as ttlval, now() table_1 join table_2 on table_1.itmcode = table_2.itmcode; 

schedule command:

mysql -uusername -ppassword < /path/to/sql_file.sql