i have built a CMS website that replies upon the mysql records of a 3rd party. i intend to download the 3rd party records via CSV. i then want to use a Cron job via my command line to update my mysql records
i know how to manually upload to mysql via CSV i.e:
LOAD DATA INFILE 'c:/tmp/discounts_2.csv'INTO TABLE discountsFIELDS TERMINATED BY ',' ENCLOSED BY '"'LINES TERMINATED BY '\n'IGNORE 1 ROWS(title,@expired_date,amount)SET expired_date = STR_TO_DATE(@expired_date, '%m/%d/%Y');
however, i am not sure how to go about creating a cron job that will automtically link to the command line and then perform this function.
i guess that what i want is a pointer on how i can go about doing this.
mysql -u LOGIN -p PASSWORD DATABASE_NAME < /path/to/import.sql
where LOGIN, PASSWORD and DATABASE_NAME are your database credentials and import.sql is the file with your query (LOAD DATA …) inside
To make this work you should know path to site root folder , something like /var/www/site.com (ask hosting support to get correct path). Then you’ll create import.sql and data.csv in that folder (or any subfolder). In the query you’ll change path to the new one:
LOAD DATA INFILE '/var/www/site.com/data.csv' ....
and add command in crontab:
mysql -u LOGIN -p PASSWORD DATABASE_NAME < /var/www/site.com/import.sql