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

@tykus So some users set data on table1, and i insert this data on table2 with this function. But Some data inserted with empty fileds for some reason. I dont know why. If i check same student_id on table 1 is filled and on table 2 is empty . But this hapend rarely. Maybe 10 empty record at day or less

users add data on table1 and on same time cronjob can be execuded. Maybe this is problem?

@usertxr

But Some data inserted with empty fileds for some reason

Do you know if the data was actually empty when the function was executed; perhaps the record in table1 was updated afterwards?

@tykus I dont know this. so I asked how I could do the function to indicate if the records have changed

@usertxr well, you can’t without querying for the record again whenever you want to perform the insert. You have fetched a (large?) Collection of user records into memory and anything could happen to the original database record while you’re working through the Collection.

Are you fetching the same records every time the job is executed; does the status somehow indicate that the record is already copied to the second table?

@tykus Yes i fetch a large of data. And yes every time the job is executed get the same data but before to insert data on table check if student_id exist on table2 and if exist dont insert same record multiple time.

@tykus i just found a way and verified that the record existed in the database before it was changed and i think job gets it before it is edited. So how do I get the job done when he has the fields filled?

@usertxr ok, of course. 🤦‍♂️

The problem is your data (i) in table2 can be stale because you don't update it after it is first inserted and (ii) the data in the Collection is potentially stale because the original record could change while you're processing.

To solve (i), you can upsert to update existing records; to solve (ii) if you perform an upsert, the data in table2 will eventually be correct; otherwise you need to re-fetch the record immediately before insert

@tykus I had something like this in mind as a logic when I asked the question. But can you help me how to make the query for this? How can i refetch record before set on table2

@usertxr you would make a query to mysql2.students table inside the loop and inside the :

$student = DB::connection('mysql2') ->table('students') ->where('student_id', $aStudent->student_id)->first();

Note that this is a very inefficient operation; you query mysql2.students for the Collection; then query mysql.students for existing User, then query mysql2.students. The Upsert approach would be more efficient; and eventually the data will be correct (since this task runs every X minutes anyway)

@tykus So you are saying to replace this query? Or to make upsert query inside this

 $student = DB::connection('mysql2') ->table('students') ->where('student_id', $aStudent->student_id)->first();

With upsert query? Its more efective? But i dont want dublicated record on mysql2.student table. If upsert can do this please can you do and example with my code? I have not use upsert before

@usertxr something like this:

$students = DB::connection('mysql2')->table('students')->where('status', '=', 'Y')->get();
foreach($students as $student){
    DB::connection('mysql')->table('student')->upsert([
      [
        'first_name' => $student->first_name,
        'last_name' => $student->last_name,
        'age' => $student->age,
        'student_id' => $student->student_id,
        'created_at' => $student->created_at
      ], [
        'student_id' => $student->student_id
      ], [
        'student_id'
      ], [
        'first_name', 'last_name', 'age', 'created_at'
      ]
]);
}
Log::info('Success, Data Updated');

@tykus This are filed what i want to check / refetch?

   [
    'student_id' => $student->student_id
  ], [
    'student_id'
  ], [
    'first_name', 'last_name', 'age', 'created_at'
  ]

@usertxr it's explained in the docs link that I shared.

The method's first argument consists of the values to insert or update, while the second argument lists the column(s) that uniquely identify records within the associated table. The method's third and final argument is an array of columns that should be updated if a matching record already exists in the database:

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

@usertxr be sure to read the disclaimer regarding unique/primary indexes

@tykus i get this errorr now

Call to undefined method Illuminate\Database\Query\Builder::upsert()

I dont use laravel 8

    "require": {
    "php": "^7.2",
    "doctrine/dbal": "^2.9",
    "fideloper/proxy": "^4.0",
    "haruncpi/laravel-log-reader": "^1.0",
    "haruncpi/laravel-user-activity": "^1.0",
    "laravel-admin-ext/log-viewer": "^1.0",
    "laravel/framework": "^6.0",
    "laravel/passport": "^7.4",
    "laravel/tinker": "^1.0",
    "maatwebsite/excel": "^3.1",
    "yajra/laravel-datatables-buttons": "^4.13",
    "yajra/laravel-datatables-oracle": "^9.18"
    },

@usertxr Then you cant use it. Upgrade to at least 8.10 or use another (slower) solution

@Sinnbeck Can you show me another solution for my version of laravel. I cant upgrade now on laravel 8, couse i need to change also php version and after this i need to upgrate and other things on my server

@usertxr @tykus already gave it to you above?

@Sinnbeck But i cant use upsert() with my laravel version, and i cant upgrade now on laravel 8.10

Also i use laravel 6 and php 7.2 how can i update directly to laravel 8 ?

@usertxr I know. But you were given another suggestion. https://laracasts.com/discuss/channels/laravel/how-to-check-if-record-changed-on-db-after-inserting-data?reply=764422

If you want to upgrade, then you need to upgrade php to at least 7.3 (I suggest going directly to 8.0 now that you are at it). And then upgrade to laravel 7.. Make sure everything works.. The upgrade to laravel 8 and make sure everything works. https://laravel.com/docs/7.x/upgrade

@Sinnbeck @tykus you would make a query to mysql2.students table inside the loop and inside the

$student = DB::connection('mysql2') ->table('students') ->where('student_id', $aStudent->student_id)->first();

Okay but how to make this query, any example with my case?

Any Idea?

@usertxr what I suggested will be very very inefficient; and especially inappropriate when you are solving for such an edge case.

Any chance that you able to perform a cross-database INSERT .. SELECT?

@tykus Any chance that you able to perform a cross-database INSERT .. SELECT?

Yes i run Insert when i indert data from table1 to table2

@usertxr I meant, can you write a query such that you are inserting directly from a select on the other database

https://dev.mysql.com/doc/refman/8.0/en/insert-select.html

@tykus i dont know this

@usertxr you should see if it is possible though... what you are doing should not require any PHP processing; it can all be handled on the database(s)

@tykus You think this will be helpfouly? or does not make sense?

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

  // Check if student_id dublicated
 $existing_data_in = DB::table('student')->where("student_id", $aStudent->student_id)->first();
if ( ! $existing_data_in) {

    $empty_record = DB::connection('mysql2') ->table('students') ->where('first_name', '!=', '') ->where('last_name', '!=', '') ->first();
    if ( $empty_record ) {

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

What i want to do with this... if on table1 have no empty record then do insert

@tykus not work like that. couse is more complicated than this. I have multiple table when i fetch data and insert on table2 where i need

@tykus @sinnbeck 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"]  

can this help in any way?

@Snapey In total are too many record until now are more than 2500 record. Function run fast, in second

@snapey I told youI alredy check if record does not exist on mysql2 and after this i insert new record form mysql1. On mysql1 sercords have student_id but other filed are empty until user update there. And i need to get after update . So how can i do this. Please do an example with my code if you can?

@Snapey what do you mean?

@Snapey I check if student_id exist on server where i want to copy to with this query

$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
    ]);
    }

Does the FROM server not have updated_at column? No doesnt have updated_at column

@usertxr ok. You are not listening.

@Snapey Maybe iam not understanding you, Sorry