INSERT INTO only if no record exsist

[ubh]

Hey I am trying to figure out a way to preform either an UPDATE or a INSERT INTO depending on if a row exists or not.

I am running an AJAX process that sends a series of elements inline CSS style values into my database.

Problem is that there should only be one row for each element and then its CSS values should be stored in the proceeding columns. What I get however, because my AJAX process runs in a for loop and loops over all elements, is multiple rows each containing that particular loops sent CSS style.

Looks like this:


|element_id|width|height|background-color|
------------------------------------------
|first |null |null |#000000 |
|first |null |100px |null |
|first |200px|null |null |


What I need is to somehow in my php file, try and run a UPDATE process first, and if no row exists then perform an INSERT INTO query.

Anyone know how to conditional a MySQL query like this??

[CFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

[Old Pedant]

The best way to do this is with a Stored Procedure.

You just attempt the UPDATE and, if it fails, then you use an INSERT instead.

Doing this all within a single simple SP is efficient and relatively simple.

[bazz]

try insert ignore.

it will insert if there is no duplicate and will ignore the insert if there would be a duplicate.

bazz

[ubh]

Ok maybe its me but I am not understanding how to work with this syntax here.

This is what I assume its trying to come across as, but I still wont work.


mysql_query("INSERT INTO structureandstyle
(id, $rules, prevSibling, nextSibling) VALUES('$id', '$styles', '$prevSibling','$nextSibling')
ON DUPLICATE KEY UPDATE structureandstyle SET id='$id', $rule='$rule', prevSibling='$prevSibling', nextSibling='$nextSibling' WHERE id='$id'
")or die(mysql_error());

[Old Pedant]

That's only going to work--and Bazz's suggestion is only going to work--*IF* the field that you don't want to duplicate is INDEXED and the index is specified as NO DUPLICATES allowed.

There's nothing "natural" in a database that says you can't have duplicate values. Only a "unique index" can enforce that.

[bazz]

mysql_query("INSERT INTO structureandstyle
(id, $rules, prevSibling, nextSibling) VALUES('$id', '$styles', '$prevSibling','$nextSibling')
ON DUPLICATE KEY UPDATE structureandstyle SET id='$id', $rule='$rule', prevSibling='$prevSibling', nextSibling='$nextSibling' WHERE id='$id'
")or die(mysql_error());

maybe the red bit should be changed by getting rid of the $.

What error are you getting?
bazz