Solved

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

Posted on 2012-12-26
16
373 Views
Last Modified: 2013-01-21
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.
0
Comment
Question by:cgustaf
  • 9
  • 7
16 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 38721699
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

0
 

Author Comment

by:cgustaf
ID: 38722233
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.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 38722357
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.
0
 

Author Comment

by:cgustaf
ID: 38724614
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?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 38724745
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?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 38724749
0
 

Author Comment

by:cgustaf
ID: 38726006
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
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 38727021
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?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:cgustaf
ID: 38727497
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.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 38727550
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

0
 

Author Comment

by:cgustaf
ID: 38728258
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.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 38728369
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

0
 

Author Comment

by:cgustaf
ID: 38763840
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.
0
 

Author Comment

by:cgustaf
ID: 38776640
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?
0
 

Accepted Solution

by:
cgustaf earned 0 total points
ID: 38783411
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
0
 

Author Closing Comment

by:cgustaf
ID: 38800503
Question is now answered (by myself!)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Creating and Managing Databases with phpMyAdmin in cPanel.
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now