Insert or Update behaviours on same form - Inserts a new record with update

Report · Feb 28, 2012

Hi,

I am trying to create a form that will allows me to insert or update a record. I am using a post i found here http://forums.adobe.com/message/1927405#1927405. I have almost got this working to how i want it, however I have found that when i update a record it is also inserting a new record. Looking at the post made David, i think this would also do the insert at time of update as there seems to be no logic for the update.

If anyone could suggest a way to incorporate the appropriate logic that would be really appreciated

Cheers

$admitid_DetailRS1 = $_GET['admitid'];

$noteid_DetailRS1 = $_GET['noteid'];

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

$insertSQL1 = sprintf("INSERT INTO notes (admitid, note_date, note_time, table_name) VALUES (%s, %s, %s, 'notes_resp')",

GetSQLValueString($admitid_DetailRS1['admitid'], "int"),

GetSQLValueString($_POST['note_date'], "date"),

GetSQLValueString($_POST['note_time'], "time"));

$insertSQL2 = sprintf("INSERT INTO notes_resp (noteid, resp_comment) VALUES (LAST_INSERT_ID(), %s)",

GetSQLValueString($_POST['resp_comment'], "text"));

mysql_select_db($database_neoData, $neoData);

$Result1 = mysql_query($insertSQL1, $neoData) or die(mysql_error());

$Result2 = mysql_query($insertSQL2, $neoData) or die(mysql_error());

$UpdateSQL1 = sprintf("UPDATE notes_resp SET resp_comment = %s WHERE noteid = %s",

GetSQLValueString($_POST['resp_comment'], "text"),

GetSQLValueString($noteid_DetailRS1, "int"));

$UpdateSQL2 = sprintf("UPDATE notes SET note_date = %s AND note_time = %s WHERE noteid = %s",

GetSQLValueString($_POST['note_date'], "date"),

GetSQLValueString($_POST['note_time'], "time"),

GetSQLValueString($noteid_DetailRS1, "int"));

mysql_select_db($database_neoData, $neoData);

$Result1 = mysql_query($UpdateSQL1, $neoData) or die(mysql_error());

$Result2 = mysql_query($UpdateSQL2, $neoData) or die(mysql_error());

echo "<script language=javascript>window.opener.location.reload();self.close();</script>";

}

$noteid_selectNotes = "-1";

if (isset($_GET['noteid'])) {

$noteid_selectNotes = $_GET['noteid'];

}

mysql_select_db($database_neoData, $neoData);

$query_selectNotes = sprintf("SELECT * FROM notes

LEFT JOIN notes_resp

ON notes.noteid=notes_resp.noteid

WHERE notes.noteid = %s",

GetSQLValueString($noteid_selectNotes, "int"));

$selectNotes = mysql_query($query_selectNotes, $neoData) or die(mysql_error());

$row_selectNotes = mysql_fetch_assoc($selectNotes);

$totalRows_selectNotes = mysql_num_rows($selectNotes);

Report · Feb 28, 2012

You only have one conditional expression in your code - the If statement that tests if MM_insert is set. If true, it is executing both insert and update statements. What you need is to pass a value from the form that indicates if the operation should be an insert or update, and then branch to the correct SQL statement accordingly.

Report · Feb 29, 2012

Thanks for the reply Bregent...yes i noticed the one conditional expression..i just followed the post by David and then realised when the inserts were occuring. I was wondering what other conditional statements would be necessary to get this working.....i'd not thought of a value from the form.

I've done a bit more research and discovered REPLACE INTO and ON DUPLICATE KEY UPDATE http://www.kavoir.com/2009/05/mysql-insert-if-doesnt-exist-otherwise-update-the-existing-row.html.... i can't test these out at the moment, but would be interested in your view on these

Thanks for all your assistance