DB::update laravel 5 raw query

Question

I intend to use laravel db update which equivalent to sql.

update users set username = "admin", status = "active" where user_id = 1

This is the query I test to run. Any wrong?

$username = "admin";
$status = "active";

DB::update('update users set username = '  .$username. ',
status = '.$status.'  where user_id = ?' ,['1']);

Solution 1

You should update your query like :

Eloquent Query:

User::where('user_id',1)->update(array(
                         'username'=>$username,
));

Fluent Query:

DB::table('users')->where('user_id',1)->update(array(
                                 'username'=>$username,
));

Hope this helps you

Solution 2

The correct query would be

DB::update('update users set username = ? , status = ? where user_id = ?', ["admin" , "active" , 1]);

OR

User::where('user_id', 1)->update( array('username'=>'admin', 'status'=>'active') );

Where "User" is the model name of "users" table.

Solution 3

The better way to do that with laravel query builder is:

DB::table('users')
    ->where('user_id', 1)
    ->update(['username' => $username, 'status' => $status]);

Solution 4

You are using raw query , it can be done as

update users set username = "admin", status = "active" where user_id = 1

DB::table('users')
->where('user_id',1)
->update(['username'=>'admin','status'=>'active']);

OR

$username = "admin";
$status = "active";

DB::update(DB::RAW('update users set username = '  .$username. ',
status = '.$status.'  where user_id = ?' ,['1']));

View more solutions

Comments

[Melroy van den Berg]

What will be the result of the first RAW DB::update method?

[Satish]

It returns number of rows changed; 0 if no row changed

[Thanura Jayasekara]

When it comes to multiple conditions in where clause it is modified as DB::table('table')->where([ ['column1','=',val1'],['column2','=','val2']])->update(['Upd‌​ateColunn'=>'updateV‌​alue']);