2. PHPRunner General questions
  3. [SOLVED]  How to Update MySQL field value by adding another value
This topic is locked
[SOLVED]

 How to Update MySQL field value by adding another value

8/18/2011 5:48:33 AM
PHPRunner General questions
M
 Mel2011 author

Hello and thanks in advance for any help I can get.
I have 2 tables where:

  • Table 1: Payments
  • Table 2: customers
    what code should I use in the after record updated event?
    Table 1 (payments) fields:
  • customer_id
  • PaymentAmount
    Table 2 (customers) fields:
  • customer_id
  • Balance
    example:

    payment amount: 10

    customer_id: 2

    current customer balance: 17

    new customer balance: 27
    I cannot just sum the 2 amounts because while the payment form is being filled up, there could be other charges to the customer and the balance value would change.
    Thanks in advance for any help.
C
 		cgphp 8/18/2011

If you want to prevent simultaneous update of the same data you can use record locking: http://xlinesoft.com/phprunner/docs/audit.htm
M
 		Mel2011 author 8/18/2011



If you want to prevent simultaneous update of the same data you can use record locking: http://xlinesoft.com/phprunner/docs/audit.htm



Thanks Christian. But this doesn't help. I cannot lock the records because it's a telephony server. I was testing earlier to add a trigger to the MySQL payments database to update the customers balance field. I'm very familiar with Access and slightly with MySQL. However, my knowledge is very limited with PHP and MySQL triggers.
Admin 8/19/2011

Here is the sample code:

CustomQuery("update customers set balance=balance+" . $values["PaymentAmount"] . " where customer_id = " . $values["customer_id"]);


It's totally safe to run two concurrent updates at the same time. Both of them will work - MySQL takes care of this.
M
 		Mel2011 author 8/19/2011



Here is the sample code:

CustomQuery("update customers set balance=balance+" . $values["PaymentAmount"] . " where customer_id = " . $values["customer_id"]);


It's totally safe to run two concurrent updates at the same time. Both of them will work - MySQL takes care of this.



Thanks Sergey. I will try the syntax.