Clever Geek Handbook

Preventing Duplicate Attachments in SQL with PHP

I am going to run thousands of queries in SQL and I need to prevent duplicate 'domain' field. This has never been done before and any help would be appreciated.

0


source to share


6 answers


You probably want to create a UNIQUE constraint on the Domain field - this constraint will generate an error if you create two rows that have the same domain in the database. For an explanation see this tutorial at the W3C school -

http://www.w3schools.com/sql/sql_unique.asp



If that doesn't solve your problem, please clarify the database you chose to use (MySql?).

NOTE. This limitation is completely different from your choice of PHP as a programming language, it is a SQL database definition thing. The huge advantage of expressing this constraint in SQL is that you can trust the database to keep the constraint even when people are importing / exporting data from the database, your buggy application, or another application is sharing the database.

+9


source


If it is a requirement for absolute database integrity (it is unlikely to change and existing data does not have this problem), then I would enforce it on a database with a unique constraint.



As for detecting it before or after trying to notify the user, there are a number of methods that can be used.

+1


source


Where does the data come from? Is it something you only want to run once, or a couple of times, or often? If the domain value already exists, do you just want to skip the insert or do something else (i.e. increment the counter)?

There are many possible solutions depending on your answers:

  • Pre-sort the data, eliminate duplicates, then insert (assumes relatively static data, empty table to start with)

  • Use an associative array in PHP as a local domain cache (if the table already contains data, start by reading the existing content; not thread safe, but works if it only runs once at a time)

  • Make domain UNIQUE a column and write wrapper code to handle return errors

  • Make domain UNIQUE or PRIMARY KEY and use ON DUPLICATE KEY clause: INSERT INTO mydata (domain, account) VALUES ('firstdomain', 1), ('seconddomain', 1), ("third domain", 1) KEY TO LONG KEY UPDATE count = count + 1

  • Insert all data into table, then remove duplicates

Note that batch inserts (i.e. using multiple value clauses for each operator) can be significantly faster.

+1


source


I'm not sure I understood your question, but perhaps you are looking for the SQL "UNIQUE" constraint . If the request tries to insert a pre-existing value into a field, you (PHP) will be notified of this constraint violation.

0


source


There are several ways to approach this. You can set a unique constraint on this column (for example, primary key). This will cause the insert to fail if that domain was also inserted. You can also insert all duplicate domains and just delete them later. This will work well if it is not that many of the domains are duplicated. There are several questions already asked for finding duplicate strings.

0


source


It can be doen with sql, not php.

I am assuming you are using MySQl, but the same principles will work with different databases.

enter the primary key in the Domain field. (makes sense since it is unique.)

Use UPDATE instead of INSERT.

if the primary key already exists (which you are trying to put into the table), the update will update the existing tuple rather than create a new tuple.

so that you overwrite the existing data if it is different and if it is identical the update will be skipped.

0


source






More articles:


All Articles