MYSQL WHERE IN explode array

Question

I have a users table with fields

(id, name, email, companies)

and data as:

(1, 'Dave', 'dave@test.com', '1,3')
(2, 'Jake', 'jake@test.com', '2,3')
(3, 'Peter', 'peter@test.com', '1,2')

Each user can access the companies in their profile. The problem I have is to select the relevant users for a specific company.

For example, I want the users fro company 2, so the result should be 'Jake' and 'Peter'. Getting the result in PHP is pretty easy, just do a normal query to the user's table and explode the companies and check if the company exists. Is there a way to do it in MYSQL?

I've tried SELECT * FROM USERS WHERE companies IN (???) but I can't get my head around how to explode the companies in MYSQL.

Suggestion. Normalise your schema. Normally I (personally) would expect company to be it's own table, and if a user can be in multiple companies, then have a link table to join them up. — Jonnix, Aug 08 '19 at 10:29
either you have to store separate company id in `number` field or use => https://stackoverflow.com/a/2674209/6309457 — Devsi Odedra, Aug 08 '19 at 10:30
Don't store data like this. You'll be able to get away this time but this will make things a lot harder. Normalize your database. — DxTx, Aug 08 '19 at 10:41

score 3 · Answer 1 · answered Aug 08 '19 at 10:33

3

Assuming that you want the users of company 2.

SELECT * FROM USERS WHERE find_in_set('2', companies) will work for you.

answered Aug 08 '19 at 10:33

monishvadhwani

71
2

Thank you very much it worked exactly the way I needed it to work. – Stuart Harrison Aug 08 '19 at 10:53

MYSQL WHERE IN explode array

1 Answers1