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

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,'') <> ''

ASKER CERTIFIED SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial