Laravel raw SQL update query
A really small mistake - I wanted to run a MySQL update statement on laravel (no bindings, no parameters, no models) so I wrote this
DB::update(`UPDATE table SET something = 1 WHERE id = 1`);
When I ran this, I got this error
ValueError: PDO::prepare(): Argument #1 ($query) cannot be empty
Was a bit surprised to see this. Upon reading the documentation, you're supposed to run the query this way, for queries not returning any values
DB::statement('UPDATE table SET something = 1 WHERE id = 1');
Also, I had used backticks `` for the original longer SQL statement so that seemed to have caused on issue.
Alternatively, I could do it without using eloquent models like:
DB::table('users')->where('id', '1')->update(['something' => 1]);
If you do that and want to get the raw SQL query, you can use the toSql
method:
DB::table('users')->where('id', '1')->toSql();
// gives a query like
select * from `users` where `id` = ?
There is a ?
in there in the query because the values are parameterized. You can use dump
method on it instead to get the query with bindings as well:
DB::table('users')->where('id', '1')->dump();
// result
select * from `users` where `id` = ? // vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:3788
array:1 [ // vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:3788
0 => "11111111"
]
If you try to print or dump a SQL statement on an update query, it will throw an error as query has already executed and it returns the number of rows that were affected by the update query.
DB::table('users')->where('id', '1')->update(['id' => 1])->toSql();
Error Call to a member function toSql() on int.
To get around this, you can use the query log. One downside is that this will execute the query and then get you the log.
\DB::enableQueryLog();
DB::table('users')->where('id', '1')->update(['id' => 1])->toSql();
dd(\DB::getQueryLog());