Re: Altering SQL code from Update to Insert

You still have to write the matching UPDATE and INSERT, but it's written so you can switch between the two easily:

$upd_sql = "UPDATE table SET [Field1]=?, [Field2]=?, [Field3]=? where 
+Field0=?";
$ins_sql = "INSERT INTO table ( [Field1], [Field2], [Field3], [Field0]
+ ) VALUES ( ?, ?, ?, ? )";

$sth = $dbh->prepare($exists ? $ins_sql : $upd_sql);
$sth->execute(
   'stringvalue',
   numericvalue,
   booleanvalue,
   index,
);
[download]

Comment on Re: Altering SQL code from Update to Insert Download Code

Replies are listed 'Best First'.
Re^2: Altering SQL code from Update to Insert by bradcathey (Prior) on Dec 11, 2004 at 18:33 UTC
This looks nice, especially if it does what I think it should. Currently, the scenario I need this for is when I want to allow someone to update ( `UPDATE`) a record through an HTML form, as opposed to entering it for the first time in the form (`INSERT`). I redirect passed on a hidden field in my form that is set to `edit` or `new`. My question is: where does `$exist` come from in your `prepare` statement? Thanks. —Brad "Don't ever take a fence down until you know the reason it was put up." G. K. Chesterton	[reply] [d/l] [select]
Re^3: Altering SQL code from Update to Insert by ikegami (Patriarch) on Dec 11, 2004 at 18:53 UTC
Out of thin air, just like it wasn't in your code. Usually, you already know if you're updating or inserting a record on what the user wants to do. If you don't know, you could try to do an INSERT, and if that fails, try to do an UPDATE.	[reply]