IN query in Yii query can only query one method

This article mainly introduces the solution to the problem that only one IN query can be queried in the Yii framework parameterized query. It analyzes the reason why only one IN query can be queried in the Yii framework and the related functions and usage skills of the FIND_IN_SET function in the form of examples. Friends in need can refer to

The example in this article describes the solution to the problem that only one IN query can be queried in the Yii framework parameterized query. Share it with everyone for your reference, the details are as follows:

When using parameterization for IN query in the yii framework, the result is not as expected

$sql =<<<SQL
SELECT id FROM tb WHERE id IN(:ids)
SQL;
$db = GeneralService::getSlaveDB();
$result = $db->createCommand($sql)->query([&#39;:ids&#39; => &#39;1013,1015,1017&#39;])->readAll();
print_r($result);

Array
(
  [0] => Array
    (
      [id] => 1013
    )
)

So I looked through the relevant source code in the yii framework and found that pdo query was used, so I queried the pdo related information and found out the reason: You cannot let placeholders replace a group value.

SELECT id FROM tb WHERE userid IN ( ? );

Now that you know the reason, find an alternative method. FIND_IN_SET can just meet the requirements

$sql =<<<SQL
SELECT id FROM tb WHERE FIND_IN_SET(id, :ids)
SQL;
$db = GeneralService::getSlaveDB();
$result = $db->createCommand($sql)->query([&#39;:ids&#39; => &#39;1013,1015,1017&#39;])->readAll();
print_r($result);

Array
(
  [0] => Array
    (
      [id] => 1013
    )
  [1] => Array
    (
      [id] => 1015
    )
  [2] => Array
    (
      [id] => 1017
    )
)

FIND_IN_SET function under simple popular science

FIND_IN_SET(str,strlist)

If the string str is in the string list strlist composed of N sub-chains, the return value is The range is between 1 and N.

A string list is a string consisting of subchains separated by ',' symbols. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit calculations.

If str is not in strlist or strlist is an empty string, the return value is 0. If any parameter is NULL, the return value is NULL. This function will not work properly if the first argument contains a comma (',').

[ps] The string consisting of commas in strlist cannot be recognized by adding a space to the right of the comma as usual.

The above is the detailed content of IN query in Yii query can only query one method. For more information, please follow other related articles on the PHP Chinese website!