1

Ok so, I'm still a beginner in databases. I have this code:

$sql="
  INSERT INTO complaints_members
    (password, complaint) 
  VALUES 
    ('$mypassword','$submit') 
  ON DUPLICATE KEY UPDATE 
    complaint='$submit' ; 
";

This simply updates my complaint in the existing entry. How can I insert a new entry with the same key, instead of updating the old one?

I'm not very familiar with the terms of SQL, so I'm sorry in advance.

  • Edit the question and add the output of SHOW CREATE TABLE complaints_members; – ypercubeᵀᴹ Feb 6 '15 at 18:35
2

Your SQL statement specifies the behavior you are seeing with ON DUPLICATE KEY UPDATE. This syntax is shorthand for logic that causes an insert only if the key does not exist, and an update of the row identified by that key if it does exist. See the MySQL documentation for more info.

Since ON DUPLICATE KEY UPDATE only operates on primary keys or unique indexes, you cannot insert another row with the same key anyway. If multiple records with the same identifier are required in the table, you must make that identifier something other than the primary key or remove the unique constraint on the index.

  • So should i use a new key to make primary ? – Dominus_Sinus Feb 6 '15 at 18:58
  • Correct. Just a guess, but going by your complaints_members table, it looks like password is currently the primary key, which would allow only one row per member (it would also disallow multiple members using the same password). The table should probably have its own incrementing primary key, just an integer to identify each record. Also, if there is a members table to which the password is related, you should consider using a column other than password from that table (is there a member id or number?) as the foreign key in complaints_members. – dartonw Feb 6 '15 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.