Search Posts

PHP Load Data Infile to MySQL Table Certain Columns

I have a CSV file that I need to insert into a MySQL table. However the CSV file has a different number of columns to the table, and the columns are in a different order.

The table has these columns:
ID, Product_ID, Order_ID, Shipping_Name, Shipping_Postcode, Tracking_Number

And the CSV has:
Tracking_Number, Order_ID, Name, Postcode
(The Product ID is the name of the file and I can set this as a variable in PHP then update the column, so that’s okay)

I know that you can create fake columns using @ in the query to skip certain columns in the table, but I am not sure how to link columns that are in a different order, or if it is even possible.

Here is my query that I use for when the columns match up:

LOAD DATA INFILE '/var/www/html/imports/tracking/$file'
    IGNORE INTO TABLE tracking
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY 'n'
    IGNORE 1 LINES
    (@product_id, @order_id, @shipping_name, @shipping_postcode, @tracking_number)
    SET [email protected]_id, [email protected]_id, [email protected]_name, [email protected]_postcode, [email protected]_number";

Can I adjust this for what I need to acheive?

Source: Stackoverflow

Leave a Reply

Your email address will not be published. Required fields are marked *