How to convert string value separated by comma into integer from mySQL

Question

I am working on a php project to retrieve data from mysql. I have a list of codes that is stored as a string separated by commas that is referenced in another table. Is there a way to get all the value from the string and return the text it referenced?

For example, item_purchased might contain one or more than one item_code. I want the query to return item names instead of item codes.

//item_purchased for transaction 123 --> ,111,222,333,

SELECT s.transaction_id, s.item_purchased, i.item_id
FROM stock s

INNER JOIN ref_item i
ON s.item_code = i.item_code

WHERE transaction_id = 123

Desired outcome: apple, carrot, milk (not ,111,222,333,)

Is there a way to do this preferably within mySQL query or maybe in PHP?

this is why you normalise the db, it would be simple if you did not use the comma separated list. — user557846
– user557846, Commented Sep 22, 2014 at 5:49
why cant you use explode() in php after fetching whole string from db — Salini L
– Salini L, Commented Sep 22, 2014 at 5:58
query for the list of names as well, turn the string in an array, you should be able to reference it like $arr[$item_id] — RST
– RST, Commented Sep 22, 2014 at 5:59
This is the proof for a very-bad database design. Cant you change this comma seperated list into a proper table structure? Its horrible! — Steini
– Steini, Commented Sep 22, 2014 at 6:23

Community · Accepted Answer · 2017-05-23 12:12:47Z

3

This is one of the reasons why you shouldn't use comma-separated lists in relational databases.

The workaround is to use the FIND_IN_SET() function in the join conditions, instead of =.

SELECT s.transaction_id, GROUP_CONCAT(i.item_name)
FROM stock s

INNER JOIN ref_item i
ON FIND_IN_SET(i.item_code, s.item_purchased)

WHERE s.transaction_id = 123

GROUP BY s.transaction_id

But unfortunately, this makes the query very inefficient, because it can't use an index to search the ref_item table. It has to do a table-scan, so it ends up having very poor performance, and gets much worse the larger your table gets.

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Sep 22, 2014 at 6:05

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cryssie Over a year ago

Thanks I didn't know it's possible to do this in mySQL. If performance is affected I might do the conversion in PHP first before calling the db.

andrex · Accepted Answer · 2014-09-22 06:20:39Z

0

Is item_purchased a comma separated string of item_code? I'm not good with joins but I think this will do

SELECT s.transaction_id, s.item_purchased, DISTINCT(i.item_id)
  FROM stock s, ref_item i
 WHERE i.item_code in (s.item_purchased ) 
   AND s.transaction_id = 123

This will return a list of all the items in the item_purchased column if my assumption of the item_purchase is right.

answered Sep 22, 2014 at 6:20

andrex

1,0326 silver badges16 bronze badges

1 Comment

Bill Karwin Over a year ago

That's a syntax error. You can't use DISTINCT like it's a function.

Collectives™ on Stack Overflow

How to convert string value separated by comma into integer from mySQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related