mysql split comma values to a single new column possible?

Question

I am exporting my products from prestashop for magento2, but some of my products in prestashop have multiple images (not all)

Here is the code im using now.

select ps_product.id_product AS 'sku', ps_product.upc, ps_product.price, ps_product.weight, ps_product.date_add AS 'created_at',
GROUP_CONCAT(DISTINCT(CONCAT('/img/p/',
IF(CHAR_LENGTH(ps_image.id_image) >= 5, 
CONCAT(
SUBSTRING(ps_image.id_image, -5, 1),
'/'),
''),
IF(CHAR_LENGTH(ps_image.id_image) >= 4, CONCAT(SUBSTRING(ps_image.id_image, -4, 1), '/'), ''),
IF(CHAR_LENGTH(ps_image.id_image) >= 3, CONCAT(SUBSTRING(ps_image.id_image, -3, 1), '/'), ''),
IF(CHAR_LENGTH(ps_image.id_image) >= 2, CONCAT(SUBSTRING(ps_image.id_image, -2, 1), '/'), ''),
IF(CHAR_LENGTH(ps_image.id_image) >= 1, CONCAT(SUBSTRING(ps_image.id_image, -1, 1), '/'), ''),
ps_image.id_image,
'.jpg')) SEPARATOR ', ') AS 'base_image',
ps_stock_available.quantity AS 'qty', ps_product_lang.description, 
ps_product_lang.name,
ps_product_lang.description_short AS 'short_description', ps_product_lang.link_rewrite AS 'url_key',
ps_product_lang.meta_title, ps_product_lang.meta_description,
'simple' AS product_type, 'Default' AS attribute_set_code, 'All' AS categories
from ps_product
INNER JOIN ps_product_lang ON ps_product.id_product=ps_product_lang.id_product
INNER JOIN ps_stock_available ON ps_product.id_product=ps_stock_available.id_product
INNER JOIN ps_image ON ps_product.id_product=ps_image.id_product
GROUP BY ps_product.id_product
ORDER BY ps_product.id_product;

As of now for the most part base_image has one, but we have about 200 of the products out of 1600 that have more then one image which magento flips about.

I have read up on substring_index but all the examples are for them being a new column for each value. What I want to accomblish is splitting anything more then one value into a second column called "additional_images".

Example: 1.jpg, 2.jpg, 3.jpg

1.jpg goes to base_image and 2.jpg and 3.jpg goes to additional_images as the comma values like they where.

Any help would be great!

Here https://stackoverflow.com/questions/46890617/restructuring-a-bad-database-with-php-loops-or-mysql/46892143#46892143 i've posted a method to dynamic split comma separated values. — Raymond Nijland, Feb 13 '18 at 15:54

score 1 · Accepted Answer · answered Feb 13 '18 at 15:36

Here is one method:

select substring_index(images, ',', 1) as first_image,
       trim(leading ',' from
            replace(images, substring_index(images, ',', 1), '')
           ) as remaining_images

I think the replace() is the simplest method to get the rest of the images, if any.

Hmmm . . . This is safer:

select substring_index(images, ',', 1) as first_image,
       (case when images like '%,%'
             then substr(images, instr(images, ',') + 1)
        end) as remaining_images

Could I do this after the logic for the images in my script? Or would I need to build it in with it? — ajankuv, Feb 13 '18 at 15:56

mysql split comma values to a single new column possible?

1 Answers1