MySQL Select: Find if a single value exists in an array field

Question

I've a field "locations" containing values in format 1,4,7,8 etc.

I want to exclude rows that contain a specific value. eg: $location = 4;

I tried: SELECT * FROM users WHERE FIND_IN_SET($location, locations) = 0

Also tried some combinations with IN .. !IN but again no results. Any suggestion?

Thank you

SELECT * FROM users WHERE location<>'$location' ? You want to exclude data based from a submitted form, right? — Logan Wayne
– Logan Wayne, Commented Oct 16, 2014 at 5:57

Community · Accepted Answer · 2017-05-23 12:20:28Z

0

As pointed by John Woo your query must work. If not then may be data in the locations contains values with spaces before commas, for example: 1,4 ,7,8. In this case FIND_IN_SET isn't working as expected. So you can try:

SELECT * FROM users WHERE NOT(locations REGEXP '[[:<:]]$location[[:>:]]')

[[:<:]], [[:>:]] stand for word boundaries, so [[:<:]]4[[:>:]] will match 4 in list, but not 14.

Test and see difference on SQL Fiddle

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Oct 16, 2014 at 7:09

Rimas

6,0332 gold badges28 silver badges40 bronze badges

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

5 Comments

Christos Teriakis Over a year ago

1.- Confirmed that there is no space between locations. 2.- Your example bring parse error: syntax error, unexpected '[', expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING)

Christos Teriakis Over a year ago

I replace the variable $location with the actual value and it works, but works on the opposite way. Maybe if I remove NOT?

Rimas Over a year ago

Did you enclose this query with double quotes " in php script?

Christos Teriakis Over a year ago

This is my query right now: "SELECT * FROM users WHERE NOT(locations REGEXP '[[:<:]]".$location."[[:>:]]') AND active=1 AND hidden=0" As I wrote before works, but opposite way. In cases that normal should not shows rows, it shows them, and in cases that it must shows data, it does not shows.

Christos Teriakis Over a year ago

Ok. I removed NOT from query and now works as expecetd. Maybe I was not clear by saying that "EXCLUDE".

Gavin Simpson · Accepted Answer · 2014-10-16 05:58:57Z

0

SELECT * FROM users WHERE FIND_IN_SET($location, locations) != 0

answered Oct 16, 2014 at 5:58

Gavin Simpson

2,8623 gold badges32 silver badges41 bronze badges

3 Comments

Gavin Simpson Over a year ago

I would think it should be $location='4' as FIND_IN_SET is a string function. Hard to tell without seeing the field types. dev.mysql.com/doc/refman/5.0/en/… refers.

Christos Teriakis Over a year ago

Field locations is a VARCHAR field type, so it's ok. I had a typo before. I want to EXCLUDE rows. I tried your suggestion but unfortunately does not works. Also tried !FIND_IN_SET($location, locations) > 0 . Nothing. What must be first parameter? The variable or the field name?

Gavin Simpson Over a year ago

Take a look at John Woo's answer above. Follow the link to his jfiddle. It is working, so that should help you?, so try "SELECT * FROM users WHERE FIND_IN_SET('$location', locations) = 0"

Collectives™ on Stack Overflow

MySQL Select: Find if a single value exists in an array field

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related