Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-12-26
16
Medium Priority
?
380 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
16 Comments
 
LVL 60

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 60

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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

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 60

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 60

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 60

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
 

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 60

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 60

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

705 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