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);

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')";

     • Overwrite Existing Records:

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

     • Update Counter:

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

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!