Problem with an insert and update on same page

I have got an insert new record which is working great, and I have an update which is not. They are on the same form and both trigger when the button 'Insert' is pressed.

Both of them are entering into the same table, but the update is obviously updating an existing record. The update is working to the point that it is entering content into the correct record, but I am not getting the correct values entered; I am only getting '0' and '0000-00-00 00:00:00'... an INT column and DATETIME column.

For the INT I am trying to use fld_fREPLYCOUNT = fld_fREPLYCOUNT + 1 to increment each time the record is updated, and for the DATETIME I am simply using NOW() to record when it was updated. I can't use timestamp to update automatically as this will have an affect on ordering elsewhere.

Is it possible to update and insert to the same table from the same form at once?

Anyway, the insert is created with the DataAssist wizard, and then I have used the Update Single Record behaviour for the update. Here's the code that has been produced by the WA wizards:


<?php
// WA DataAssist Update
if ($_SERVER["REQUEST_METHOD"] == "POST") // Trigger
{
$WA_connection = $conn_mrs;
$WA_table = "tbl_forumPOSTS";
$WA_redirectURL = "";
if (function_exists("rel2abs")) $WA_redirectURL = $WA_redirectURL?rel2abs($WA_redirectURL,dirname(__FILE__)):"";
$WA_keepQueryString = false;
$WA_indexField = "fld_fID";
$WA_fieldNamesStr = "fld_fRELPYCOUNT|fld_fREPLYDATE";
$WA_fieldValuesStr = "fld_fREPLYCOUNT = fld_fREPLYCOUNT + 1" . $WA_AB_Split . "NOW()";
$WA_columnTypesStr = "none,none,NULL|',none,NULL";
$WA_comparisonStr = "=|=";
$WA_fieldNames = explode("|", $WA_fieldNamesStr);
$WA_fieldValues = explode($WA_AB_Split, $WA_fieldValuesStr);
$WA_columns = explode("|", $WA_columnTypesStr);

$WA_where_fieldValuesStr = "".$_SESSION['USES_threadID'] ."";
$WA_where_columnTypesStr = "none,none,NULL";
$WA_where_comparisonStr = "=";
$WA_where_fieldNames = explode("|", $WA_indexField);
$WA_where_fieldValues = explode($WA_AB_Split, $WA_where_fieldValuesStr);
$WA_where_columns = explode("|", $WA_where_columnTypesStr);
$WA_where_comparisons = explode("|", $WA_where_comparisonStr);

$WA_connectionDB = $database_conn_mrs;
mysql_select_db($WA_connectionDB, $WA_connection);
@session_start();
$updateParamsObj = WA_AB_generateInsertParams($WA_fieldNames, $WA_columns, $WA_fieldValues, -1);
$WhereObj = WA_AB_generateWhereClause($WA_where_fieldNames, $WA_where_columns, $WA_where_fieldValues, $WA_where_comparisons );
$WA_Sql = "UPDATE `" . $WA_table . "` SET " . $updateParamsObj->WA_setValues . " WHERE " . $WhereObj->sqlWhereClause . "";
$MM_editCmd = mysql_query($WA_Sql, $WA_connection) or die(mysql_error());
if ($WA_redirectURL != "") {
if ($WA_keepQueryString && $WA_redirectURL != "" && isset($_SERVER["QUERY_STRING"]) && $_SERVER["QUERY_STRING"] !== "" && sizeof($_POST) > 0) {
$WA_redirectURL .= ((strpos($WA_redirectURL, '?') === false)?"?":"&").$_SERVER["QUERY_STRING"];
}
header("Location: ".$WA_redirectURL);
}
}
?>



And I have also tried the following:

<?php
// WA DataAssist Update
if ($_SERVER["REQUEST_METHOD"] == "POST") // Trigger
{
$WA_connection = $conn_mrs;
$WA_table = "tbl_forumPOSTS";
$WA_redirectURL = "";
if (function_exists("rel2abs")) $WA_redirectURL = $WA_redirectURL?rel2abs($WA_redirectURL,dirname(__FILE__)):"";
$WA_keepQueryString = false;
$WA_indexField = "fld_fID";
$WA_fieldNamesStr = "fld_fRELPYCOUNT|fld_fREPLYDATE";
$WA_fieldValuesStr = "".((isset($_POST["fld_fREPLYCOUNT"]))?$_POST["fld_fREPLYCOUNT"]:"") ."" . $WA_AB_Split . "".((isset($_POST["fld_fREPLYDATE"]))?$_POST["fld_fREPLYDATE"]:"") ."";
$WA_columnTypesStr = "none,none,NULL|',none,NULL";
$WA_comparisonStr = "=|=";
$WA_fieldNames = explode("|", $WA_fieldNamesStr);
$WA_fieldValues = explode($WA_AB_Split, $WA_fieldValuesStr);
$WA_columns = explode("|", $WA_columnTypesStr);

$WA_where_fieldValuesStr = "".$_SESSION['USES_threadID'] ."";
$WA_where_columnTypesStr = "none,none,NULL";
$WA_where_comparisonStr = "=";
$WA_where_fieldNames = explode("|", $WA_indexField);
$WA_where_fieldValues = explode($WA_AB_Split, $WA_where_fieldValuesStr);
$WA_where_columns = explode("|", $WA_where_columnTypesStr);
$WA_where_comparisons = explode("|", $WA_where_comparisonStr);

$WA_connectionDB = $database_conn_mrs;
mysql_select_db($WA_connectionDB, $WA_connection);
@session_start();
$updateParamsObj = WA_AB_generateInsertParams($WA_fieldNames, $WA_columns, $WA_fieldValues, -1);
$WhereObj = WA_AB_generateWhereClause($WA_where_fieldNames, $WA_where_columns, $WA_where_fieldValues, $WA_where_comparisons );
$WA_Sql = "UPDATE `" . $WA_table . "` SET " . $updateParamsObj->WA_setValues . " WHERE " . $WhereObj->sqlWhereClause . "";
$MM_editCmd = mysql_query($WA_Sql, $WA_connection) or die(mysql_error());
if ($WA_redirectURL != "") {
if ($WA_keepQueryString && $WA_redirectURL != "" && isset($_SERVER["QUERY_STRING"]) && $_SERVER["QUERY_STRING"] !== "" && sizeof($_POST) > 0) {
$WA_redirectURL .= ((strpos($WA_redirectURL, '?') === false)?"?":"&").$_SERVER["QUERY_STRING"];
}
header("Location: ".$WA_redirectURL);
}
}
?>

With these hidden fields:

<input id="fld_fREPLYCOUNT" name="fld_fREPLYCOUNT" type="hidden" value="fld_fREPLYCOUNT = fld_fREPLYCOUNT + 1">
<input id="fld_fREPLYDATE" name="fld_fREPLYDATE" type="hidden" value="NOW()">



I am certain that the issue lies in what I am entering into the Value field of the bindings tab in the Update Single Record wizard... I am literally entering fld_fREPLYCOUNT = fld_fREPLYCOUNT + 1 and NOW(). Page in question is attached.

What am I doing wrong?

Thanks.

Forgot to mention that I entered <?php ECHO($WA_Sql);?> at the end of the update to see if I was getting errors and I did get this:

UPDATE `tbl_forumPOSTS` SET `fld_fRELPYCOUNT` = 0, `fld_fREPLYDATE` = 'NOW()' WHERE `fld_fID`=5
Warning: Cannot modify header information - headers already sent by (output started at /Users/matthewstuart/Sites/ktc/forum_reply.php:347) in /Users/matthewstuart/Sites/ktc/forum_reply.php on line 385

I have since moved the ECHO and the error has disappeared, but I am still using the same code, so I am not sure if the ECHO itself was causing the headers error! So the lines have changed a little, and the ECHO can now be found just before the DOCTYPE declaration.

using the hidden form elements is the right approach, but I dont understand why you are using both an update and an insert?

you don't appear to be retuning a record to update using a recordset, the update is only updating these two columns?

I dont see the purpose behind the update.

I have built a forum, and it works well apart from a couple of things. I want to be able to display how many replies have been submitted, and when they were submitted. Because I am only displaying the initial thread post on the results page (just as this forum does), I need to be able to update the original records date so that it then appears at the top of the results page with the amount of replies displayed... a bit of user friendliness really. I can't use timestamp because I already have a counter in place that increments by 1 each time the page is viewed. As a result, auto updating a timestamp would falsely reorder the results page.

The original thread and its replies all come from one table and I am relating them by a thread ID linking to the original post ID

So, that's my reason for the insert and update from the same form post and only needing to update those two columns.

I did try a recordset (rs_replyID) to give me the record that I want to update, but it returned an ID of '0' in the URL rather than the correct ID and caused an error when inserting and updating. I chose to use $_SESSION['USES_threadID'] which actually updated the correct record, but only with 0's. So I assumed I had done something wrong in the Update Single Record wizard. I didn't actually think that I'd need to use a recordset for this as they only retrieve content from the DB don't they?

Is update and insert from one form post possible with MySQL? I am told it's possible with SQLServer by a .NET expert I know, and he can't see why it should be in MySQL, but, I know there's differences between SQLServer and MySQL.

Does the order have an effect on locking the table? I have it in update first, then insert... should it be insert then update?

OR... have I simply entered the wrong code into the wizards Binding Panel value? Is NOW() valid code to enter there?

Thanks

you need to use a recordset to populate the count field


set the recordset to fiter using the $_SESSION['USES_threadID'] session variable to pull the correct record.


for the date hidden element, use the following code for todays date as the value:

<?php echo(date("Y-m-d")); ?>

for the count hidden element, you will need to use the recordset variable, and perform path on it:

<?php echo($row_recordsetName['columnName'] + 1); ?>

Well, after what I can only assume is about 80 attempts at trying to figure this out, I have finally cracked both inserting and updating from one form post! The insert works fine... always has, and now the update does too:


<?php
// WA DataAssist Update
if (isset($_POST["Insert"]) || isset($_POST["Insert_x"])) // Trigger
{
$WA_connection = $conn_mrs;
$WA_table = "tbl_forumPOSTS";
$WA_redirectURL = "forum_detail.php?fld_fID=".$_SESSION['USES_threadID']."#b";
if (function_exists("rel2abs")) $WA_redirectURL = $WA_redirectURL?rel2abs($WA_redirectURL,dirname(__FILE__)):"";
$WA_keepQueryString = false;
$WA_indexField = "fld_fID";
$WA_fieldNamesStr = "fld_fRELPYCOUNT|fld_fREPLYDATE";
$WA_fieldValuesStr = "".$row_rs_replyID['fld_fRELPYCOUNT'] + 1 ."" . $WA_AB_Split . "".$row_rs_replyID['fld_fREPLYDATE'] = date('Y-m-d H:i:s') ."";
$WA_columnTypesStr = "none,none,NULL|',none,NULL";
$WA_comparisonStr = "=|=";
$WA_fieldNames = explode("|", $WA_fieldNamesStr);
$WA_fieldValues = explode($WA_AB_Split, $WA_fieldValuesStr);
$WA_columns = explode("|", $WA_columnTypesStr);

$WA_where_fieldValuesStr = "".$_SESSION['USES_threadID'] ."";
$WA_where_columnTypesStr = "none,none,NULL";
$WA_where_comparisonStr = "=";
$WA_where_fieldNames = explode("|", $WA_indexField);
$WA_where_fieldValues = explode($WA_AB_Split, $WA_where_fieldValuesStr);
$WA_where_columns = explode("|", $WA_where_columnTypesStr);
$WA_where_comparisons = explode("|", $WA_where_comparisonStr);

$WA_connectionDB = $database_conn_mrs;
mysql_select_db($WA_connectionDB, $WA_connection);
@session_start();
$updateParamsObj = WA_AB_generateInsertParams($WA_fieldNames, $WA_columns, $WA_fieldValues, -1);
$WhereObj = WA_AB_generateWhereClause($WA_where_fieldNames, $WA_where_columns, $WA_where_fieldValues, $WA_where_comparisons );
$WA_Sql = "UPDATE `" . $WA_table . "` SET " . $updateParamsObj->WA_setValues . " WHERE " . $WhereObj->sqlWhereClause . "";
$MM_editCmd = mysql_query($WA_Sql, $WA_connection) or die(mysql_error());
if ($WA_redirectURL != "") {
if ($WA_keepQueryString && $WA_redirectURL != "" && isset($_SERVER["QUERY_STRING"]) && $_SERVER["QUERY_STRING"] !== "" && sizeof($_POST) > 0) {
$WA_redirectURL .= ((strpos($WA_redirectURL, '?') === false)?"?":"&").$_SERVER["QUERY_STRING"];
}
header("Location: ".$WA_redirectURL);
}
}
?>


Needed to edit this line:

$WA_fieldValuesStr = "".$row_rs_replyID['fld_fRELPYCOUNT'] + 1 ."" . $WA_AB_Split . "".$row_rs_replyID['fld_fREPLYDATE'] = date('Y-m-d H:i:s') ."";

Basically added the + 1 and the date('Y-m-d H:i:s')

Just incase it's useful to anybody else out there.