I would like to run every 6 hours, from a cron, a PHP script (or whatever else) that would update a MySQL table from a csv file present in FTP server.
What CSV Import/Export is very interesting, but I would like it to pick the CSV file from a directory instead of manually uploading it, because the name of the file will change: logdata_*******.csv).
Has anyone done such a thing ? Is it easily possible ? Any help would be appreciated. Thanks
Here's a php script that will guide you, you need to change it to your needs, I've put some explanations into it:
If you want to do this with ftp, you will also need to establish a ftp connection.
$db_connection = new mysqli("HOST","DB_USER","DB_PASS","DB"); // make mysqli connection to DB
$query = $db_connection->prepare("INSERT INTO yourtable (column1,column2) VALUES (?,?)");
$data = scandir('/PATH/TO/READ'); // Scan the dir your files are in
foreach ($data as $datas) {
if (0 === strpos($datas, 'logdata')) {
echo $datas."<br />"; //Show all the files that beginn with logdata
$file = 'PATH/TO/FILE/'.$datas;
unset($file[0]); //DELETE THE FIRST LINE IF FILE CONTAINS A HEADLINE ELSE DELETE THIS LINE
foreach ($file as $line)
{
$column = explode("\t", $line); // \t stands for tab-delimited, change it to what ever delimites your file (f.e. ; , . )
$example = $column[0]; // get content of first column
$example1 = $column[1]; // get content of second column
// .....
$query->bind_param('ss',$example, $example1)
$query->execute();
}}}
Let's call the file insertscript.php, then your cronjob would be:
0 */6 * * * nohup php /path/to/file/insertscript.php
nohup put's the proccess to the background and creates a file for every run. If you don't want this file, you can alter your cronjob to:
0 */6 * * * nohup php /path/to/file/insertscript.php >/dev/null 2>&1