load data local infile

nickbesseling

Hi.
Im having an issue with using LOAD DATA LOCAL.

I developed some functionality that worked fine on our testing system and when it was switched over to the clients host it was discovered that they ran their web and db servers seperately and having no access to the db server directly I realised that Id need to use the LOCAL keyword.

heres my code.

mysql_query("LOAD DATA LOCAL INFILE 'shipping.csv' INTO TABLE orders FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'");

the problem is that the file isnt getting read by the db but returns no errors.

the question where is the 'local' file actually need to sit?
the actual user computer or the web server? and how should it be pathed?

as I mentioned the actual csv file is fine getting read into the ssytem directly on the server so Im assuming that the problem is the location of the file.

I read read all the documentation and forums and tried all locations and paths but still cant seem to get it into the right place.

can anyone help?
cheers.
Nick

nnichols

When 'LOCAL' is specified it means that the file is located on the same machine as the mysql CLIENT, whereas without 'LOCAL' the server reads the file directly.

This means that the file must get uploaded from the users computer to the webserver and then it should be referenced using the full path of the folder it has been uploaded to.

Hope this helps 😉

nickbesseling

I appreciate the reply.

definitely showing my lack of knowledge here but what is mysql CLIENT referring to? the actual machine with the scripts opening the db? i.e in my case the webserver?

I have the file on the webserver so Im assuming that for some reason my pathing is incorrect (which Ive suspecte all along)

the problem is that I can seem to get the correct pathing.

ive tried absolute INFILE 'http://www.domain.com/file' which hasnt worked.
ive kept the csv file in the same directory just left as INFILE 'file.csv'
as the script file calling it which hasnt worked either.

basically ive tried every combination of pathing but still cant seem to find the file

nnichols

CLIENT refers to the location of the libraries that actually run the sql against the db. In your case it is PHP which uses the C libraries that PHP is compiled with.

This means for the path you have to use the full nix (I assume you are on nix) path to the file. Something like -
/home/sites/sitename/tmp/uploads/

If you telnet into the server and navigate to the folder containing the files you upload and type 'pwd' it will print the full path to the current directory.

Hope this helps 😉