Best way to avoid duplicate entry into mysql database

by

First step would be to set a unique key on the table:

ALTER TABLE thetable ADD UNIQUE INDEX(pageid, name);

Then you have to decide what you want to do when there’s a duplicate. Should you:

  1. ignore it?

    INSERT IGNORE INTO thetable (pageid, name) VALUES (1, "foo"), (1, "foo");

  2. Overwrite the previously entered record?

    INSERT INTO thetable (pageid, name, somefield)
VALUES (1, "foo", "first")
ON DUPLICATE KEY UPDATE (somefield = 'first')

INSERT INTO thetable (pageid, name, somefield)
VALUES (1, "foo", "second")
ON DUPLICATE KEY UPDATE (somefield = 'second')

  3. Update some counter?

    INSERT INTO thetable (pageid, name)
VALUES (1, "foo"), (1, "foo")
ON DUPLICATE KEY UPDATE (pagecount = pagecount + 1)

More Related Contents:

  1. Laravel – only a zero is saved to the database
  2. storing user_id in session variable
  3. I am seeing this error on submitting form Error: query was empty [closed]
  4. Reference: What is a perfect code sample using the MySQL extension? [closed]
  5. Convert from MySQL datetime to another format with PHP
  6. Why is mysqli giving a “Commands out of sync” error?
  7. JSON encode MySQL results
  8. PHP mysql insert date format
  9. How to store file name in database, with other info while uploading image to server using PHP?
  10. How to best store user information and user login and password
  11. Error while sending QUERY packet
  12. PDO fetch one column from table into 1-dimensional array
  13. PHP: Calling MySQL Stored Procedure with Both INPUT AND OUTPUT Parameters (NOT “INOUT”)
  14. Detect mysql update/insertion failure due to violated unique constraint
  15. PHP + MySql + Stored Procedures, how do I get access an “out” value?
  16. Get all mysql selected rows into an array
  17. Select from mysql table WHERE field=’$array’?
  18. How to bind SQL variables in PHP?
  19. PHP – Secure member-only pages with a login system
  20. MySQL ORDER BY IN()
  21. mysql – INSERT date range into date columns IF dates don’t overlap with existing ones
  22. php password_verify() hash and pass won’t match
  23. MySQL query using an array [duplicate]
  24. PHP mySQL – Insert new record into table with auto-increment on primary key
  25. How to backup MySQL database in PHP?
  26. Create PHP array from MySQL column
  27. UNION syntax in Cakephp
  28. How do i search from serialize field in mysql database?
  29. What is the PDO equivalent of function mysql_real_escape_string?
  30. Installing PDO driver on MySQL Linux server

Leave a Comment