I’m trying to run a query like
DELETE FROM mvp_board_player_owned WHERE player_id IN (1,2,3,4,5,6) AND mvp_board_scoring_id = 1
I’ve followed the instructions here: http://www.yiiframework.com/doc-2.0/guide-db-dao.html#executing-non-select-queries
Which lead me here: http://www.yiiframework.com/doc-2.0/yii-db-command.html#delete()-detail
And I’ve also read there where documentation here: http://www.yiiframework.com/doc-2.0/yii-db-query.html#where()-detail
What I’ve understood I should do is the following:
$delete_command = Yii::$app->db->createCommand()
->delete(
'mvp_board_player_owned',
[
'player_id' => ':ids_to_remove_from_owned',
'mvp_board_scoring_id' => ':mvp_board_scoring_id',
],
[
':ids_to_remove_from_owned' => $ids_to_remove_from_owned,
':mvp_board_scoring_id' => $mvp_board_scoring_id
]
)->execute();
But when I do that I get the error:
SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
The SQL being executed was: DELETE FROM `mvp_board_player_owned` WHERE (`player_id`=':ids_to_remove_from_owned') AND (`mvp_board_scoring_id`=':mvp_board_scoring_id')
I inspect the command object using print_r and find there are 4 parameters attempting to be bound:
...
[_pendingParams:yii\db\Command:private] => Array
(
[:ids_to_remove_from_owned] => Array
(
[0] => 2460
[1] => 233
[2] => 6663
[3] => 3450
[4] => 1825
[5] => 2102
[6] => 2739
)
[:mvp_board_scoring_id] => Array
(
[0] => 63020
[1] => 2
)
[:qp2] => Array
(
[0] => :ids_to_remove_from_owned
[1] => 2
)
[:qp3] => Array
(
[0] => :mvp_board_scoring_id
[1] => 2
)
)
...
Here’s the result of a var_dump of $mvp_board_scoring_id and $ids_to_be_removed_from_owned:
array(7) {
[0]=>
string(4) "2460"
[1]=>
string(3) "233"
[2]=>
string(4) "6663"
[3]=>
string(4) "3450"
[4]=>
string(4) "1825"
[5]=>
string(4) "2102"
[6]=>
string(4) "2739"
}
string(5) "63020"
I’ve tried several variations of how to create my condition statements based on the where documentation I listed above and nothing has worked. I won’t post them all here becauase this will get too long.
What am I doing wrong? Do I not need to use parameters with array values? I’d like to do this properly and making sure that I dont just insert values into the array without them going through the proper security measures. Am I not using the $params argument correctly? I feel like I’m trying to do something really easy I’m not sure why everything I try is producing such a terrible results. Please help!