Avatar of Tony Pearce
Tony Pearce
Flag for United Kingdom of Great Britain and Northern Ireland asked on

MySQL made multiple rows from delimited field

Hi,
I dont know if this is possible, I have data in the format:

code                subcode         ref      price      stock                  variants
SKU16927      SKU16927      0      51.01      1000                  colour=black/blue;size=S
SKU16927      SKU169270      0      51.01      1000                  size=S;colour=grey/white
SKU16927      SKU169271      0      51.01      1000                  size=M;colour=black/blue
SKU16927      SKU1692702      0      51.01      1000                  colour=grey/white;size=M
SKU16927      SKU169272      0      51.01      1000                  size=L;colour=black/blue
SKU16927      SKU1692703      0      51.01      1000                  size=L;colour=grey/white
SKU16927      SKU169274      0      51.01      1000                  size=XL;colour=black/blue

As you can see the last column contains two sets of data, (in this example size and colour but this field could contain more than shown) what I want to do is create the result below:

code                subcode         ref      price      stock                  variants
SKU16927      SKU16927      0      51.01      1000                  colour=black/blue
SKU16927      SKU169270      0      51.01      1000                  colour=grey/white
SKU16927      SKU169270      0      51.01      1000                  size=S

but only distinct 'variants' for main 'code' the 'subcode' is not important.

Any ideas??
MySQL Server

Avatar of undefined
Last Comment
Lee Wadwell

8/22/2022 - Mon
Lee Wadwell

How many 'sets' can exist in the variants column?  If only one or two ... this is should work OK  - not the prettiest solution, nor the most efficient.  But ...

select distinct code, subcode, ref, price, stock, variants
from (
	select code, subcode, ref, price, stock, 
               SUBSTRING_INDEX(CONCAT(variants, ';'),';',1) variants
	  from skudata
	union all
	select code, subcode, ref, price, stock, 
               SUBSTRING_INDEX(substr(variants,length(SUBSTRING_INDEX(variants,';',1))+2),';',1) variants
	  from skudata                                                /*          /|\ 
                                                                                   | 
                                                                    Increment for set, 1=2nd set, 2=3rd set etc */
) v
 where ifnull(variants,'') <> ''

Open in new window


If more than two - but still limited to a known amount - repeat the second select in the subselect (i.e below the "union all") and change the indicated value in the function to 2, 3, etc.

If an unknown number of 'sets' - a temp table and a procedure would be required.
Cornelia Yoder

It appears that this is a one-time change to your table.  If so, simply

CREATE TABLE skudatanew

with the same fields as skudata.

Write a simple php script that will

SELECT * FROM skudata

then

INSERT INTO SKUDATANEW a new row for each of the variations in variants in a simple php loop for each row from skudata.

After you have checked the result to see that your script ran properly,

DROP TABLE skudata

and

RENAME TABLE skudatanew TO skudata



The php processing loop should look something like this for each row of the original table.

$row = mysql_fetch_array($results);
$variants = $row["variants"];
$variantarray = explode( ";", $variants );
for($i=0; $i<count($variantarray);$i++)
  {  
     $thisvariant=$variantarray[$i];
     mysql_query("INSERT INTO skudatanew (..... ,$thisvariant)");  
  }

Note: You can use whatever php array loop you wish (for, for each, etc), but this one is simple and easy to understand.

This will handle any number of parts in the variants field.
Tony Pearce

ASKER
Hi

I have been TRYING to get this working as per first query, I have adjusted the sql to this but keep getting syntax errors:

select distinct code, optionprice, optionvalue
from (select code, optionprice, optionvalue,
               SUBSTRING_INDEX(CONCAT(optionvalue, ';'),';',1) optionvalue
        from shinyvar3
      union all
      selectcode, optionprice, optionvalue ,
               SUBSTRING_INDEX(substr(optionvalue,length(SUBSTRING_INDEX(optionvalue,';',1))+2),';',1) optionvalue
        from shinyvar3
 where ifnull(optionvalue,'') <> ''
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Lee Wadwell

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question