MySQL Server
MySQL Server

DREAMWEAVER MX -- HOW TO WRITE MySQL UPDATE SET QUERY...

Trying to write a MySQL UPDATE SET query in DW MX but can't make it work.

Here is what I have:

SELECT *
FROM t_dontn_new
UPDATE t_dontn_new
SET t_dontn_new.amt_adm = 5
WHERE t_dontn_new.dontn_id = 254

Another variant would be:

SELECT *
FROM t_dontn_new
UPDATE t_dontn_new
SET t_dontn_new.amt_adm = t_dontn.amt * 0.15
WHERE t_dontn_new.dontn_id = 254

This should be pretty simple, but does not work (for me) in DW MX.

Any help you can offer will be greatly appreciated.

cgustafAsked: 2012-12-26

Who is Participating?

Solutions

cgustafConnect With a Mentor Author Commented: 2013-01-16

I have now found a solution. It may not be the most elegant, but it works. Also, it stays within the DW MX code, but requires a slight edit of that code.

Here is the solution:

  
$insertSQL = sprintf("INSERT INTO t_dontn (donr_id, desig_id, ck_nbr, amt, amt_adm, amt_net, don_comment) VALUES (%s, %s, %s, %s, amt * 0.1, amt * 0.9, %s)",
GetSQLValueString($_POST['id'], "int"),
GetSQLValueString($_POST['desig_id'], "int"),
GetSQLValueString($_POST['ck_nbr'], "text"),
GetSQLValueString($_POST['amt'], "double"),
GetSQLValueString($_POST['amt_adm'], "double"),
GetSQLValueString($_POST['amt_net'], "double"),
GetSQLValueString($_POST['don_comment'], "text"));

Open in new window

The calculations take place in the "VALUES" statment on line 2, and the results are POSTED to the database columns

LVL 61

Kevin CrossChief Technology OfficerCommented: 2012-12-26

The SELECT * FROM t_dontn_new does not belong.

In other words, what happens with just this.

UPDATE t_dontn_new
SET amt_adm = amt_adm * 0.15
WHERE dontn_id = 254
;

Open in new window

cgustafAuthor Commented: 2012-12-26

Well, I use Dreamweaver MX, and would like to execute the UPDATE SET query within the DW MX environment. There, a query of any kind must have SELECT as part of it.

Within the generated PHP script, the query looks like this:

<?php
mysql_select_db($database_ibt_dbc, $ibt_dbc);
$query_Recordset1 = "SELECT * FROM t_dontn_new UPDATE t_dontn_new SET amt_adm = amt_adm * 0.15 WHERE dontn_id = 254 ;";
$Recordset1 = mysql_query($query_Recordset1, $ibt_dbc) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>

This does not work, however.

LVL 61

Kevin CrossChief Technology OfficerCommented: 2012-12-26

I have not heard that requirement at all. If you want to UPDATE the database, the syntax you have will not work. I am not sure what to tell you beyond what I did above. Dreamweaver MX is a programming environment; it restricts you to valid syntax for a given language. In your case, you need valid MySQL within your PHP.

cgustafAuthor Commented: 2012-12-27

Thanks for your reply. Though I have worked in DW over a period of many years, and have never seen a DW query withouth a SELECT statement, perhaps there is an exception to this when it comes to an UPDATE SET query.

However, entering simply:

UPDATE t_dontn_new
SET amt_adm = amt_adm * 0.15
WHERE dontn_id = 254
;

only causes an error. The query does not run anything.

What is your experience working in DW?

LVL 61

Kevin CrossChief Technology OfficerCommented: 2012-12-27

The query will not return anything because it is an UPDATE; therefore, there will be no resultset. What is the error? Did you check the database to see if the UPDATE processed correctly?

LVL 61

Kevin CrossChief Technology OfficerCommented: 2012-12-27

Here is an example: http://www.w3schools.com/php/php_mysql_update.asp

cgustafAuthor Commented: 2012-12-27

I had checked the w3schools example (and many other examples) early on, but they are not helpful, primarily because they do not address the DW environment.

I attach the error message, which is consistent whether the SELECT stmt is included or not.
scr-shot-1.gif

LVL 61

Kevin CrossChief Technology OfficerCommented: 2012-12-28

I programmed in DW for years as a ColdFusion programmer and never had the issue, so I apologize for being unfamiliar with the restriction. I keep going back to invalid php or mysql. For example, in the error there is a comma where a dot should be. Why are you using two-part naming here? Is there more than one involved table?

cgustafAuthor Commented: 2012-12-28

Sorry for the typo in the code. Correcting this does not change the result, however.

The only reason for two-part naming was to make doubly sure the naming is clear. There is only one table involved, so this naming is not necessary, of course.

The PHP is generated by DW MX. Unless there is a bug in that DW code generation, which could be the case (witness the unresolved bug in the MX log-in coding), the PHP should be valid. I have never used an UPDATE SET query before, so have no exprience in resolving the issue.

Here is what I want to accomplish for each new record added to the table:

1. The "amt" column is user filled

2. At the point of inserting the new record, the "amt_adm" column is automatically calculated as "amt * 0.10" and the result is inserted.

3. Next, the "amt_net" column is automatically caclulated as "amt - amt_adm" and the result is inserted.

In my testing, so far, I have only attempted to calculate the "amt_adm" column using the UPDATE SET query.

But perhaps there is a better way to achieve these results?

There is of course the possiblity of simply calculating these results in a report design, but I was hoping to have it all in the table as well.

LVL 61

Kevin CrossChief Technology OfficerCommented: 2012-12-28

Would a trigger work?

EDIT: you are correct that normalization practices dictate one should not store these calculations, but you could do the calculation upfront if you do. For example, the original INSERT could be the following.

INSERT INTO t_dontn_new(amt, amt_adm, amt_net)
VALUES($amt, $amt_adm, $amt_net);

Where you calculate $amt_adm ($amt * 0.1) and $amt_net ($amt * 0.9 OR $amt - $amt_adm) in the PHP code.

The trigger works in a similar way. You could write a trigger on INSERT or UPDATE of amt with following code.

UPDATE t_dontn_new 
SET amt_adm = amt * 0.1
  , amt_net = amt * 0.9 
WHERE dontn_id = NEW.dontn_id;

Open in new window

cgustafAuthor Commented: 2012-12-28

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO t_dontn_new (dontn_id, desig_id, ck_nbr, amt, don_comment, amt_adm, amt_net) VALUES (%s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['dontn_id'], "int"),
                       GetSQLValueString($_POST['desig_id'], "int"),
                       GetSQLValueString($_POST['ck_nbr'], "text"),
                       GetSQLValueString($_POST['amt'], "double"),
                       GetSQLValueString($_POST['don_comment'], "text"));
                       GetSQLValueString($_POST['amt_adm'], "double"));
                       GetSQLValueString($_POST['amt_net'], "double"));

  mysql_select_db($database_ibt_dbc, $ibt_dbc);
  $Result1 = mysql_query($insertSQL, $ibt_dbc) or die(mysql_error());

  $insertGoTo = "enter_don_new.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

Open in new window

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO t_dontn_new (dontn_id, desig_id, ck_nbr, amt, don_comment, amt_adm, amt_net) VALUES (%s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['dontn_id'], "int"),
                       GetSQLValueString($_POST['desig_id'], "int"),
                       GetSQLValueString($_POST['ck_nbr'], "text"),
                       GetSQLValueString($_POST['amt'], "double"),
                       GetSQLValueString($_POST['don_comment'], "text"));
                       GetSQLValueString($_POST['amt_adm'], "double"));
                       GetSQLValueString($_POST['amt_net'], "double"));

  mysql_select_db($database_ibt_dbc, $ibt_dbc);
  $Result1 = mysql_query($insertSQL, $ibt_dbc) or die(mysql_error());

  $insertGoTo = "enter_don_new.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

Open in new window

Thanks.

I now see that because UPDATE SET involves INSERT INTO there is no need for SELECT to be part of the code. INSERT INTO, itself, points to the table and the columns to be updated. Therefore UPDATE SET should somehow be part of the INSERT INTO instance. In DW it seems this is not possible without coding.

I understand the reationale in what you write, but am not clear on how to implement it.

First I wonder if the INSERT INTO must be followed by and UPDATE of that same record, or if the two operations can be combined. Using variables, which is something I'm weak on but want to learn, it should be possible to do it in one operation.

I attach a code snippet of the INSERT INTO which I have tweaked in an attempt to claculate the amt_adm and amt_net columns. This attemtp included the following:

<input name="amt_net" type="hidden" id="amt_net" value="<?php echo $_POST['amt * 0.90']; ?>">
<input name="amt_adm" type="hidden" id="amt_adm" value="<?php echo $_POST['amt * 0.10']; ?>">

This did not work, and I believe the reason is that amt had not yet been inserted.

LVL 61

Kevin CrossChief Technology OfficerCommented: 2012-12-28

Correct. You have to do the calculations after the form submits.

You may need something like doubleval() to ensure that you have a valid amt, but the gist of the solution is as follows.

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $amt = doubleval($_POST['amt']);
  $amt_adm = $amt * 0.10;
  $amt_net = $amt - $amt_adm; // $amt * 0.90

  $insertSQL = sprintf("INSERT INTO t_dontn_new (dontn_id, desig_id, ck_nbr, amt, don_comment, amt_adm, amt_net) VALUES (%s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['dontn_id'], "int"),
                       GetSQLValueString($_POST['desig_id'], "int"),
                       GetSQLValueString($_POST['ck_nbr'], "text"),
                       GetSQLValueString($amt, "double"),
                       GetSQLValueString($_POST['don_comment'], "text"));
                       GetSQLValueString($amt_adm, "double"));
                       GetSQLValueString($amt_net, "double"));

  mysql_select_db($database_ibt_dbc, $ibt_dbc);
  $Result1 = mysql_query($insertSQL, $ibt_dbc) or die(mysql_error());

  $insertGoTo = "enter_don_new.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

Open in new window

cgustafAuthor Commented: 2013-01-10

Sorry for lack of reply -- actually wrote a reply immediatelym and waited for a response. Now I realize my reply was never delivered for some reason, Perhaps I previewed it but never submitted it.

Nevertheless, I tested the above script, but could not make it work. It failed around the "doubleval" on line 2.

It would be nice to get this to work. I did play with "trigger" on the server, but was not able to get any results there.

cgustafAuthor Commented: 2013-01-14

In the MySQL database table I have these fields, among others:

amt
amt_adm
amt_net

Data for amt is inserted into the table via a form.

Following the insertion, the record should be updated with amt * 0.1, and amt_net should be updated with amt * 0.9 .

How can this be accomplished?