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

问题描述：

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?

我试图用php返回一组数据，其中特定的id匹配数据库中的一组id。 p>

我必须遵循以下代码： p>

  $ id = 2; 
 
 $ stmGetScoutUnits = $ db-＆gt; prepare（  'SELECT * FROM scout_units as su WHERE'。$ id。'IN（su.greenhouse_ids）AND user_id = 1'）; 
 
 $ stmGetScoutUnits-＆gt; execute（）; 
 
 $ scoutUnits = $ stmGetScoutUnits-  ＆gt; fetchAll（PDO :: FETCH_OBJ）; 
 
var_dump（$ scoutUnits）; 
  code>  pre> 
 
 数据库看起来如下： p> 
 
   scout_units \ N + --------- + --------------- + ------- + \ N |  user_id |  greenhouse_ids |  name | 
 + --------- + --------------- + ------- + 
 |  1 |  1,2 | 测试| 
 |  1 |  1,2 |  test2 | 
 |  1 |  3,4 |  test3 | 
 + --------- + --------------- + ------- + 
  code>  pre> 
  
 
当我有id 1它返回sql行1和2但是当我有id 2时它什么都不返回。 我没有在这里发生什么事吗？ 任何想法？ p> 
  div>

答

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

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

相关推荐