2. PHPRunner General questions
  3. Updating records
This topic is locked

Updating records

 		8/20/2007 5:49:07 PM
PHPRunner General questions
H
 horsey_kim author

I have two tables one for customer data and one for product data. Due to the fact that my customers want to be able to download the product data and use it in excell along with the customer info for each product. I need to have same data in some of the customer records repeat also in the product records.
Each customer data (invoice) has an invoice number. I plan on having each of the products ordered (they are unique products) to have the invoice number and some of the needed customer data in that same table. But if my customer changes somethin gin the customer data I would like it to search out and update the data in the product table that is tied to that same invoice number.
I started looking at events and I see "after record updated" and "List page: After record processed" that I can "insert record into another table". But when I tried this before it just added a new record. I want it to search for all records that have that invoice number and change the particular fields in product table to match that of customer table.
Would I need to do something like "check if specific record exist and insert a record into another table?"
I am pretty new to all this so please any help is appreciated,
Kim
J
 		Jane 8/21/2007

Kim,
to change data in the products table use Before record updated event for the customers table.

Here is a sample code:

global $conn;

$strUpdate = "update products set ProductFieldName1 = ".$values["CustomerFieldName1"].", ProductFieldName2='".$values["CustomerFieldName2"]."' where ProductInvoiceField=".$values["CustomerInvoiceField"];

$db_exec($strUpdate,$conn);



where ProductFieldName1, ProductFieldName2 and ProductInvoiceField are your actual field names in the products table, CustomerFieldName1, CustomerFieldName2 and CustomerInvoiceField are your actual field names in the customers table.

ProductFieldName1 is numeric field, ProductFieldName2 is text field (need quotes around it).
H
 		horsey_kim author 8/28/2007

Thanks Jane - will try it out!
Your help is so appreciated - Your awesome!
Kim <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=20926&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
H
 		horsey_kim author 9/5/2007

Jane or who ever can help <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=21152&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
I tried this code and I get an error "Undefined variable: db_exec"
I have placed the custom code in the before record update:
global $conn;

$strUpdate = "update _seeddata set billname = '".$values["billto_name"]."', billaddress='".$values["billto_address"]."' where blueid2=".$values["blueid"];

$db_exec($strUpdate,$conn);
Please note blueid2=".$values["blueid"]; I also tried it as blue ide2=".$values[blueid']."; I thought that might be the problem and it errors out the script also.
Note that _seeddata is the product table and I have the code in the screen for the customer table which I call _blue
Alexey admin 9/6/2007

Hi,
try this code:

global $conn;

$strUpdate = "update _seeddata set billname = '".$values["billto_name"]."', billaddress='".$values["billto_address"]."' where blueid2=".$values["blueid"];

db_exec($strUpdate,$conn);