How to Prevent Duplicate Entries in MySQL When Inserting Data in PHP?

Prevent MySQL Database Duplicate Entries Effectively

Issue:
Inserting thousands of records into a table with duplicate values can lead to data integrity issues. How can we prevent duplicate entries while looping through the script in PHP?

Solution:

1. Create a Unique Key:

   • Define a UNIQUE INDEX on the combination of columns that should be unique (e.g., pageId and name):

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

     Copy after login

2. Handling Duplicates:

   • Now, you need to decide how to handle duplicate entries:

     • Ignore Duplicates:

       $query = "INSERT IGNORE INTO thetable (pageid, name) VALUES (1, 'foo'), (1, 'foo')";

       Copy after login

     • Overwrite Existing Records:

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

       Copy after login

     • Update Counter:

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

       Copy after login

The above is the detailed content of How to Prevent Duplicate Entries in MySQL When Inserting Data in PHP?. For more information, please follow other related articles on the PHP Chinese website!