Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

Re^3: MySQL and Duplicate Keys

by Coruscate (Sexton)
on Jan 20, 2003 at 09:16 UTC ( #228307=note: print w/replies, xml ) Need Help??

in reply to Re: Re: MySQL and Duplicate Keys
in thread MySQL and Duplicate Keys

I don't really know much of anything about ON DUPLICATE KEY UPDATE, but I don't think it does what you are hoping for. Even with the duplicate key update in there, your primary key values still have to be unique. What I believe that directive does is allow you to change the existing primary key value to make room for the one you're trying to insert. In your case, you can't change the ISBN number, so you'll either have to check to see if the ISBN number is already entered, or you could simply pass the IGNORE directive with the INSERT. If the ISBN is already entered, then no error will be generated, but the table will not be updated in any way.

Your best bet would be to check the existence of the ISBN and then present the user entering the data with options for selecting which title/author, etc fits the bill the best. So if 2 people issue the same ISBN, but they supply slightly different titles, ask the user to select between their option or the option already in the DB. I'm not sure how well that system would work, but I'm sure you'll come up with something.

One more possible method perhaps: have you thought about supplying the ISBN number to an online book store and retrieving book info from the html? Just an idea...

Replies are listed 'Best First'.
Re: Re^3: MySQL and Duplicate Keys
by cidaris (Friar) on Jan 20, 2003 at 09:23 UTC
    These were my reasons exactly. I was originally writing a method that would check for pre-existing key and then present options, etc. but was hoping to keep the process as simple as possible for the end user... You know, if they enter the same ISBN twice, it just uses the latest entered data... Transparent to the user. I came across the DUPLICATE KEY thing while browsing and thought maybe I could shoehorn it into my needs... That's why I asked for how people were using it before I showed my own or my reasons... I suppose it makes a little more sense now.

    I guess I can still make it transparent to the user, it'll just require more code for the system... Not really a big deal.

    I appreciate your input though,

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://228307]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (3)
As of 2023-06-07 03:32 GMT
Find Nodes?
    Voting Booth?
    How often do you go to conferences?

    Results (29 votes). Check out past polls.