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?
------------------- 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:
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
users SET col = '1' WHERE name = 'matt'" .... Do i need the mysql? am I using use correctly? The docs Im looking at don't usually execute this code one 1 line..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.
You might be better off scheduling a Cronjob to run a script to carry this operation out. You can create a PHP script that examines the Excel file and then enters it into MySQL.
http://www.cyberciti.biz/faq/how-do-i-add-jobs-to-cron-under-linux-or-unix-oses/ http://code.google.com/p/php-excel/
Alternatively, you can use the event scheduler in MySQL to schedule specific operations. However, for your application, PHP or another scripting language may be better suited to handle reading from an Excel spreadsheet.
Example:
CREATE EVENT import_data
ON SCHEDULE AT '2006-02-10 23:59:00'
DO LOAD DATA LOCAL INFILE '/importfile.csv' INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);
