CODEWITHSUNDEEP
phpmysql
Mediatomcat
Mediatomcat

Reputation: 67

In Array not working as expected

I am running a query where you search an array value. I found a solution as to how to do this on Stackoverflow, however, it does not seem to give the output I would expect. Here are the examples, can you tell me where I am going wrong please:

I have a table with a column called departments and some of the examples rows contain values like: 

1: 2,4
2: 1,7,2,8,9,4
3: 4, 2

I run a query on this: 

SELECT * FROM udf_multi_value WHERE udfdefid =  '1' AND department IN ( 1, 7, 2, 8, 9, 4 );

This returns all rows in the results, which is what I would expect. However, when I run: 

 SELECT * FROM udf_multi_value WHERE udfdefid =  '1' AND department IN ( 2, 4 )

it only returns row 1 and 3. As 2 and 4 are in all rows I wuld ave thought it should return row 2 as well.

Any help greatly appreciated.

Upvotes: 1

Views: 82

Answers (1)

Marc B
Marc B

Reputation: 360772

Are those single fields with comma-separated values? If so, then you're getting expected behavior. You're passing in the integers 2 and 4, and comparing them against char/varchar fields, e.g.

if (2 == '2,4')
and
if (4 == '2,4')

MySQL will convert the varchar field to an int, meaning 2,4 becomes int 2 and 4,2 becomes int 4, so the matches succeed.

But 1,7,2,... will be cast to just 1, so there's no match.

You need to normalize your table so each of those numbers is in its own record in a sub-table, or use the MySQL non-standard SQL function find_in_set()

... AND (FIND_IN_SET('2', department) OR FIND_IN_SET('4', department))

Upvotes: 5

Related Questions