How to explode user id from a database column?

Question

I have a table name messages. There is a column name receiver_id I need to fetch record where receiver_id = 4 but i am always getting null output.

See my table Screenshot-

enter image description here

Above screenshot there are 3rows. second and third rows exist 4 along with other ids i need to explode that and show the second and third record.

How to write MySQL query?

Do not store data as comma separated string. For your issue checkout find_in_set function in mysql. — Abhik Chakraborty, Sep 22 '14 at 15:00
@AbhikChakraborty This find_in_set() not working in CakePHP i am getting `Error: Call to undefined function find_in_set()` how to fix that issue? — Developer, Sep 23 '14 at 06:00

score 7 · Accepted Answer · answered Sep 22 '14 at 15:00

7

Storing comma separated values is really a bad design you should normalize it first by storing all association of receiver in a junction table,If you are not able to alter your schema then for your current situation you can use find_in_set() to search values in a comma separated list

select * from table 
where find_in_set(4,receiver_id ) >0

answered Sep 22 '14 at 15:00

M Khalid Junaid

63,861
10
90
118

2

I would gladly upvote a second time for the sound advice *Storing comma separated values is really a bad design*. – VMai Sep 22 '14 at 15:03
In this case receiver 44 will return also positive for `find_in_set(4,receiver_id)` :( – LHristov Sep 22 '14 at 15:20
This find_in_set() not working in CakePHP i am getting `Error: Call to undefined function find_in_set() File: E:\xampp\htdocs\2014\profect\app\Controller\MessagesController.php Line: 24` how to fix that issue? – Developer Sep 23 '14 at 05:58
1

@learnphp php this new question related to php so better to ask new one with the tag for cake php also `find_in_set` is Mysql's function not php's – M Khalid Junaid Sep 23 '14 at 06:24

Linger · Answer 2 · 2014-09-22T15:13:49.420

2

This should also work (SQL Fiddle):

SELECT * 
FROM messages
WHERE receiver_id LIKE '%,4,%'
   OR receiver_id LIKE '4,%'
   OR receiver_id LIKE '%,4'
   OR receiver_id = '4'

Or with regex (SQL Fiddle):

SELECT * 
FROM messages
WHERE receiver_id REGEXP '4,|,4,|,4'
   OR receiver_id = '4'

edited Sep 22 '14 at 15:13

answered Sep 22 '14 at 15:01

Linger

14,942
23
52
79

youforgot the case `receiver_id=4` - if `4` is the only value in the field, then your query would fail. – Marc B Sep 22 '14 at 15:06
1

**@Marc B**, fixed the queries, but **M Khalid Junaid** answer is clearly better/simpler. – Linger Sep 22 '14 at 15:15

How to explode user id from a database column?

2 Answers2

Linked