Conditional INSERT ?! Does that exists ?

pedroteixeira07

Hello everybody,

I need to do an INSERT to my DB but just in case the value doesn't exists...

For example:

INSERT INTO Keyword (Keyword_ID, Keyword) VALUES ('','$keyword')

if the Keyword doesn't exists in the Keyword Table, otherwise it doesn't do anything.

Any help? Is there any syntax for this or should I go PHP?

bradgrafelman

Add a UNIQUE key on the Keyword column and check for error number 1062 (duplicate data for key) when executing that query.

sfullman

I don't know but I assume your table Keyword is in fact designed so that there is only one keyword "grapefruit" in the table.

So, first make the Keyword FIELD unique (if there are non-unique values congrats you have some clean-up to do 🙂

Next, use the following code when you "insert":

REPLACE INTO Keyword SET Keyword='".addslashes($Keyword)."'

This piggybacks on the UNIQUE key you set on the Keyword field. It will, if the record is present, DELETE the existing record and immediately INSERT the new one - on a small table like this, effectively no change will occur.

If there is no record like this considering all unique keys, then it will be inserted.

Interesting note: you can tell if it was there after the operation by checking mysql_affected_rows() - count will be 2 (one deleted, one inserted) if it was.

This works fine on small atomic tables like this, but if there is other associated data you want to preserve in other fields, it's not good cause you'll lose that data.

Cheers,
Samuel

bradgrafelman

Small note:

sfullman;10959518 wrote:

REPLACE INTO Keyword SET Keyword='".addslashes($Keyword)."'

The generic [man]addslashes/man function should never be used to prepare data for a MySQL query.

sfullman

good point, and if you're receiving the data from a form post, with magic quotes enabled, should already be excaped properly. 🙂

bradgrafelman

sfullman;10959527 wrote:
good point, and if you're receiving the data from a form post, with magic quotes enabled, should already be excaped properly. 🙂

Considering that magic quotes does the same thing addslashes() does, that's entirely incorrect. 😉

Besides, magic quotes should be long gone by now anyways.

sfullman

pedroteixeira07,

I made a mistake on this. You probably CANNOT use REPLACE INTO. Here's why:

when you do replace into, you are deletin the old record with it's primary key (assumed auto-increment), and other tables may depend on this. because of this, your only option is php logic:

if( $keywordid=q("SELECT ID FROM Keyword WHERE Keyword='$Keyword'", O_VALUE)){
   //OK, you have the keyword id
}else{
  //create it
   $keywordid=q("INSERT INTO Keyword SET Keyword='$Keyword'", O_INSERTID);
}

you may wonder about function q() - like in James Bond, top secret 🙂 a function I developed but you can see the logic there.

This way key values for keywords don't get changed around; sorry for the earlier post