Julez Julez - 1 month ago 12
SQL Question

SQL IN operator with alias selector not working only return first match

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

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