MySQL - How to select rows where value is in array?

Ok, normally I know you would do something like this if you knew the array values (1,2,3 in this case):

SELECT * WHERE id IN (1,2,3)

But I don't know the array value, I just know the value I want to find is 'stored' in the array:

SELECT * WHERE 3 IN (ids) // Where 'ids' is an array of values 1,2,3

Which doesn't work. Is there another way to do this?

Solution 1:

Use the FIND_IN_SET function:

SELECT t.*
  FROM YOUR_TABLE t
 WHERE FIND_IN_SET(3, t.ids) > 0

Solution 2:

By the time the query gets to SQL you have to have already expanded the list. The easy way of doing this, if you're using IDs from some internal, trusted data source, where you can be 100% certain they're integers (e.g., if you selected them from your database earlier) is this:

$sql = 'SELECT * WHERE id IN (' . implode(',', $ids) . ')';

If your data are coming from the user, though, you'll need to ensure you're getting only integer values, perhaps most easily like so:

$sql = 'SELECT * WHERE id IN (' . implode(',', array_map('intval', $ids)) . ')';

Solution 3:

If the array element is not integer you can use something like below :

$skus  = array('LDRES10','LDRES12','LDRES11');   //sample data

if(!empty($skus)){     
    $sql = "SELECT * FROM `products` WHERE `prodCode` IN ('" . implode("','", $skus) . "') "      
}

Solution 4:

If you use the FIND_IN_SET function:

FIND_IN_SET(a, columnname) yields all the records that have "a" in them, alone or with others

AND

FIND_IN_SET(columnname, a) yields only the records that have "a" in them alone, NOT the ones with the others

So if record1 is (a,b,c) and record2 is (a)

FIND_IN_SET(columnname, a) yields only record2 whereas FIND_IN_SET(a, columnname) yields both records.