具有别名选择器的SQL IN运算符不起作用仅返回第一个匹配

Question

Im trying to return a set of data with php where a specific id matches in a set of ids in the database.

i have to following code:

$id = 2;

$stmGetScoutUnits = $db->prepare('SELECT * FROM scout_units as su WHERE '.$id.' IN (su.greenhouse_ids) AND user_id = 1');

$stmGetScoutUnits->execute();

$scoutUnits = $stmGetScoutUnits->fetchAll(PDO::FETCH_OBJ);

var_dump($scoutUnits);

database looks a follow:

scout_units
+---------+---------------+-------+
| user_id | greenhouse_ids| name  |
+---------+---------------+-------+
|    1    | 1,2           | test  |
|    1    | 1,2           | test2 |
|    1    | 3,4           | test3 |
+---------+---------------+-------+

When i have id 1 it returns sql rows 1 and 2 but when i have id 2 it returns nothing. i have no id whats going on here? any idea?

Answer 1

What you need here is MySQL's FIND_IN_SET() function, so your query should be like this:

SELECT * 
FROM scout_units as su 
WHERE FIND_IN_SET('.$id.', su.greenhouse_ids) 
AND user_id = 1

And your prepared statement should be like this:

$stmGetScoutUnits = $db->prepare('SELECT * FROM scout_units as su WHERE FIND_IN_SET('.$id.', su.greenhouse_ids) AND user_id = 1');