3

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?

JSW189
  • 6,267
  • 11
  • 44
  • 72
mattyd
  • 1,643
  • 2
  • 17
  • 26

3 Answers3

4

------------------- 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

Robbie
  • 17,605
  • 4
  • 35
  • 72
  • So this line of code I put in a file (with what extension?) then select that file with the Win task scheduler. The mysql command line interface will work after downloading and installing mysql. – mattyd Dec 19 '12 at 23:39
  • 1
    No it doesn'g go in a file at all. Hit "start", "Run", "cmd" and open a command window. Type in the command and it'll work. If you get "mysql not found" then add in the path. That shoudl do what you want. Then, use the Windows scheduler (plenty of help in google) and enter that command you typed in the command window. It's the simplest option - you're over complicating it by wanting to put it in a file. – Robbie Dec 19 '12 at 23:57
  • So what was throwing me off is this is for a PHPmyAdmin on a server. I see on my cPanel a 'cron jobs' icon to set up a new job. I imagine I would enter the command line code there. – mattyd Dec 20 '12 at 00:10
  • Yes - if you're in cPanel that that's the right icon. Also, the good news, mysql will be in the path and no-one else (except server admins) will see your password. Only problem than is you need to specify path for filecsv - but hopefully you got this sussed? – Robbie Dec 20 '12 at 00:20
  • I think im close... so my crone line is now.... mysql --user=username --password=password use my_db -e "UPDATE `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.. – mattyd Dec 20 '12 at 00:37
  • the mysql is needed.. I just feel I am not selecting the DB correctly, because I am using the same user and pass i use to gain access to phpMyAdmin and the query working once in PHPmyAdmin.. – mattyd Dec 20 '12 at 00:41
  • You can mix the database and hte table together, as in "UPDATE mydb.mytable SET..." so long as you have permissions with the connecting user, of course. Otherwise you need to string commands together using a semi colon ("use mydb; update mytable...") – Robbie Dec 20 '12 at 03:20
  • Do not forget to switch ON the event_scheduler in CREATE EVENT. SET GLOBAL event_scheduler=ON; – David Lopes Oct 07 '21 at 08:09
3

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.

Mihai P.
  • 9,307
  • 3
  • 38
  • 49
  • Is Cronjob the linux term and scheduled task the Windows term? I am not seeing great documentation about cronjobs from preliminary research. – mattyd Dec 19 '12 at 22:57
  • OK, so I see I can set up a daily script through Windows task wizard. Is this script like a php file that has the appropriate DB connect and UPDATE queries in it? – mattyd Dec 19 '12 at 23:03
2

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);

http://dev.mysql.com/doc/refman/5.1/en/events.html

Darius
  • 612
  • 2
  • 11
  • 23