2

MySQL. Two columns, same table.
Column 1 has product_id
Column 2 has category_ids (sometimes 2 categories, so will look like 23,43)

How do i write a query to return a list of product_id, category_ids, with a seperate row if there is more than 1 category_id associated with a product_id.

i.e
TABLE:

product_id | category_ids 
100        | 200,300
101        | 201

QUERY RESULT: Not trying to modify the table

100 | 200
100 | 300
101 | 201


EDIT: (note) I don't actually wish to manipulate the table at all. Just doing a query in PHP, so i can use the data as needed.

jason3w
  • 715
  • 1
  • 7
  • 17
  • Why are you saving category_ids comma seperated? It is not normalised form. Your desired result structure should be there. – Somnath Muluk Jun 19 '12 at 12:06
  • This is almost an exact duplicate of this question: http://stackoverflow.com/questions/10293861/split-comma-seperated-values-in-column-mysql/10294021#10294021 – Gustav Bertram Jun 19 '12 at 12:07
  • Another question that is an exact duplicate: http://stackoverflow.com/questions/4890968/comma-seperated-value-seperation – Gustav Bertram Jun 19 '12 at 12:11
  • And also http://stackoverflow.com/questions/5342629/mysql-split-data-into-multiple-rows/5344071#5344071 – Somnath Muluk Jun 19 '12 at 12:13
  • i didn't create the table. just trying to get some data out. Checked out the link Gustav. No luck with a clear answer. – jason3w Jun 19 '12 at 12:13
  • sorry didn't make it clear, i don't wish to chang ethe table at all. just running a query. – jason3w Jun 19 '12 at 12:19
  • @jason3w unfortunately doesn't exist a reverse function of GROUP_CONCAT in mysql, would have been the one right for you ... – aleroot Jun 19 '12 at 12:29

1 Answers1

1

Your database table implementation seems bad designed, however in your case what you need would be a reverse function of GROUP_CONCAT, but unfortunately it doesn't exist in MySQL.

You have two viable solutions :

  1. Change the way you store the data (allow duplicate on the product_id field and put multiple records with the same product_id for different category_id)
  2. Manipulate the query result from within your application (you mentioned PHP in your question), in this case you have to split the category_ids column values and assemble a result set by your own

There is also a third solution that i have found that is like a trick (using a temporary table and a stored procedure), first of all you have to declare this stored procedure : 

DELIMITER $$
CREATE PROCEDURE csv_Explode( sSepar VARCHAR(255), saVal TEXT )
body:
BEGIN

  DROP TEMPORARY TABLE IF EXISTS csv_Explode;
  CREATE TEMPORARY TABLE lib_Explode(
    `pos` int unsigned NOT NULL auto_increment,
    `val` VARCHAR(255) NOT NULL,
    PRIMARY KEY  (`pos`)
  ) ENGINE=Memory COMMENT='Explode() results.';

  IF sSepar IS NULL OR saVal IS NULL THEN LEAVE body; END IF;

  SET @saTail = saVal;
  SET @iSeparLen = LENGTH( sSepar );

  create_layers:
  WHILE @saTail != '' DO

    # Get the next value
    SET @sHead = SUBSTRING_INDEX(@saTail, sSepar, 1);
    SET @saTail = SUBSTRING( @saTail, LENGTH(@sHead) + 1 + @iSeparLen );
    INSERT INTO lib_Explode SET val = @sHead;

  END WHILE;

END; $$
DELIMITER ;

Then you have to call the procedure passing the array in the column you want to explode : 

CALL csv_explode(',', (SELECT category_ids FROM products WHERE product_id = 100));

After this you can show results in the temporary table in this way : 

SELECT * FROM csv_explode;

And the result set will be :

+-----+-----+
| pos | val |
+-----+-----+
|   1 | 200 |
|   2 | 300 |
+-----+-----+

It could be a starting point for you ...

aleroot
  • 71,077
  • 30
  • 176
  • 213
  • A dev achieved something similar for me before with this (but i don't know how to translate and use again): SELECT p.prodname, c.combinationid, CONCAT(o.voname),CONCAT(vovalue) FROM isc_product_variation_combinations AS c LEFT JOIN isc_product_variation_options AS o ON find_in_set(o.voptionid, c.vcoptionids) LEFT JOIN isc_products AS p ON p.productid = c.vcproductid WHERE c.vcproductid = 255 – jason3w Jun 19 '12 at 12:32
  • this was combining data from a few tables though. So thought it might be pretty easy with just one table. – jason3w Jun 19 '12 at 12:33
  • The query you posted join two different table, is another thing. In that case you have a table with a single id and you join with that column using FIND_IN_SET function ... – aleroot Jun 19 '12 at 13:02