CODEWITHSUNDEEP
mysqlwhere-clause
Jesica Arroyo Salvador
Jesica Arroyo Salvador

Reputation: 143

Is it possible to exactly match the value in "WHERE IN" query?

I have a table with this estructure:

ID | level1 | level2 | level3

Every field store an interger. Then I have an array like this: $array (1,2,8,9,15)

I want to get all rows that contain a value in the array in any of the fields level1, level2 or level3.

I try this:

SELECT P.* FROM table P 
WHERE level1 OR level2 OR level3 IN (".$array.")

The problem is that if the array contains for example the number 1, query get rows with 10, 11, 12...

Is there any way to make the value match exactly?

Upvotes: 0

Views: 96

Answers (3)

Mike Brant
Mike Brant

Reputation: 71422

The IN selector requires and exact match of the one of the values in the array. You have a few issue here. First, you can't use OR like you are using no need specifiy the IN for every field like this:

SELECT P.* FROM table P 
WHERE
level1 IN (...)
OR level2 IN (...)
OR level3 IN (...)

Second, you can't just echo your array out like like try something like this:

$in_string = "'" . implode("','", $array) . "'";
$query = "SELECT P.* FROM table P 
    WHERE
    level1 IN ($in_string)
    OR level2 IN ($in_string)
    OR level3 IN ($in_string)";

Upvotes: 1

Ike Walker
Ike Walker

Reputation: 65587

The problem is not what you think it is. As presently written, your query will return any row where level1 != 0 OR level2 != 0 OR level3 is in your list, because level1 and level2 are being evaluate as booleans.

You need to have 3 separate IN clauses, like this:

SELECT P.* 
FROM table P 
WHERE level1 IN (...) 
OR level2 IN (...) 
OR level3 IN (...)

Upvotes: 3

fthiella
fthiella

Reputation: 49089

You could use FIND_IN_SET:

SELECT P.* FROM P 
WHERE
  FIND_IN_SET(level1, '".$array."') OR
  FIND_IN_SET(level2, '".$array."') OR
  FIND_IN_SET(level3, '".$array."')

Please see fiddle here.

Upvotes: 0

Related Questions