1

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?

MiloMal
  • 21
  • 4
  • 1
    Have you already tried to use the `$_FILES['userfile']['tmp_name']` to get the server side address? – dvaltrick Aug 27 '19 at 16:36
  • as your php is served thanks to an http server you have to provide the absolute filename but you can't do this thanks to basic browser feature (javascript) so 2. is not an option. https://stackoverflow.com/questions/15201071/how-to-get-full-path-of-selected-file-on-change-of-input-type-file-using-jav – Tuckbros Aug 27 '19 at 17:19

1 Answers1

1

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.

Dave
  • 5,108
  • 16
  • 30
  • 40
  • You may also need to get the path https://www.php.net/manual/en/features.file-upload.post-method.php Files will, by default be stored in the server's default temporary directory, unless another location has been given with the upload_tmp_dir directive in php.ini. The server's default directory can be changed by setting the environment variable TMPDIR in the environment in which PHP runs. – Tuckbros Aug 27 '19 at 17:29
  • I have tried using the tmp_name option, but it still does not work. I think the key lies in finding out where MySQL picks up files from in the LOAD DATA command. – MiloMal Sep 03 '19 at 10:35
  • You specify where the file is that is used with `LOAD DATA`. Use `print_r($_FILES['userfile']['tmp_name']);` to find out exactly where the file is. Passing that to the SQL command should work just fine. – Dave Sep 03 '19 at 13:35