Set up SQL query on timer with PHPmyAdmin

Question

I am trying to execute a query on a timer (once a day at the same time).

The query is the same every time. I grab data from excel and save it to my computer's desktop. Then UPDATE from file.csv with PHPmyAdmin.

In phpmyadmin I do not see any option to automate from the interface. Can someone please help me accomplish this for free?

Answer 1

------------------- Update (Better answer - Jan 2014) --------------------

You can use the MySQL even scheduler (http://dev.mysql.com/doc/refman/5.1/en/events.html). Allows you to set up events in MySQL that run queries at the specified time.

Sample from the manual

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
  DO
    UPDATE myschema.mytable SET mycol = mycol + 1;

------------------ Original Answer (Dec 2012) ------------------

Simplest way is a CRON job or Windows Task Scheduler job to run the mysql command:

mysql -u [username] -p[password] -e "UPDATE from 'filecsv'"

Notes:

1. This will reveal your password in the scheduler.
2. If mysql is not in your path (that the sceduler will run as), specify the path in the line. If in doubt, put "c:\progam files(x86)\mysql\bin\mysql" or wherever the mysql.exe is located
3. While there is a space between -u and username, and between -e and the comment, there is NOT a space between -p and the password.

More reading: - Google for tech scheduler (add your flavour of windows) https://www.google.com.au/search?q=using+windows+task+scheduler - MySQL command line "execute" (and other options) http://dev.mysql.com/doc/refman/5.5/en/mysql-command-options.html#option_mysql_execute

Answer 2

There is not way to do that from phpmyadmin from what I know, you can do it however from the scripts, this will allow you to create a cronjob in linux or a schedueled task in Windows to import the CSV into your database. phpMyAdmin runs a command like this anyway.

The SQL command you are looking for is

load data ' . LOCAL_DATA . ' 
infile "/path_on_disk/file.csv" 
into table `table_name` ' . $details['import_options'] . ' 
(brand, sku, name, retail_price, url, image, short_category, stock_availability, freight_cost, retail_price, currency);');

LOCAL_DATA - can either be empty or the string "LOCAL". Test to see how it runs for you, for my computer I need LOCAL, for the server I do not.

$details['import_options'] - can be a number of things, again the best way to do it is to test to see how it runs for your file. I have used in the past

fields terminated by "," lines terminated by "\n" IGNORE 1 LINES

or

fields terminated by "\t" enclosed by "\"" lines terminated by "\n" IGNORE 1 LINES

or

fields terminated by "," optionally enclosed by "\"" lines terminated by "\n" IGNORE 1 LINES

you can run this in php to get the file you want in the table you want.