CODEWITHSUNDEEP
phphtmlmysql
MiloMal
MiloMal

Reputation: 21

How to provide file and directory name to LOAD DATA statement in PHP

I am using PHP to load the contents of a CSV file into a MySQL table using MySQL's "LOAD DATA" command, as this is faster than parsing and processing the file line by line.

The SQL query I am using is this:

LOAD DATA LOCAL INFILE 'c:/work/members.csv' INTO TABLE tempmember 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' 
LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES;

This works fast and well; it loads the file directly from the user's computer and there are no problems with it. What I would like to do now is allow the user to select a file using a selection dialog box on their PC, send the name of the file (including directory name) to the server as a POST variable ('filename'), and use it within the LOAD statement, thus:

$filename = $_POST['filename']

$query = "LOAD DATA LOCAL INFILE '".$filename."'INTO TABLE..." etc.

Sending that file name is the issue. I tried using a kludge by actually sending the file to the server using 'input type="file"' in a form, and then picking up the file name on the server from $_FILES['userfile']['name'] but this only provides the filename without the folder name.

I see three possible solutions to the problem:

  1. Have the user type in the folder and file names rather than select from a dialog box. Typing errors could then be a problem.

  2. Is there a file selection dialog box available that can return the directory and file names, without having to upload the file?

  3. Use the input type="file" option, upload the file to the server, and place it in a folder that can be directly accessed by the LOAD DATA statement. I would first have to remove the word "LOCAL" from my SQL query. Question is: Where can the I put the file so that it can be accessed by MySQL. The documentation on this is confusing, to say the least.

My favored option would be number 2. Does anyone have answers to 2 or 3?

Upvotes: 1

Views: 526

Answers (1)

Dave
Dave

Reputation: 5191

The name on the users computer doesn't matter at all. What you want to use is the $_FILES['userfile']['tmp_name'] variable from the POST values in your code. That will be a complete path to the file that was uploaded.

Upvotes: 1

Related Questions