Solve the problem that IN query in Yii framework parameterized query can only query one

How to solve the problem that only one IN query can be queried in the Yii framework parameterized query? This article mainly introduces the solution to the problem that only one IN query can be queried in the Yii framework parameterized query, and analyzes the IN query in the Yii framework in the form of examples. The reason why you can only check one function and the related functions and usage skills of the FIND_IN_SET function can be referenced by friends who need it. I hope to be helpful.

The details are as follows:

When using parameterization for IN query in the yii framework, the results were 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 checked the relevant source code in the yii framework and found that the It was a pdo query, so I queried pdo related information and found out the reason: cannot let placeholders replace a set of values ​​.

SELECT id FROM tb WHERE userid IN ( ? );

Now that you know the reason, then 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 composed of N sub In the string list strlist composed of chains, the return value ranges from 1 to 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.

Related recommendations:

Detailed explanation of restful api authorization verification of yii2

Detailed explanation of how Yii2 implements a custom independent validator

##Yii2 integrates Xunsou to achieve efficient Chinese word segmentation retrieval

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