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!

Are you saving the change log in database too? or just the Log file? — Salim Djerbouh, Sep 05 '19 at 13:54
Use Eloquent instead of the raw `DB` facade, and implement something like http://laravel-auditing.com/ or https://github.com/VentureCraft/revisionable. — ceejayoz, Sep 05 '19 at 13:55
There's no reason you can't use Eloquent here. Doing so allows packages like Laravel Auditing to **automatically** track changes to your models. If you don't want to use Eloquent, you'll have to implement this logging yourself. — ceejayoz, Sep 05 '19 at 13:57

matticustard · Answer 1 · 2019-09-05T14:51:25.957

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.

I updated the above code to make the model name dynamic by grabbing the class name directly from the model. This way, you can reuse the `saved()` method for additional models by creating a base Observer class to extend and avoid repeating yourself. — matticustard, Sep 05 '19 at 14:51

score 2 · Answer 2 · answered Sep 05 '19 at 14:28

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

you can see the answer of @matticustard ... he gave good examples and explanations. — shushu304, Sep 05 '19 at 14:29

score 0 · Answer 3 · answered Sep 05 '19 at 14:03

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);

Julius Fasema · Answer 4 · 2019-09-05T14:32:46.477

//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));

       }
      }
  }

laravel DB update get changes column

4 Answers4