laravel DB update get changes column

Question

i want to save log of changes when i update something on the database. there is elegant way to get the column that will be updated (just if there is change). i want to save the old column value in log..

for example:

$updateUser = DB::table('users')->where('id','1')->update(array('email' => 'new@email.com', 'name' => 'my new name'));

from this i want to get back the old email was in database (if changed) and the old name (again, only if changed)

thanks!

Answer 1

As others have mentioned, Eloquent is a great way to go if using Laravel. Then you can tap directly into Laravel's events using Observers. I have used a method very similar to what is below. Of course, you would need to set up Models for User and AuditLog.

See more info regarding Observers.
https://laravel.com/docs/5.8/eloquent#observers

In Controller Method

$user = User::find(1);
$user->update([
    'email' => 'new@email.com',
    'name' => 'my new name'
]);

App/Providers/EventServiceProvider.php

class EventServiceProvider extends ServiceProvider
{
    // ...

    public function boot()
    {
        User::observe(UserObserver::class);
    }
}

App/Observers/UserObserver.php

class UserObserver
{
    /**
     * The attributes to exclude from logging.
     *
     * @var array
     */
    protected $except = [
        'created_at',
        'updated_at'
    ];

    /**
     * The attributes to mask.
     *
     * @var array
     */
    protected $masked = [
        'password',
    ];

    /**
     * Listen for model saved event.
     *
     * @var array
     */
    public function saved($model)
    {
        // search for changes
        foreach ($model->getChanges() as $key => $new_value) {

            // get original value
            $old_value = $model->getOriginal($key);

            // skip type NULL with empty fields
            if ($old_value === '' && $new_value === null) {
                continue;
            }

            // attribute not excluded and values are different
            if (!in_array($key, $this->except) && $new_value !== $old_value) {

                // mask designated fields
                if (in_array($key, $this->masked)) {
                    $old_value = '********';
                    $new_value = '********';
                }

                // create audit log
                AuditLog::create([
                    'user_id' => auth()->user()->id,
                    'model_id' => $model->id,
                    'model' => (new \ReflectionClass($model))->getShortName(),
                    'action' => 'update',
                    'environment' => config('app.env'),                   
                    'attribute' => $key,
                    'old_value' => $old_value,
                    'new_value' => $new_value,
                ]);
            }
        }
    }
}

I hope this helps!

EDIT: See comment regarding update.

Answer 2

//try this. hpe it helps out:

    function Update(Request $request, $id)
    {

      $dbrecord = DB::table('users')->where('id',$id)->first();

      $oldemail = $dbrecord->email;
      $oldname =  $dbrecord->name;

      if(($oldemail==$request->input('email'))&&($oldname==$request->input('name')))
     {
      //do nothing
     }
    elseif(($oldemail!=$request->input('email'))or($oldname!=$request->input('name')))
     {

      $updateUser = DB::table('users')->where('id',$id)->update(array('email' => $request->input('email'), 'name' => $request->input('name')));
      if($updateUser)
       {
           DB::table('log')->where('id',$id)->insert(array('email' => $oldemail, 'name' => $oldname));

       }
      }
  }

Answer 3

I will suggest 2 options:

1) to use the Eloquent model on every changes,

and then to use the existing methods like :

model->isDirty()

model->getChanges()

you can implement it on the model life cycle of updating / updated events listeners

more information and example you can see here:

https://laravel.com/docs/5.8/events

https://medium.com/@JinoAntony/10-hidden-laravel-eloquent-features-you-may-not-know-efc8ccc58d9e

https://laravel.com/api/5.3/Illuminate/Database/Eloquent/Model.html

2) if you want to log changes even if you are running regular queries and not only via model life cycle,

you can use MySql Triggers on every table updates and then to check OLD vs NEW and insert directly to the log changes db

more information you can find here:

https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

MySQL Trigger after update only if row has changed

Answer 4

Why not just something like this:

$changeArr = ['email' => 'new@email.com', 'name' => 'my new name'];
$id = 1;
$table = 'users';

foreach($changeArr as $key => $value){
    DB::table('updateTable')->insert(['table' => $table, 'id' => $id, 'col' => $key, 'oldVal' => $value]);
}

$updateItem = DB::table($table)->where('id', $id)->update($changeArr);