Link to home
Start Free TrialLog in
Avatar of Tony Pearce
Tony PearceFlag for United Kingdom of Great Britain and Northern Ireland

asked on

MySQL made multiple rows from delimited field

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??
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

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.
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


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


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++)
     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.
Avatar of Tony Pearce



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,'') <> ''
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial