在php中的单独列表上匹配值

Question

Sorry if this is a little long winded but here's some background.

I'm migrating some products from one shopping cart to another. the original cart is very old and the export file only gives the option id for different product options. eg. if a product comes red, yellow and blue it might give me the option ids 1,2 and 3.

I have managed to get a separate list showing all available option ids and the option it refers to.

So, List A shows me each product and the option IDs available to it like so:

List A

In this example product_id 1 could be a T shirt, option_id 11 could be "colour" and the various option_value_ids could be red, blue, yellow etc

List B shows me what each option_value_id means in real terms like so:

List B

Basically I'd like to write a PHP script which will compare the two lists and for each unique product ID then for each unique option ID, output a line of text in the following format:

product_id;option_id; list of option values separated by commas eg

1;11;red,blue,green,yellow...

I'm fairly new to PHP and started by putting together a webform so I could paste in the data from the spreadsheet but I think I'm barking up the wrong tree. I then thought maybe comparing them as 2 MySQL tables in a database?

Could someone please point me in the right direction?

Answer 1

SELECT a.product_id, a.option_id, GROUP_CONCAT(DISTINCT b.option_name SEPARATOR ', ') as option_names 
FROM listA a 
LEFT JOIN listB b ON a.option_value_id = b.option_id
GROUP BY a.product_id, a.option_id;

Should work - I'm writing it from memory :) If not - try to tune it up. You can export it then to CSV.