0

I am trying to prevent duplicate copies in a table. I am looking for duplicates where the entire row is a copy of another row. If one element of a row is a copy of somehting from another row, that is not a problem. For example, if the name is repeated in multiple fields, that is ok but if each additional field is the same as another entry, then I would like to prevent my program from adding the entry. Here is what I was going to try:

if(mysql_num_rows(mysql_query("SELECT * FROM table WHERE field1 = '$field1',field2 =     '$field2', field3 = '$field3',field4 = '$field4'"))){
// send some message if entry is within table
} else {
// add unique entry to the table if field is not there
}

Will the above code search and check to see all 4 values are within each row or if all 4 values are located somewhere in each column but not necessarily in each row?

Stagleton
  • 1,060
  • 3
  • 11
  • 35

1 Answers1

1

Add unique key:

ALTER TABLE table ADD UNIQUE( field1);

And than use insert into with ignore keyword:

INSERT IGNORE INTO table ...;

That way:

  • you'll have only one query to database
  • no error will be triggered
  • no duplicity will occur in database (guaranteed by database)

This is nice reading: INSERT IGNORE or INSERT WHERE NOT IN

Community
  • 1
  • 1
Vyktor
  • 20,559
  • 6
  • 64
  • 96
  • would that make it so that the table would not add any entries that had the same name as field1? Can I change the above so that the other fields are also considered within the same row? – Stagleton Feb 03 '12 at 11:42
  • @Stagleton what do you know about `UNIQUE KEYS`? They ensures that data are unique in table, if you crate index `UNIQUE (name)`, you can insert each name just once (the other try creates a failure). When you create it on `UNIQUE (category_id, product_id)`, you can insert `(1,1), (1,2), (2,2)`, but reinserting `(1,1)` will trigger error. When you use `INSERT IGNORE`, error won't be produced, query will just silently stop. You also may use `ON DUPLICATE` (see link at the end of my answer). – Vyktor Feb 03 '12 at 11:47
  • I don't know enough. I'll try that when I get home. Thanks for the help. – Stagleton Feb 03 '12 at 11:52
  • 1
    `A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.` this is all that mysql manual provides and found so far (Jakub Vrana wrote perfect article about using unique keys some time ago http://php.vrana.cz/vyuziti-unikatnich-klicu-v-databazi.php but it's in Czech but you'll may understand it with little help of google translator). – Vyktor Feb 03 '12 at 12:06
  • This doesn't seem to work properly. I had unique fields except for the date and I got an error because the date field was a repeat – Stagleton Feb 03 '12 at 15:30
  • nevermind, because there were already duplicates within the table, an error message was thrown – Stagleton Feb 03 '12 at 15:35