Where is the file located in Load Data Infile?

Question

If I use a Load Data Infile MySQL query in PHP, where is the file location relative to? Is it the same folder as the PHP file?

Answer 1

No, that path is a path on the server-side. So the file must be on the same machine as the MySQL server is.

I'm not sure about the working directory, but it should be the one where the database files reside (/var/...). You can use absolute path to be sure.

But as you can see, because having to involve the server machine, this is not a long-term solution.

Answer 2

From http://dev.mysql.com/doc/refman/5.1/en/load-data.html see the section that starts

If LOCAL is not specified, the file must be located on the server host and is read directly by the server. The server uses the following rules to locate the file:

about a page and a half down.

Basically, you should specify a complete path for the file. Relative paths are relative to the MySQL server directory.

If you want to upload a file from the web server where php is running to the database server where mysql is running (if these are different machines -- often they are the same machine) apparently Mysql 5.1 has a mechanism for that and thats what LOCAL is for. I've never used that, though.

Answer 3

With LOAD DATA INFILE, the file name is relative to the server.

With LOAD DATA LOCAL INFILE, the file is relative to the client.

If both server and client reside on the same machine, you shold use LOAD DATA INFILE, because it can handle key violations and other errors much more nicely.