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

Question

Ok, normally I know you would do something like this is you knew the array vals:

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

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

SELECT * WHERE 3 IN (ids) // Where ids is stored as 1,2,3

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

Thanks.

PHP + MySQL

Answer 1

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 (' . array_map('intval', implode(',', $ids)) . ')';

Answer 2

Use the FIND_IN_SET function:

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