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

Question

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?

Answer 1

Use the FIND_IN_SET function:

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

Answer 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)) . ')';