Avoiding Duplicate Records on MySQL Insert

["Shannon ODonnell" <sodonnell@xxxxxxxxxxxxxxxxxxxxxxxxx>]

I am having a problem trying to avoid duplicate records on my MySQL
database's  primary/unique key.

 

If I have a database named "DB1"  with a primary key value named "ID1"
(which is also the index).

 

And then I attempt to insert a second record to the database that has a
duplicate ID1 value of "A"

 

I keep getting an error.

 

This is the PHP statement:

 

  $keyvalue="A";

 

    $query = "INSERT INTO DB1 (ID1) VALUES( '$keyvalue') ON DUPLICATE UPDATE
ID1=VALUES('$keyvalue')";  

                

                $result = mysqli_query($cxn, $query)

                       or die("Unable to insert record into DB1: \n"."ID1:
".$keyvalue."     My sql error was: ".mysqli_error($cxn));

 

This returns the error: 

 

 Unable to insert record into DB1: ID1: A    My sql error was:  You have an
error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near 'UPDATE ID1=VALUES('A')' at
line 1

 

 

 

 

I also tried:

 

    $query = "INSERT IGNORE INTO DB1 (ID1) VALUES( '$keyvalue') ";  

 

 

And that did not generate an error but it did allow the duplicate value into
the table.

 

What am I missing here?   It seems like such an easy thing to accomplish
(and with a DB2/400 table and RPG it would be!).

 

Thanks!


Shannon O'Donnell