Re: Avoiding Duplicate Records on MySQL Insert

["Shannon ODonnell" <sodonnell@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Never mind.  I didn't realize that my database had to be empty (no
duplicates) before I made the key value unique.

I thought I was adding the unique key to the database but it wasn't because
there was already duplicates in it.

D'oh!

So much to learn to be a web programmer!!!



-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx] On
Behalf Of Shannon ODonnell
Sent: Thursday, January 21, 2010 10:51 AM
To: 'Web Enabling the AS400 / iSeries'
Subject: [WEB400] Avoiding Duplicate Records on MySQL Insert

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