Preventing Duplicate Inserts Into SQL With PHP

I'm going to running thousands of queries into SQL and I need to prevent the duplication of field 'domain'. Never had to do this before and any help would be appreciated.

--------------Solutions-------------

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

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

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

NOTE: This constraint is completely separate from your choice of PHP as a programming language, it is a SQL database definition thing. A huge advantage of expressing this constraint in SQL is that you can trust the database to preserve the constraint even when people import / export data from the database, your application is buggy or another application shares the database.

If this is an absolute database integrity requirement (It's not likely to change, nor does existing data have this problem), I would enforce it at the database with a unique constraint.

As far as detecting it before or after the attempt in order to notify the user, there are a number of techniques which could be used.

Where is the data coming from? Is this 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 (ie increment a counter)?

Depending on your answers, there are many possible solutions:

  1. Pre-sort your data, eliminate duplicates, then insert (assumes relatively static data, empty table to begin with)
  2. Use an associative array in PHP as a local domain-value cache (if table already contains data, start by reading existing content; not thread-safe, but works if it only runs once at a time)
  3. Make domain a UNIQUE column and write wrapper code to handle return errors
  4. Make domain a UNIQUE or PRIMARY KEY column and use an ON DUPLICATE KEY clause: INSERT INTO mydata ( domain, count ) VALUES ( 'firstdomain', 1 ), ( 'seconddomain', 1 ), ( 'thirddomain', 1 ) ON DUPLICATE KEY UPDATE count = count+1
  5. Insert all data into the table, then remove duplicates

Note that batching inserts (ie using multiple value clauses per statement) can be significantly faster.

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

There are a bunch of ways to approach this. You could set a unique constraint (like a primary key) on that column. This will cause the insert to fail if that domain has also been inserted. You could also insert all of the duplicate domains and just delete them later on. This will work well if not that many of the domains are duplicated. There are a few questions posted already on finding duplicate rows.

This can be doen with sql, rather than with php.

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

make the Domain column the primary key. (makes sense, as it has to unique.)

Rather than using INSERT, use UPDATE.

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

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

Category:database Time:2008-11-09 Views:1

Related post

  • How to use SqlBulkCopy to prevent duplicates insert? 2012-03-29

    I am going to use SqlBulkCopy, but I need to prevent duplicates insert. What is the best way to do it? Have I do check existence for each item before make execute WriteToServer command? --------------Solutions------------- Bring all the values form t

  • Prevent Duplicate Entries in SQL 2012-02-10

    How do I prevent a duplicate entry from a text field by using SQL Query? --------------Solutions------------- If you want to prevent the insertion of duplicate rows, you can add a unique index: CREATE UNIQUE INDEX ix_IndexName ON MyTable(MyUniqueFiel

  • Rails: Prevent duplicate inserts due to pressing back button and save again 2011-01-11

    Think about a simple Rails scaffold application with a "new" action containing a form to add records to a database with a "save" button. After the "create" action the controller redirects to the "show" action, where the user can use the "edit" link t

  • Prevent duplicate insert without session 2011-01-31

    im working on sharepoint 2010 (using visual webpart) is there any way to prevent duplicate insert in a list when page is refreshed using F5, without using session thx --------------Solutions------------- The sharepoint "way" is usually to redirect to

  • Prevent duplicate inserts without using unique keys in Oracle 2011-11-08

    This is a use case in member enrollment via web application/web service. We have a complex algorithm for checking if a member is duplicate, by looking at multiple tables like phone,address etc. The algorithm varies based on member's country. So this

  • Preventing duplicates in an SQL query? 2010-07-03

    Is there an easy way when inserting a new record to make it fail if one of the fields is a duplicate of one of the other fields? I don´t want the field to be a primary key or anything like that... --------------Solutions------------- Set the column a

  • prevent duplicate insert by using updatepanel 2011-01-14

    i wrote a page which are several fields and there is gridview which keeps the data inserted bottom of the page and i added a updatepanel... when i click the insert double data inserting to grid how to prevent it... --------------Solutions------------

  • How to prevent duplicates in an SQL cross-query 2012-01-29

    so Here's my query: SELECT b1.beer as beer1, b2.beer as beer2 , b1. price as price1, b2.price as price2 (b1.price+b2.price) as pair , b1.bar as bar FROM `beer`.`sells` b1, `beer`.`sells` b2 WHERE b1.beer <> b2.beer AND b1.bar = b2.bar; I want t

  • can isolation levels prevent duplicate insert? 2012-04-09

    i have a table like this UserPageId (Primary Key) int UserId (user Foreign Key) int PageId (page Foreign Key) int and the whole senario is to prevent user to add one page more than once,,according to each user can be use with several person and we mi

  • Preventing Duplicate Table Inserts 2011-06-17

    The database is SQL Server 2008. I have a query which extracts rows from one or more tables and then attempts to insert them into a table variable. I'd like an efficent way to prevent duplicate inserts, so what I came up with was: INSERT INTO @MyTabl

  • preventing duplicate table inserts using jdo in android connected appengine 2012-03-18

    I would like to know how to prevent duplicate inserts when doing an RPC call from an Android client connected to app engine. Below is my code and what I tried at the back-end but when I do this I get an "Internal Server Error". public void createenti

  • Adding a constraint to prevent duplicates in SQL Update Trigger 2009-02-13

    We have a user table, every user has an unique email and username. We try to do this within our code but we want to be sure users are never inserted (or updated) in the database with the same username of email. I've added a BEFORE INSERT Trigger whic

  • How to prevent duplicate records being inserted with SqlBulkCopy when there is no primary key 2010-04-07

    I receive a daily XML file that contains thousands of records, each being a business transaction that I need to store in an internal database for use in reporting and billing. I was under the impression that each day's file contained only unique reco

  • Prevent Duplicate SQL entries 2011-03-23

    I want to be able to prevent duplicate SQL text field rows. That is, if row 1 has the name field already defined as "John Smith", I don't want it to be able to add another "John Smith" (as common as that name might be). I tried checking if it existed

  • How to prevent duplicate values from inserting mySQL considering two columns? 2011-10-20

    Here is my table: Table Name: UserLinks Link_ID User_1 User_2 1 234325 100982 2 116727 299011 3 399082 197983 4 664323 272351 Basically, in this table a duplicate value is: Link_ID User_1 User_2 1 232 109 2 109 232 I have looked around and found that

  • How to prevent duplicate rows in insert query using Oracle? 2009-10-31

    I have table A in Oracle that has a primary key (id). I need to insert data into this table. How do I prevent duplicate rows? --------------Solutions------------- If the id column is marked as the PK you will not be able to insert a duplicate key, th

  • Creating trigger in SQL Server 2005 (has to work in 2008 too) to prevent duplicates? 2010-03-07

    I have table that I insert data with following query (from c# code): INSERT INTO [BazaZarzadzanie].[dbo].[Wycena] ([KlienciPortfeleKontaID] ,[WycenaData] ,[WycenaTyp] ,[WycenaWartosc] ,[WycenaWaluta] ,[WycenaUzytkownik] ,[WycenaUzytkownikData]) VALUE

  • Prevent duplicate record insert on manual page refresh 2010-09-16

    I have a problem which has only just surfaced itself. I'm working in a MVC environment. The method names in my interface class match those of the request module and action i.e. ?module=test&action=action would result in a method called public fun

  • Insert Query (that prevents duplicates) 2014-09-07

    I saw a question similar to mine and I tried to modify the code but to no avail. I have a multiple-field index but it doesn't seem to be stopping duplicates from being imported or entered. I will post here the fields in the index as well as the code

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 1.099 (s). 13 q(s)