LOAD DATA LOCAL INFILE MySQL/PHP issue

问题:

Here is my MySQL table structure

id    |   tracking_number   |   order_id

Here is the structure of the CSV file: (Sometimes the order_id is missing, and this seems to be causing issues)

"1R2689Y603406","33097"
"1R2689Y603404","33096"
"1R2689Y603414",
"1R2689Y603429","33093"
"1R2689Y603452",

Here is my current SQL Query which isn't working: (The file is being uploaded, and is being read correctly, it's the query itself which is causing issues)

        $sql = 'LOAD DATA LOCAL INFILE "'.$_FILES['my_file']['tmp_name'].'" 
        INTO TABLE table_tracking
        FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "\""
        LINES TERMINATED BY "\n" 
        (tracking_number,order_id)';

        mysql_query($sql) or die(myqsl_error());

What is wrong with my query? Thanks for any help!

Edit: Changed CSV structure to represent missing data that sometimes occurs. Changed query to match the one I am now using

回答1:

Just trying here (not 100% confident) but did you try adding the destination columns like this:

mysql_query("LOAD DATA LOCAL INFILE '".$_FILES['my_file']['tmp_name']."' 
     INTO TABLE table_tracking FIELDS TERMINATED BY ',' 
     OPTIONALLY ENCLOSED BY '\"' 
     LINES TERMINATED BY '\n'
     (tracking_number,order_id)");

You have three columns in your table and are only providing two data.

回答2:

This worked for me:

$sql = 'load data local infile "c:/users/ramon/desktop/1.csv"
        into table test fields terminated by ","
        optionally enclosed by "\""
        lines terminated by "\n"';

mysql_query($sql) or die(myqsl_error());

You also probably need to make sure that your third column has a default value.

回答3:

Instead of $_FILES['my_file']['tmp_name'], try

dirname(__FILE__)."/".$_FILES['my_file']['tmp_name']

dirname(__FILE__) will give the full directory path like 'C:/path/to/directory'.