...

View Full Version : INSERT INTO only if no record exsist



ubh
03-03-2009, 01:44 AM
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
03-03-2009, 01:54 AM
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Old Pedant
03-03-2009, 03:05 AM
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
03-03-2009, 04:57 AM
try insert ignore.

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

bazz

ubh
03-03-2009, 05:16 AM
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
03-03-2009, 07:22 AM
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
03-03-2009, 07:23 AM
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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum