ON DUPLICATE UPDATE does not work with some string

Question

I am using ON DUPLICATE UPDATE for my query, some of the results did not save it. I tried everything possible, but they still remain the same. here is a database image.

These NULLs are a string that has not been successfully saved; the result should be 1 instead of NULL.

if($remark){ $query3 = "INSERT INTO `audit_section_remarkrecord` SET remark = '$remark', form_details_subquestion_id = '$form_details_subquestion_id', form_details_section_id = '$form_details_section_id', audit_section_no = '$audit_no' ON DUPLICATE KEY UPDATE form_details_section_id = '$form_details_section_id' , remark = '$remark'"; $result3 = $db->query($query3); $query4 = "UPDATE `remarkrecord_update_details` SET form_details_section_id = '$form_details_section_id', userlog = '$user_staff', ipaddress = '$ip' WHERE form_details_subquestion_id = '$form_details_subquestion_id' AND audit_section_no = '$audit_no' "; $result4 = $db->query($query4); }else{ } } 

Table structure

Answer 1

Try changing one of the columns in which you insert the value into the primary key, or add a UNIQUE constraint to the column.

 ALTER TABLE audit_section_remarkrecord ADD UNIQUE KEY (your column); 

Since currently there is only a unique key in the main key, auto-increment, it will be difficult for you to create a unique key collision on insert on duplicate key update (IODKU). Therefore, it is necessary to create another unique key (even a composite one) that will cause a unique key collision and allow IODKU to work as expected.