Why I am unable to perform this update using laravel query builder?

Question

I run the following code:

$crm_code = "Omaewamoshindeiru";
DB::update("UPDATE death_invitation set active = false where crm_code = '?' and active = true and contract_type_id IN (13, 14, 20, 21)",[[$code]]);

And I recieve the following error:

Illuminate/Database/QueryException with message 'PHP Notice:  Array to string conversion in /var/www/html/api/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 575 (SQL: UPDATE death_invitation set active = false where code = 'Omaewamoshindeiru' and active = true and contract_type_id IN (13, 14, 20, 21))'

As you can see despite the fact the query is generated correctly I fail to execute it. I also tried the following as well:

$code = "Omaewamoshindeiru";
DB::update("UPDATE death_invitation set active = false where code = '?' and active = true and contract_type_id IN (13, 14, 20, 21)",[$code]);

With response:

Illuminate/Database/QueryException with message 'SQLSTATE[08P01]: <<Unknown error>>: 7 ERROR:  bind message supplies 1 parameters, but prepared statement "pdo_stmt_00000005" requires 0 (SQL: UPDATE death_invitation set active = false where code = '2014-6-23-12-46-16' and active = true and contract_type_id IN (13, 14, 20, 21))'

Edit1:

I also tried the following:

$code = "Omaewamoshindeiru";
DB::update("UPDATE death_invitation set active = false where code = '?' and active = true and contract_type_id IN (13, 14, 20, 21)",$code);

And I got the error:

TypeError: Argument 1 passed to Illuminate/Database/Connection::prepareBindings() must be of the type array, string given, called in /var/www/html/api/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 481

Edit 2:

Also by running:

DB::update("UPDATE death_invitation set active = false where code = :crm_code and active = true and contract_type_id IN (13, 14, 20, 21)",[ 'crm_code' => $code ])

I get the following error:

Argument 1 passed to Illuminate\Database\Grammar::parameterize() must be of the type array, string given, called in /var/www/html/api/vendor/laravel/framework/src/Illuminate/Database/Query/Grammars/Grammar.php on line 853

Do you have any idea why? I am using laravel 5.7

Answer 1

See the laravel docs here: https://laravel.com/docs/8.x/database#running-an-update-statement

You don't need to quote your params within the statement. By using the prepared statement and param binding you can safely use any value without needing to escape anything. The query would look like this:

DB::update("UPDATE death_invitation set active = false where crm_code = ? and active = true and contract_type_id IN (13, 14, 20, 21)",[$code]);

The param is in an array because if you use multiple params (e.g. foo = ? and bar = ?) you can pass multiple values.

I also recommend using named bindings with multiple params, this makes it clear what goes where.