Re: Avoiding Duplicate Records on MySQL Insert

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