2. PHPRunner General questions
  3. HOw to Update : SQL
This topic is locked

HOw to Update : SQL

 		5/29/2008 11:25:45 PM
PHPRunner General questions
S
 sweethard010 author

Hi... can i ask for help... thnx....

i have 1 table in my database named: BILLING.

Fields are : Due_Date, amount(Discounted), amount(Undiscounted), final_Amount_Charge, payment_Status.

i have the payment_status field SET toDiscounted by default in my database and the final_Amount_Charge is the amount coming from the amount_Discounted field if it is not beyond the Due_Date.

EX:

BILLING

==============================================================================

Due_Date.. | amount(Discounted) |amount(Undiscounted).| final_Amount_Charge .|. payment_status

=============================================================================

2008-05-05. |...........500.00............|...600.00.........................|..... 500.00 ..............| .... Discounted
=============================================================================

NOW;

is it possible in 1 (one) SQL UPDATE STATEMENT to update my Billing table by Transferring the Amount(Undiscounted) to_Final_AmountCharge if the _DueDate is beyond the Date today...??

or do you have other possible solution for this>>?? thnx again..
J
 		Jane 5/30/2008

Hi,
sure it can be done.

Use Before record updated event on the Events tab for that.

Here is a sample:

global $conn,$strTableName;

if (strtotime($values["DueDate"])-strtotime("now")>0)

{

$strUpdate = "update ".$strTableName." set final_Amount_Charge=".$values["Amount(Undiscounted)"]." where RecordID=".$keys["RecordID"];

db_exec($strUpdate,$conn);

}



where RecordID is your actual field name where ID of current record is stored.
S
 		sweethard010 author 6/2/2008

hi jane... thnx>>,. it works fine now..