PHP Load Data Infile to MySQL Table Certain Columns

Question

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 Product_ID=@product_id, Order_ID=@order_id, Shipping_Name=@shipping_name, Shipping_Postcode=@shipping_postcode, Tracking_Number=@tracking_number";

Can I adjust this for what I need to acheive?

ScaisEdge · Accepted Answer · 2018-02-12 12:22:28Z

1

you have not product_id so

just use the csv as position and the column table baded on indentier

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
    (@col1, @col2, @col3, @col4)
    SET  Order_ID=@col2, Shipping_Name=@col3, Shipping_Postcode=@col4, Tracking_Number=@col1";

answered Feb 12, 2018 at 12:22

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP Load Data Infile to MySQL Table Certain Columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related