CODEWITHSUNDEEP
phpmysql
starleaf1
starleaf1

Reputation: 2872

Separating a single linebreak-separated value into multiple values in MySQL

I have a table that puts multiple values into a single value in MySQL, separated by linebreaks. Like this:

+------------+-------------+
|    Company | Products    |
|  (VARCHAR) | (TEXT)      |
+------------+-------------+
|  Acme Corp | Medicine    |
|            | Food        |
|            | Phones      |
+------------+-------------+
|  Ajax Corp | TVs         |
|            | Phones      |
|            | Pianos      |
+------------+-------------+

I can't do anything about the table structure. Now I need a query that will return this table:

+==========+
| Products |
+==========+
| Food     |
+----------+
| Medicine |
+----------+
| Phones   |
+----------+
| Pianos   |
+----------+
| TVs      |
+----------+

I prefer a pure MySQL approach, but a solution with PHP is also OK for me.

Upvotes: 1

Views: 54

Answers (2)

Mihai
Mihai

Reputation: 26784

This will work for 3 products maximum,for more it needs a bit of tinkering 

CREATE TABLE t 
    (

     company varchar(20), 
     products text
    );
INSERT INTO t
VALUES
('Acme', 'Medicine,Food,Phones'),
('ajax', 'TVs,Phones,Pianos');  



SELECT SUBSTRING_INDEX(Products,',',1)  FROM t
UNION
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(Products,','),',',2),',',-1) FROM t
UNION
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(Products,','),',',3),',',-1) FROM t
WHERE SUBSTRING_INDEX(Products,',',1)<>''
AND SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(Products,','),',',2),',',-1) <>''
AND SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(Products,','),',',3),',',-1)<>''

Results

Medicine
TVs
Food
Phones
Pianos

Upvotes: 1

Casimir et Hippolyte
Casimir et Hippolyte

Reputation: 89557

There are no handy ways to split a field with MySQL. (Check the comments in the MySQL documentation). So, the best method seems to obtain all the product records with a simple:

SELECT Products FROM YourTable

And after in you php code:

$products = array_merge($products, explode("\n", $record));

for each record.

Upvotes: 2

Related Questions