0

I've got an ecommerce database where I'm trying to export all of the image names associated with a product into a csv file.

I'm writing it in php and running the script through the admin of the ecommerce system but I'm struggling to print out the results in a way I would like them.

The two tables involved are: CubeCart_inventory which contains the product information, and CubeCart_img_idx which contains the associated images.

The tables are linked by a 'productid' so in the first table the product id is: CubeCart_inventory.productId and in the image table the id CubeCart_img_idx.productId.

Is there an SQL statement that I can use that puts all of the image names into a string which I can then use to write my csv on a product by product basis?

Example of the CubeCart_inventory table:

id | productId | productname  | price
____________________________________
1  |    1234   | Bag of Nails | £35

Example of the CubeCart_img_idx table

id | productId | img
____________________
 1 | 1234      | nails.jpg
 2 | 1234      | nails2.jpg
 3 | 5678      | otherproduct.jpg
 4 | 1234      | nails3.jpg

So what I would like is: nails.jpg, nails2.jpg, nails3.jpg all in one field in my CSV file because they are joined by the same productId

Jan Doggen
  • 8,799
  • 13
  • 70
  • 144
Gavin
  • 61
  • 2
  • 6

1 Answers1

0

For MySQL, looks like GROUP_CONCAT is what you're looking for. For SQL Server, you can use a combination of STUFF with a subquery.

itsadok
  • 28,822
  • 30
  • 126
  • 171