How to find id in MySQL column array

Question

Company table structure:

(`id`=`1`, `name`=`CompanyName`, `members`=`3,52,134,21`)

The numbers in the members column are the user id's added to this company.

When user with id = 3 OR 52 OR 134 OR 21 is logged in the row above should be returned with a MySQL query.

Is this possible with a query or do I have to edit my table structure?

Answer 1

Try:

SELECT * FROM tbl_name WHERE FIND_IN_SET('3',members);

Note: Never store data in comma separated format. If it is not late yet, then make one more table and normalize the structure. By normalizing the structure, it will be easy and fast searching by giving indexing to the column.

table name: company_users

pk_id    member_id   company_id(foriegn_key of company_table)
1          3           1
2          52          1
3          21          1

Answer 2

Use FIND_IN_SET() function:

Try this:

SELECT u.*
FROM Company c
INNER JOIN company_users u ON FIND_IN_SET(u.id, c.members)
WHERE c.id = 1;

Answer 3

Use FIND_IN_SET() method like this..

SELECT * FROM table_name WHERE  FIND_IN_SET('your_search','table_column_name');

For example :

SELECT * FROM table_name WHERE FIND_IN_SET('52',members);