How to check if record was changed on db after inserting data?-CodePudding

I insert some data from a db.table1 to db.table2 with a cronjob which runs every minutes. But I need to check if any record has changed after data is inserted on table2 because I get some wrong data on this table when data is inserted.

this is crontab function

$data = DB::connection('mysql2')
    ->table('students')
    ->where('status', '=', 'Y')
    ->get();

foreach ($data as $key => $aStudent) {
    // Check if student_id duplicated
    $existing_data_in = DB::table('student')
        ->where("student_id", $aStudent->student_id)
        ->first();

    if (! $existing_data_in) {
        DB::connection('mysql')->table('student')->insert([
            "first_name"        =>$aStudent->first_name,
            "last_name"         =>$aStudent->last_name,
            "age"               =>$aStudent->age,
            "student_id"        =>$aStudent->student_id,
            "created_at"        =>$aStudent->created_at
        ]);
    }
}

Log::info('Success, Data Updated');

Or maybe should I run cronjob rarely? My problem is when some record on table1 are filed with data and when this record goes to table2 are empty on table2. If I check same student_id on table1 is okay but on table2 are saved empty

i have loged my query on cronjob and this is what i get in case i find an empty filed on table 2. So last name on table 1 in this case is filled with really last name but on table 2 is inserted as empty and this is query (what hapend)

[2022-01-19 14:16:17] local.INFO: select `student_id` `first_name`, `last_name`, `age`, `created_at` from `students` where `student_id` = ? limit 1 [421] 

// this comes from query where i check for dublicated lines i think
[2022-01-19 14:16:17] local.INFO: select * from `student` where `student_id` = ? limit 1 [421] 

[2022-01-19 14:16:17] local.INFO: insert into `student` 
(`student_id`, `first_name`, `last_name`, `age`, `created_at`) values 
(?, ?, ?, ?, ?) 
["421","name","","38","2022-01-19 14:13:35"]

CodePudding user response：

If the tables contain created_at and updated_at columns, you can tell if a record has been updated by comparing the two columns.

->whereRaw('created_at != updated_at')->get();

CodePudding user response：

You could wrap everything in a transaction to make sure it works as it should.

$data = DB::connection('mysql2')
    ->table('students')
    ->where('status', '=', 'Y')
    ->get();

$dataToInsert = $data->map(function ($item) {
    return [
        'first_name' => $item->first_name,
        'last_name'  => $item->last_name,
        'age'        => $item->age,
        'student_id' => $item->student_id,
        'created_at' => $item->created_at
    ];
})->all();

$columnsToCheck = ['student_id'];
$columnsToUpdate = [];

$conn = DB::connection('mysql');
$conn->beginTransaction();
try {
    $conn->table('student')->upsert($dataToInsert, $columnsToCheck, $columnsToUpdate);
    $conn->commit();
    Log::info('Success');
} catch (\Exception $e) {
    $conn->rollBack();
    Log::error($e);
}

I'm using upsert. You can read more about it here.

https://laravel.com/docs/8.x/queries#upserts