Re: Automating LOAD DATA INFILE with data collected regularly on an other system (Windows)

[Les Dee]

I think I am nearly there.

It turned out that the people hosting my web site do not allow LOAD DATA LOCAL INFILE so I now have a job running on the remote system which regularly uploads any new batches (uniquely named with date/time).

On the web site I have a php routine which is kicked off via a cron job at regular intervals scanning for received files.

Each such file is then processed by running a LOAD DATA INFILE command for the file.

I have the basic code working but I have a couple of issues with logging the output when the table update is successful.

For testing, I run the mysql command interactively and it shows a message confirming the number of records that have been processed.

When running the command in php using shell_exec I redirect errout so I can read back the errors, but I can't find a way to get hold of the confirmation messages.

I am running in shell_exec something like:

mysql -u user --password=xyz databasename < sqldata.csv 2> errout.txt

I have tried to redirect stdout but the file is always empty.

I have also assigned in php the output of shell_exec to a variable but that too is empty.

So how do I get hold of the confirmation messages.

Also, errout always has the line 'Warning: Using a password on the command line interface can be insecure.' in it. Is there some way to stop that - I would much prefer an empty errout file if there are no errors.

Thanks