Level 73
Still because the value from the source table is empty.
1 like
Jan 19, 2022
11
Level 4
Why INSERT sometimes insert empty record instead of filling them out?
I have a table in a db where users add data constantly, now from this table I take some columns and do INSERT in a second db ('mysql2') :: connection, I use student_id to not allow it to have a dublicated record ne db2. But my problem is that sometimes INSERT puts empty data in db2, I do not know why this happens but in the table of db1 the data is filled in while in db2 sometimes the data passes "" (empty).
Use a cronjob to execute every minute as the data is updated every moment
$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) {
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');
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"]
Iam using laravel 6.X version
Level 4
@Tray2 No value from the source is not empty. If I check the same record in the table where I get the data is not empty
If i check for studnet_id = '421' in table sondazhes i see last name filled if i check for student_id = '421' in table sondazhe where i insert data with job every 1 minut, last_name is empty
Level 55
@usertxr for sure it's because the value from source table is empty.
Level 4
If i check for studnet_id = '421' in table sondazhes i see last name filled
if i check for student_id = '421' in table sondazhe where i insert data with job every 1 minut, last_name is empty
this is poblem couse is not empty on source table. If it were empty in the source table where I get the data I would not have opened a discussion here
Level 39
Are you sure you did not get an error during the insert ?
- check your logs
and / or
- use try to catch error on insert
Level 4
@sr57 I save my query logs on a text file and i check for error. But looks is working correctly
Level 39
@usertxr catch the error to be sure and log in in a text file $aStudent->last_name, to be sure it's not empty
Level 4
@sr57 I cant do this couse every time is different filed empty. Its not a specific filet. Next time maybe will be age =" "
This is what i have get from query
[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"]
Level 39
@usertxr You can and MUST catch the error first, without you'll never be sure of your code.
That said, why do use limit 1, have you several record with the same student_id, if so are you sure all of them contains all the data in the fields?
Have a look of the result without limit.
Level 4
@sr57 I use limit 1 for query to check row by row if an student_id exist do not sublicate on insertng table.
And my query dont show any error
Level 39
@usertxr Please reread my previous answer
-
1 - share your code with "try"
-
2 - share your result without limit for table students and student_id=421
Please or to participate in this conversation.