Importing Complicated Data into mySQL via CSV

[frank5050]

Hi PHP/mySQL Gurus,

 

Newbie here, barely know enough to get around.

 

 

I am importing data into mySQL from a CSV file. The data consists of various fields involving customer information and the products they ordered.

 

 

All the information pertaining to the customers is easily importable since most of the information is in separate columns of the cSV file and I created a column for each item in my database table and imported the customer information.

 

 

The hard part is importing the "products" that each customer orders.

 

Given below is the information contatined in "1" column of the CSV file called Product_Details.

 

AS an example:

 

A customer placed a particular order# 4755. The customer ordered 3 different products (and a total of 5 items), the Product_Details column that I need to import into mysql table includes the following info:

 

 

Product ID: 8457, Product Qty: 3, Product SKU: M-242, Product Name: DCE/DTE DB60 Crossover Cable - 1FT, Product Weight: 1, Product Variation Details: , Product Unit Price: 4.68, Product Total Price: 14.04|Product ID: 8800, Product Qty: 1, Product SKU: M-1038, Product Name: RJ45 8P8C Plug Flat Stranded 50pcs/Bag, Product Weight: 1, Product Variation Details: , Product Unit Price: 3.42, Product Total Price: 3.42|Product ID: 8940, Product Qty: 1, Product SKU: M-1385, Product Name: RJ11/12/45 Crimp Tool with Ratchet [HT-568R], Product Weight: 1, Product Variation Details: , Product Unit Price: 9.89, Product Total Price: 9.89

 

 

Few observations in the above data:

 

Each attribute is separate by a comma.

Each item ordered by the customer is separated with a "|".

 

 

Where I need help:

 

1- Most importantly: How do I import the above data in a table so that I am able to search each order and individual attributes (Product ID, Product Qty, Products SKU, Product Name, Product Weight, Product Variation Details, Product Unit Price, Product Total Price). Should they all reside in separate columns?

 

 

2- Since I have already created a table called 'customers' where I have imported customer related info (not the products they ordered). I am thinking (please let me know if I'm thinking correctly or not) that I create a separate table for just the products, where I import the Order ID (4755 in the above example) and Product_Details. Then join queries could be written as needed? would it make more sense to reside just the product related info in a separated table instead?

 

All help is appreciated.

 

Thanks in advance.

[fenway]

TLDR -- simply your scenario and clarify your questions.

[frank5050]

I am not sure how else I could simplify my scenario, because what I explained is exactly what I am faced with.

 

 

Also, I tried to be quite detailed with my questions/explanation.

 

 

Any help?

[fenway]

Sounds like you need to parse out that field... and otherwise, yes, a JOIN will work quite well.