Delimited foreign ID's in a mysql field

Question

Preface: Yes i realize this is bad design, but i can't change this.

Question

I have a customers table, and within that a field 'products'. Here is an example of what is in a sample customers products field:

36;40;362

Each of those numbers reference a record from the products table. I'm trying to do a

(SELECT group_concat(productName) from products where productID=???)

but am having trouble with the delimiters. I know how to remove the semi colons, and have tried 'where INSTR' or IN but am having no luck.

Is the best approach to return the whole field to PHP and then explode / parse there?

score 4 · Answer 1 · answered Oct 09 '12 at 15:22

You can use FIND_IN_SET function in MySQL.

You just need to replace semicolons with a comma and the use it in your query:

SELECT group_concat(productName)
FROM products
WHERE FIND_IN_SET(productID, ???) > 0

Just remember that ??? should be comma-separated!

score 2 · Answer 2 · answered Oct 09 '12 at 15:27

Like you said, this isn't the way to do it. But since it's an imperfect world:

Assuming a database structure like so:

+-PRODUCTS---------+    +-CUSTOMERS---------+------------+
| ID | productName |    | ID | customerName | productIDs |
+----+-------------+    +----+--------------+------------+
|  1 | Foo         |    |  1 | Alice        | 1;2        |
+----+-------------+    +----+--------------+------------+
|  2 | Bar         |    |  2 | Bob          | 2;3        |
+----+-------------+    +----+--------------+------------+
|  3 | Baz         |    |  3 | Charlie      |            |
+----+-------------+    +----+--------------+------------+

Then a query like this:

   SELECT customers.*, 
          GROUP_CONCAT(products.id) AS ids, 
          GROUP_CONCAT(productName) AS names
     FROM customers
LEFT JOIN products
       ON FIND_IN_SET(products.id, REPLACE(productIDs, ";", ","))
 GROUP BY customers.id

Would return:

+-RESULT------------+------------+-----+---------+
| ID | customerName | productIDs | ids | names   |
+----+--------------+------------+-----+---------+
|  1 | Alice        | 1;2        | 1,2 | Foo,Bar |
+----+--------------+------------+-----+---------+
|  2 | Bob          | 2;3        | 1,2 | Bar,Baz |
+----+--------------+------------+-----+---------+
|  3 | Charlie      |            | 1,2 | NULL    |
+----+--------------+------------+-----+---------+

FIND_IN_SET( search_value, comma_separated_list ) searches for the value in the given comma separated string. So, you need to replace the semicolons with commas, which is obviously what REPLACE() does. The return value of this function is the position where it found the first match, so for example:

SELECT FIND_IN_SET(3, '1,3,5')   = 2
SELECT FIND_IN_SET(5, '1,3,5')   = 3
SELECT FIND_IN_SET(7, '1,3,5')   = NULL

Delimited foreign ID's in a mysql field

2 Answers2