Checking for UNIQUE entries without returning error

[kingnutter]

Hi everyone,

 

On my thesaurus site, every word will be submitted with a series of tags. For the purpose of searching, I want to ensure that every tag has a unique ID. All tags are entered into 'tagtable' and assigned an autoincremented tag_id.

 

Words are link to their tags in a table called 'linktable' consisting solely of columns word_id and tag_id.

 

I know that I can specify NOT NULL UNIQUE when creating the table, but if a user enters a duplicate tag I do not want an error message generated.

 

How can I get the database to recognise a duplicate tag and simply assign the new word_id to the original tag_id?

 

Any pointers appreciated,

 

Many thanks,

Gary

[fenway]

You can use INSERT... ON DUPLICATE KEY UPDATE.

[kingnutter]

Thanks for that. I've had a look at mysql.com and can't figure out how to apply the function in this instance.

 

On my site, the user submits a word (entry_title) and several tags (which will later be used in a search facility).

 

There are three tables involved in this process:

 

Entry Table

entry_id -> PRIMARY KEY (id for below)

entry_title -> VARCHAR (The actual word the user has submitted)

 

Tags Table

tag_id -> PRIMARY KEY

tag_name -> VARCHAR (Each individual tag submitted by the user for the above word)

 

Link Table

entry_id

tag_id

 

Essentially once the new word has been entered into the database, I need the following to occur to complete the query:

 

Foreach tag: If tag is unique add to tagstable and autoincrement to generate tag_id

 

Foreach: add entry id and tag id to link table

 

If tag is not unique do not do anything to tagstable. Go straight to linkstable and foreach: link tag_id to entry_id

 

Any help with this code would be very appreciated and how to incorporate ON DUPLICATE KEY UPDATE if you feel it is still useful in this instance.

 

Many thanks,

Gary

[fenway]

Normally if you INSERT and hit a duplicate key, it will throw an error.

 

With ON DUPLICATE KEY UPDATE, you get the chance to tell the database how to treat such a collision -- in your case, you simply want to update the word, and keep everything else the say.