close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Problem with an insert and update on same page

Thread began 2/09/2013 8:11 pm by mrs | Last modified 2/11/2013 10:23 pm by mrs | 681 views | 5 replies |

mrs

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.

Attached Files
forum_reply.php.zip
Sign in to reply to this post

mrs

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.

Sign in to reply to this post

Jason ByrnesWebAssist

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.

Sign in to reply to this post

mrs

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

Sign in to reply to this post

Jason ByrnesWebAssist

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:
<?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:
<?php echo($row_recordsetName['columnName'] + 1); ?>
Sign in to reply to this post

mrs

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.

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...