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 '
'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.
UPDATE @fancyPants asked the question "what do i want the cronJob to do?" the answer is this. i want the cronJob job to access the CSV file (that i have previously downloaded) and then upload all those values from the CSV file to the mysql database
i know the command for doing the CSV conversion to mysql;
LOAD DATA INFILE 'c:/tmp/discounts_2.csv'INTO TABLE discountsFIELDS TERMINATED BY ',' ENCLOSED BY '"'LINES TERMINATED BY ' 'IGNORE 1 ROWS(title,@expired_date,amount)SET expired_date = STR_TO_DATE(@expired_date, '%m/%d/%Y');
i am however stuck at the point of how to do a cron job to my command line and then how to execute the above command once the cron arrives at the command line
Create a MySQL event rather than a cronjob.
Enable the event scheduler with
SET GLOBAL event_scheduler = ON;
and create an event like this:
DELIMITER $
CREATE EVENT
ON SCHEDULE EVERY 1 DAY
STARTS '2015-10-11 00:00:00'
DO
BEGIN
TRUNCATE TABLE discounts;
LOAD DATA INFILE 'c:/tmp/discounts_2.csv'INTO TABLE discounts FIELDS TERMINATED BY ',' ENCLOSED BY '"'LINES TERMINATED BY '
'IGNORE 1 ROWS(title,@expired_date,amount)SET expired_date = STR_TO_DATE(@expired_date, '%m/%d/%Y');
END $
DELIMITER ;
and that's it.
Read more about the syntax here and here is more general information about it.