× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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






As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.