Update MySQL from CSV file Every 10 Minutes

php mysql csv cron

53,374

cron is going to be the way to go. Depending on the size of the file loaded it might be an expensive task (in terms of time). I would suggest having the cron upload the file to a temp table then within a transaction drop the old table then rename the temp table to the correct table name. If the file is relatively small then it might make sense to walk each line in the file and determine if based on querying the database a record needs to be added or updated.

53,374

Author by

MillerMedia

Thanks for the help! Any programmers looking for subcontractor work, feel free to e-mail me at [email protected] . We're always looking for great workers. Thanks!

Updated on June 28, 2020

Comments

MillerMedia almost 4 years

I am relatively new to MySQL and am looking for a way to run a MySQL update every 10 minutes. Basically there is a central CSV file that is updated every ten minutes on a server that we grab the data from. I am trying to set up a MySQL table that will read that CSV file and load it into the MySQL table every ten minutes.

I know that you can link a MySQL table to a CSV file directly using 'load data.' My confusion comes with the actual automatic updating. I've done some research and read about 'cron' jobs which will perform automated tasks at set intervals. My thought is to have the PHP script run by the cron job every ten minutes (if the host allows it).

I'm just wondering if this is best practice. I've been searching around and don't see many other options and wanted to see if you all knew of any other methods that I may be missing so I can make a proper assessment of this job. Thanks for your help!
- matzone almost 11 years
  
  It's Javascript's order ....
MillerMedia almost 11 years

It's not too terribly big. The CSV file only has like 8 columns on it and it would probably be 100 rows at most at a time so it wouldn't have any issue on that end. I'm assuming the online cron services like getcron and onlinecronjobs are reliable?
Orangepill almost 11 years

Can't speak that but most hosts I've dealt with have some form of task scheduler
Orangepill almost 11 years

Another option is to have the server process that is creating the file use curl to post the file to your importer.
MillerMedia almost 11 years

Ok great, this gives me a good start. Thanks so much for your help!