CODEWITHSUNDEEP
mysqlsqlarraysyii2
ron
ron

Reputation: 175

Yii2 Update where the condition is an Array

This is the update code

 $clients = OpClient::find(['id'])->where(['status'=>'Active'])->all();

    foreach($clients as $client)
    {
        $array[] = $client['unit_id'];
        $unit = OpUnit::find()->where(['id'=>$array]);

         file_put_contents('test.txt',print_r($client['unit_id'],true));

         $connection = Yii::$app->db;
        $connection->createCommand()->update('op_unit', ['selected' => 'Yes'], 'id='.$array.'')->execute();
    }

How should I type in the update query where the id is an array? It keep showing error Array to string conversion. Any advice will be apprecieated. Thanks

Upvotes: 0

Views: 9211

Answers (3)

Yasin Patel
Yasin Patel

Reputation: 5731

You can use updateAll query :

$update = OpUnit::updateAll(['selected' => 'Yes'],['id' => $array]);

It returns number of rows updated.

Refer : http://www.yiiframework.com/doc-2.0/yii-db-activerecord.html#updateAll()-detail

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

should be this way .. 

$connection->createCommand()->update('user', 
             ['selected' => 'Yes'],['id' => $array])->execute();

try the real sql code using 

$myRawSql=   $connection->createCommand()->update('user', 
             ['selected' => 'Yes'],['id' => $array])>getRawSql();

var_dump($myRawSql);

Upvotes: 3

Chetan Sharma
Chetan Sharma

Reputation: 2557

For searching you can use the IN condition. i.e

->andWhere(['in', 'id', [1, 2, 3]])

// Query will be: WHERE id IN (1, 2, 3)

http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html

in: operand 1 should be a column or DB expression. Operand 2 can be either an array or a Query object. It will generate an IN condition. If Operand 2 is an array, it will represent the range of the values that the column or DB expression should be; If Operand 2 is a Query object, a sub-query will be generated and used as the range of the column or DB expression. For example, ['in', 'id', [1, 2, 3]] will generate id IN (1, 2, 3). The method will properly quote the column name and escape values in the range. The in operator also supports composite columns. In this case, operand 1 should be an array of the columns, while operand 2 should be an array of arrays or a Query object representing the range of the columns.

So basically you need to pass your array to IN for search.

For update you can use same Where syntax in updateAll command i.e

// UPDATE customer SET status = 1 WHERE id IN (1, 2, 3) http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#updating-multiple-rows

Customer::updateAll(['status' => Customer::STATUS_ACTIVE], ['in', 'id', [1, 2, 3]]);

Hope this helps. Thanks.

Upvotes: 2

Related Questions