Mysql search for a string in an Array

Question

Data stored in the database as below where category is column name and comma separated strings are values.

category = 'cat,cat1,cat2,cat3,cat33,cat4';

I'm trying to search with a category from the list:

$query= mysqli_query($mysqli, "SELECT * FROM table where
INSTR(category,'cat3') > 1 ");

But this doesn't work because the result includes cat3 and cat33.

Any thoughts on how I can do this search, please?

Thanks!

"Data stored in the database as below where category is column name and comma separated strings are values"—this is a sure sign that you've got a data modeling problem. Multiple values shouldn't be crammed into a single field in your database. — Chris
– Chris, Commented Jul 23, 2018 at 22:04
Someone may suggest using FIND_IN_SET, but the best answer is normalization. — Don't Panic
– Don't Panic, Commented Jul 23, 2018 at 22:08
If you don't yet have the select working for this, I'm gonna presume that means it's not live, otherwise it wouldn't be doing anything. Honestly, extending the comments above, you should take this time while it's not live to add a new table called (eg) categories and have each category as a separate row with data in other columns relevant to each cat — James
– James, Commented Jul 23, 2018 at 22:25

Tantowi Mustofa · Accepted Answer · 2018-07-23 22:19:46Z

3

You can use FIND_IN_SET function :

SELECT * FROM table where FIND_IN_SET('cat3', category) > 0

But you should normalize your data by creating category table and relate to your table's primary key

answered Jul 23, 2018 at 22:19

Tantowi Mustofa

6974 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nikolai · Accepted Answer · 2018-07-23 22:20:57Z

0

"SELECT * FROM table where category LIKE '%cat3,%'";
"SELECT * FROM table where category LIKE '%cat33,%'";

if you don't want to add comma to the end of each category name, then "SELECT * FROM table where category LIKE '%cat3,%' OR category LIKE '%cat3'";

edited Jul 23, 2018 at 22:20

answered Jul 23, 2018 at 22:18

Nikolai

3051 gold badge2 silver badges13 bronze badges

3 Comments

Don't Panic Over a year ago

This will miss the last value in the list.

Nikolai Over a year ago

"SELECT * FROM table where category LIKE '%cat3,%' OR category LIKE '%cat3'";

Mike Over a year ago

This will also match a category called "somecat3". If you're going to do it the wrong way and not normalize your tables, you could instead use a regular expression: SELECT * FROM table where category REGEXP '(^|,)cat3(,|$)'.

Mateus Alberghini · Accepted Answer · 2018-07-23 23:00:18Z

0

You can "solve" this using the query below. BUT I HARDLY RECCOMEND you to normalize your database and use this only if you REALLY NEED

SELECT * FROM table WHERE fild LIKE '%,camp3,%' OR fild LIKE 'camp3' OR field LIKE '%,camp3'

answered Jul 23, 2018 at 23:00

Mateus Alberghini

771 silver badge7 bronze badges

Collectives™ on Stack Overflow

Mysql search for a string in an Array

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related