-1

I have attached my data and need result, I need to filter data from the array list. I have show "2" data result.

How can I get my output in mysql data?

enter image description here

Lawrence Cherone
  • 46,049
  • 7
  • 62
  • 106
AB Shaman
  • 21
  • 12
  • It will be easier to help you if you show us your code. Please [edit] your question to include what you've tried so far. – shoover Jul 13 '18 at 04:14
  • 1
    Possible duplicate of [Why find\_in\_set works but IN clause](https://stackoverflow.com/questions/15656740/why-find-in-set-works-but-in-clause) – mickmackusa Jul 13 '18 at 05:31

3 Answers3

1

May be this code helps you

Sample code : SELECT FIND_IN_SET('1', '1,2,3,4,5')

Your code may like : SELECT * from table WHERE FIND_IN_SET('2', array)>0

Praveen S
  • 395
  • 4
  • 18
-1

Sadly There is no native Array Datatype in mysql See this How to store arrays in MySQL?

The best bet you can do is to go for Postgres where it has a support for it

Or anyway, you can create a text datatype and save the array as json. As for the getting the data set. You need to do it manually on php. There's no way to do that in MYSQL alone

Or much better yet Normalize again your table into 3NF Separate the ARRY into separate table and bound it using the ID

keysl
  • 2,127
  • 1
  • 12
  • 16
-1

You can use LIKE to do it 

SELECT t.* FROM yourtable t
 WHERE t.attr LIKE CONCAT('2,%')
 OR t.attr LIKE CONCAT('%,2')
 OR t.attr LIKE CONCAT('%,2,%')

Test data

CREATE TABLE `system_users` (
  `id` varchar(32) NOT NULL,
  `name` varchar(32) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `system_users`(`id`,`name`) 
values 
('1','1,5,3,12'),
('2','1,2,3'),
('3','1,12,13'),
('4','1,5,2,10,12'),
('5','2,3,4'),
('6','5,4,3,2');

Working query:

SELECT * FROM system_users u 
  WHERE u.name LIKE CONCAT('2,%')
     OR u.name LIKE CONCAT('%,2')
     OR u.name LIKE CONCAT('%,2,%');

Output result:

+----+-------------+
| id | name        |
+----+-------------+
| 2  | 1,2,3       |
| 4  | 1,5,2,10,12 |
| 5  | 2,3,4       |
| 6  | 5,4,3,2     |
+----+-------------+

Also,as Praveen S mentioned,FIND_IN_SET() also works for you,it's more simple and elegant then LIKE

flyingfox
  • 13,414
  • 3
  • 24
  • 39