Kyle.Belanger Kyle.Belanger - 5 months ago 25
PHP Question

Load data in file from form upload

I'm trying to insert data into MySQL table using the load data infile query. I have an HTML form that accepts a text file for upload, and then tries to process.

File

00|Oakleaf Forest|Norfolk|VA|United States|PC
01|Igo|Lowell|MA|United States|PC
02|Mint|Lawrence|MA|United States|PC
03|Hosa|Boston|MA|United States|PC


HTML

<p>Please upload file below:</p>

<form action="process.php" method="post" enctype="multipart/form-data">
<input type="file" name="file" accept="text/plain">
<input type="submit">
</form>


PHP

/* processFile
* Process the submitted file
*
* @param Array - file for processing
*/
function processFile($file) {

// Get global variable
global $DB;

// file contents
$file_contents = $file["tmp_name"];

$SQL_statement = "LOAD DATA INFILE '$file_contents' INTO TABLE organization FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n' IGNORE 2 LINES
(ein, legal_name, city_name, state_name, country_name, description);";

// Run SQL query
$DB->execute($SQL_statement);
}


When I upload the file, nothing is inserted into the table.

I've tried debugging with
echo($SQL_statement);
, which returns the following query:

LOAD DATA INFILE '/Applications/XAMPP/xamppfiles/temp/phpOtL2q1' INTO TABLE organization FIELDS TERMINATED BY '|' LINES TERMINATED BY ' ' IGNORE 2 LINES (ein, legal_name, city_name, state_name, country_name, description);


What am I missing here?

Answer

LOAD DATA INFILE requires that the file be readable by the database process. I doubt that the temporary upload file is world-readable, it's probably only readable by the webserver userid.

One thing you can try is changing the permissions on the temp file first:

chmod($file["tmp_name"], 0444);

However, this still might not work if the directory containing the temp files is not world-readable. Another solution is to use LOAD DATA LOCAL INFILE. This reads the file in PHP, not the database server, so any file that the PHP process can read can be loaded. It's not as efficient, since all the file data has to be sent over the database connection, but it avoids permission problems.

So try the first method first, if it doesn't work try the second.