$query = $db->createCommand("SELECT username FROM user WHERE id IN :ids")->bindValue([':ids' => "($ids)"])->queryAll();
Produces this error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’ ‘(1, 3, 5)’ ’ at line 5
Parameter binding is implemented via prepared statements. Besides preventing SQL injection attacks, it may also improve performance by preparing a SQL statement once and executing it multiple times with different parameters. For example,
$command = $db->createCommand(‘SELECT * FROM post WHERE id=:id’);
Because bindParam() supports binding parameters by references, the above code can also be written like the following:
$command = $db->createCommand(‘SELECT * FROM post WHERE id=:id’)
->bindParam(':id', $id);
$id = 1;
$post1 = $command->queryOne();
$id = 2;
$post2 = $command->queryOne();
Notice that you bind the placeholder to the $id variable before the execution, and then change the value of that variable before each subsequent execution (this is often done with loops). Executing queries in this manner can be vastly more efficient than running a new query for every different parameter value.
for variable number of variables you have to build own query
with PDO workaround (index padding). better if you can provide array with start index as ‘1’
$ids = [1,2,3,4,5];
// change index 0 to 1. PDO starts from 1
$ids = array_pad($ids, -count($ids)-1, null);
unset($ids[0]); // we got required [1=>1,2,3,4,5];
$in = implode(',', array_fill(0, count($ids), '?'));
$c = \Yii::$app->db->createCommand("SELECT username FROM user WHERE id IN ($in)")->bindValues($ids);
echo $c->rawSql;
die;
just looping. looks better for regular array with start index ‘0’
$ids = [1,2,3,4,5];
$in = implode(',', array_fill(0, count($ids), '?'));
$c = \Yii::$app->db->createCommand("SELECT username FROM user WHERE id IN ($in)");
foreach ($ids as $k=>$id) {
$c->bindValue($k+1,$id);
}
echo $c->rawSql;
die;