2. PHPRunner General questions
  3. Unable to update records
This topic is locked

Unable to update records

 		5/19/2007 6:21:53 PM
PHPRunner General questions
A
 amannerud author

Hi,
I am trying to set transaction_received to be True for all transactions within all group payments that are part of a wire transfer. For example, a client comes in and submits a payment, this is recorded in the transaction table. All the transactions for a given week are then grouped in a group payment recorded in a group payment/group payment detail table. Then all the group payments for a given month are then grouped in a wire transfer recorded in a wire transfer/wire transfer detail table.
So my idea was to place this code in the BeforeEdit event in the Wire Transfer form, i.e. if a check box for Wire Transfer Received is checked then mark all groups within the wire transfer as received, then mark all transactions within those groups as recieved.
The code I came up with was as follows, but I was not sure if I am headed in the right direction here. Do anyone have any thoughts on this particular problem? Any help would be much appreciated.
Best regards,

Tom
//List all group payments

global $conn;

$str1 = "select from wire_transfer_details where Wire_Transfer_ID='".$values["Wire_Transfer_ID"]."'";

$rs1 = db_query($str1,$conn);

$wire_transfer_details_table = db_fetch_array($rs1);
//List all transactions within those group payments

$str1 = "select from group_payment where Group_Payment_ID='".$wire_transfer_details_table["Group_Payment_ID"]."'";

$rs1 = db_query($str1,$conn);

$group_payment_details_table = db_fetch_array($rs1);
//Mark all transactions as received

$sql = "update group_payment set group_payment_received='".$values["Wire_Transfer_ID"]."' where Group_Payment_ID='".$wire_transfer_details_table["Group_Payment_ID"]."'";

db_exec($sql,$conn);
//Mark all transactions as received

$sql = "update transaction set transaction_received='".$values["Wire_Transfer_ID"]."' where Transaction_ID='".$group_payment_details_table["Transaction_ID"]."'";

db_exec($sql,$conn);
return true;
J
 		Jane 5/21/2007

Tom,
did some errors happens?

Also you can debug your code printing your queries on the page:

...

$str1 = "select from wire_transfer_details where Wire_Transfer_ID='".$values["Wire_Transfer_ID"]."'";

echo $str1;

....
//List all transactions within those group payments

$str1 = "select from group_payment where Group_Payment_ID='".$wire_transfer_details_table["Group_Payment_ID"]."'";

echo $str1;

...
A
 		amannerud author 5/21/2007

Hi Jane,
Thanks for the response. The trick with troubleshooting by using echo statements was great. However, I am still having some challenges. If you had the following two tables:
-------

Order

-------

Order_ID

Submitted
and
-------

Order_Details

-------

Order_ID

Submitted
When a user goes into the Edit "Order" form and checks the "Submitted" checkbox, how would you by using BeforeEdit event populate the "Submitted" checkbox in all the "Order_Details" entries for that particular Order?
Thanks in advance for your time and expertise.
Tom
J
 		Jane 5/22/2007

Tom,
here is a sample code for the Before record updated event:

global $conn;

if ($values["Submitted"]==True)

{

$str = "select Order_ID from Order where ".$where;

$rs = db_query($str,$conn);

while ($data = db_fetch_array($rs))

{

$str2 = "update Order_Details set Submitted=True where Order_ID=".$data["Order_ID"];

db_exec($str2,$conn);

}

}
A
 		amannerud author 5/22/2007

Thank you very much for all the help. The sample snippet worked great! You guys are rock stars!
M
 		mrpeeble 8/17/2007

I am trying to do something similar, when I edit a field in the Master table I want to update a field in a Child1 table.
When I use this code:

function BeforeEdit(&$values, $where)

{

global $conn;

if ($values["Terminated"]==101)

{

$str = "select ID from Master where ".$where;

$rs = db_query($str,$conn);

while ($data = db_fetch_array($rs))

{

$str2 = "update Child1 set Terminated=101 where MasterID=".$data["ID"];

db_exec($str2,$conn);

}

}

}

I am getting error type 256 unknown table master in where clause.

any help would be appreciated
J
 		Jane 8/17/2007

Hi,
it's difficult to tell you what's happening without seeing actual files.
Please publish your project on Demo Account and post here a URL to your pages along with instructions on reproducing this error.

I'll find what's wrong with your project inspecting it at Demo account site.
M
 		mrpeeble 8/17/2007

i uploaded project to demo account:

http://demo.asprunner.net/thomony%5Ftoront...Detail/menu.php
The error occurs when i try to edit master table.

Hi,

it's difficult to tell you what's happening without seeing actual files.
Please publish your project on Demo Account and post here a URL to your pages along with instructions on reproducing this error.

I'll find what's wrong with your project inspecting it at Demo account site.
Sergey Kornilov admin 8/17/2007

You have an extra closing curly bracket in your event. Just remove it.
M
 		mrpeeble 8/17/2007

You have an extra closing curly bracket in your event. Just remove it.


Sergey,
I uploaded my project again and checked the event code, here is a copy:
<?php

function BeforeEdit(&$values, $where)

{

global $conn;

if ($values["Terminated"]==101)

{

$str = "select ID from Master where ".$where;

$rs = db_query($str,$conn);

while ($data = db_fetch_array($rs))

{

$str2 = "update Child1 set Terminated=101 where MasterID=".$data["ID"];

db_exec($str2,$conn);

}

}

} // function BeforeEdit

?>
There are 3 opening braces and 3 closing braces. I am still getting the same error when I run project, master table not found
Sergey Kornilov admin 8/17/2007

There was an extra closing bracket in your event code. I removed it on your Demo Account and it works now.
Open include/master_events.php file in Notepad to see what I mean.
M
 		mrpeeble 8/21/2007

There was an extra closing bracket in your event code. I removed it on your Demo Account and it works now.

Open include/master_events.php file in Notepad to see what I mean.


I must be missing something, but this would not be the first time.
I just logged into demo account. In the Master table 'terminated' field I enter 101. What I get is this error:

?
Error type 256

Error description You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Terminated=101 where MasterID=2' at line 1

URL demo.asprunner.net/thomony_toronto_ca/MasterDetail/master_edit.php?

Error file C:\UserAccounts\www\thomony_toronto_ca\MasterDetail_20070817102609\include\dbconnection.php

Error line 26

SQL query update `master` set

Solution
This is a general error. It occurs when there is an error in event code or in SQL.
Send your SQL or event code along with full error message tosupport@xlinesoft.com.
J
 		Jane 8/21/2007

Hi,
this error happens because terminated is reserved word in the MySQL. You need to add backquotes to your SQL query.

Here is the correct code:

function BeforeEdit(&$values, $where)

{

global $conn;

if ($values["Terminated"]==101)

{

$str = "select ID from Master where ".$where;

$rs = db_query($str,$conn);

while ($data = db_fetch_array($rs))

{

$str2 = "update `child1` set `Terminated`='101' where `MasterID`=".$data["ID"];

db_exec($str2,$conn);

}

}

return true;

} // function BeforeEdit
M
 		mrpeeble 8/21/2007

Hi,

this error happens because terminated is reserved word in the MySQL. You need to add backquotes to your SQL query.

Here is the correct code:


Thanks Jane that fixed it!