我一直得到无效的参数号：数字或绑定变量不是yii2中的数学标记数

Question

I am doing a simple update in yii2 controller inside of a for loop. When doing so I keep getting sql error: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

My code is below:

for($x = 0; $x < $existingProductCount; $x++)
        {
            $connection = Yii::$app->db;
            $params = [
                ':idcampaign_products' => intval(Yii::$app->request->post("idcampaign_product")[$x]),
                ':existing_products' => intval(Yii::$app->request->post("dynamicExistingProducts")[$x]),
                ':existing_billing_cycle' => Yii::$app->request->post("dynamicExistingBillingCycle")[$x],
                ':existing_trial_length' => Yii::$app->request->post("dynamicExistingTrialLength")[$x],
                ':existing_trial_price' => Yii::$app->request->post("dynamicExistingTrialPrice")[$x],
                ':existing_reg_price' => Yii::$app->request->post("dynamicExistingRegPrice")[$x]
            ];
            $connection->createCommand()
            ->update('campaign_products',
                [
                    'product_id'=>$params[':existing_products'],
                    'campaign_billing_cycle'=>$params[':existing_billing_cycle'],
                    'trial_length'=>$params[':existing_trial_length'],
                    'trial_price'=>$params[':existing_trial_price'],
                    'regular_price'=>$params[':existing_reg_price']
                ],
                'idcampaign_products=:idcampaign_products')
            ->bindValues($params)
            ->execute();
            //var_dump($params);

        }

Answer 1

You are not doing this properly. When you use query builder with an array you don't have to bound values - it's done automatically. With your code only :idcampaign_products needs to be bound and since you pass 6 values that is why the error pops.

Do it like that:

$connection->createCommand()
    ->update('campaign_products', [
        'product_id' => $params[':existing_products'],
        'campaign_billing_cycle' => $params[':existing_billing_cycle'],
        'trial_length' => $params[':existing_trial_length'],
        'trial_price' => $params[':existing_trial_price'],
        'regular_price' => $params[':existing_reg_price']
    ], ['idcampaign_products' => $params[':idcampaign_products']])
    ->execute();