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

cgustaf
cgustaf used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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

Author

Commented:
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.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
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?
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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?
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:

Author

Commented:
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
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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?

Author

Commented:
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.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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

Author

Commented:
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.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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

Author

Commented:
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.

Author

Commented:
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?
Commented:
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

Author

Commented:
Question is now answered (by myself!)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial