2

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?

Cryssie
  • 3,047
  • 10
  • 54
  • 81
  • this is why you normalise the db, it would be simple if you did not use the comma separated list. –  Sep 22 '14 at 05:49
  • why cant you use explode() in php after fetching whole string from db – Salini L Sep 22 '14 at 05: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 Sep 22 '14 at 05: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 Sep 22 '14 at 06:23

2 Answers2

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.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 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. – Cryssie Sep 22 '14 at 06:24
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.

andrex
  • 983
  • 5
  • 14