0

Got an error- 'Duplicate entry '9' for key 'accountID' ERROR 1062.

I'm trying to use ON DUPLICATE KEY UPDATE in order to update any fields that are already inserted into the database.

Background about program:

I have three tables. Account, address, and profile. Because of foreign key constraints on person and on address, I cannot use a simple update query, therefore I'm using this. I'm not sure if I'm using it incorrectly, or that I don't understand the logic of it. If anyone can give a hand that would be great. I believe the problem lies in the method of inserting the form data. I've added a description below.

In my function, I update two tables (address and person) in the same form. Because person refers to an addressID, address data needs to be inserted before the person data can be inserted. According to my code, the issue lies with the person data insert (w/ on duplicate ...), although the address insert (on duplicate...) works, it doesn't update a row, it just adds a new row - which is wrong still. I'm trying to understand the issue with the second insert statement below.

Note: assume that all of the array fields ($addressData, $profileData) are correct, they work without the ON DUPLICATE KEY UPDATE function.

Thanks!

$this->db->insert('address', $addressData).'ON DUPLICATE KEY UPDATE country=country+1, city=city+1, streetName=streetName+1, postcode=postcode+1, buldingNumber= buldingNumber+1';

$this->db->insert('person', $profileData).'ON DUPLICATE KEY UPDATE firstname=firstname+1, lastname=lastname+1,addressID='.$addressID.'+1, dob=dob+1, religion=religion+1, locationFlexibility=locationFlexibility+1';
Chris
  • 785
  • 10
  • 24
  • accountID is auto increment? – Rafael Shkembi Nov 24 '16 at 15:19
  • @RafaelShkembi Yup, and is the primary key of the account table. – Chris Nov 24 '16 at 15:20
  • in our case "9" is the accountID right? – Rafael Shkembi Nov 24 '16 at 15:21
  • @RafaelShkembi Correct – Chris Nov 24 '16 at 15:22
  • @RafaelShkembi is it possible that I need to add 'where accountID = accountID"? or something of the sort? – Chris Nov 24 '16 at 15:24
  • try this-> $sql = $this->db->insert_string('address', $addressData) . ' ON DUPLICATE KEY UPDATEcountry=country+1, city=city+1, streetName=streetName+1, postcode=postcode+1, buldingNumber= buldingNumber+1'; $this->db->query($sql); – Rafael Shkembi Nov 24 '16 at 15:27
  • I'll try to explain how my code works. I update two tables (address and person) in the same form. Because person refers to an addressID, address data needs to be inserted before the person data can be inserted. According to my code, the issue lies with the person data insert (w/ on duplicate ...), although the address insert (on duplicate...) works, it doesn't update a row, it just adds a new row - which is wrong still. The code you shown above doesn't give any errors, but that doesn't solve the error for the duplicate accountID error. Appreciating the help however! – Chris Nov 24 '16 at 15:34
  • I belive the error is from db->insert (). Maybe if you change it to db->query you will solve the error – Rafael Shkembi Nov 24 '16 at 15:37
  • Okay I'll give it another try. Thanks! – Chris Nov 24 '16 at 15:38

0 Answers0