Load csv file to mysql using load data local infile

Question

I need a simple way to load a csv file from my computer to the database (which is on a remote server). I'm trying to load the file using a php page that executes this code:

    $file = $_FILES['csv']['tmp_name']; 
    $handle = fopen($file,"r"); 
    $name = $_FILES['csv']['name'];

   $import = "LOAD DATA LOCAL INFILE '" . $name .
                "' INTO TABLE temporal_load
                  FIELDS TERMINATED BY ','  
                  optionally ENCLOSED BY '\"' 
                  LINES TERMINATED BY '\\n' 
                  IGNORE 1 LINES
                  (num,ticker,company,sector,industry) ";

mysql_query($import) or die(mysql_error());

But when I select a file on the php page and try to load it, shows the error message:

File 'test11-14-15.csv' not found (Errcode: 2)

I already reviewed that the variable mysql.allow_local_infile is ON and the database connection was made this way:

mysql_connect(HOST,USER,PASSWORD,false,128);

The file is not on the remote server, the file is on my computer, but it should work anyway because I'm using LOCAL.

What am I doing wrong???

Answer 1

Your problem is on using the variable $name on your LOAD DATA LOCAL INFILE statement, you should use the variable $file in your statement..

Your statement should change to;

$import = "LOAD DATA LOCAL INFILE '" . $file. "' INTO TABLE temporal_load FIELDS TERMINATED BY ',' optionally ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (num,ticker,company,sector,industry) ";

mysql_query($import) or die(mysql_error());

Answer 2

You need to use $_FILES['csv']['tmp_name'] instead of $name in your import query.

So your php function might look like like:

$filename = $_FILES['csv']['tmp_name'];

$import = "LOAD DATA LOCAL INFILE '" . $filename . "' INTO TABLE temporal_load FIELDS TERMINATED BY ',' optionally ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES (num,ticker,company,sector,industry) ";

mysql_query($import) or die(mysql_error());