Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: Multiple updates

    I also posted this at sitepoint, not sure where to look for help!
    Apologies for my lack of scripting skills. Im trying to adapt a PayPal IPN script to update several tables in one go.

    This script works fine: $strQuery2 =

    "UPDATE user_usr
    SET amt_paid_usr='".$mc_gross."'
    , paypal_usr='".$txn_id."'
    , paymentdate_usr='".$fecha."'
    WHERE invoice_usr='".$custom."'";
    "


    $result = mysql_query($strQuery2) or die("Subscription - paypal_subscription_info, Query failed:<br>" . mysql_error() . "<br>" . mysql_errno());

    When adding a second query it fails:

    "UPDATE user_usr
    SET amt_paid_usr='".$mc_gross."'
    , paypal_usr='".$txn_id."'
    , paymentdate_usr='".$fecha."'
    WHERE invoice_usr='".$custom."'";

    "UPDATE transactions
    SET amt_paid_trn='".$mc_gross."'
    , paypal_usr='".$txn_id."'
    , paymentdate_trn='".$fecha."'
    WHERE '".$item_name."'
    LIKE '%G%' AND gifter_trn='".$custom."'";



    $result = mysql_query($strQuery2) or die("Subscription - paypal_subscription_info, Query failed:<br>" . mysql_error() . "<br>" . mysql_errno());

    I also tried to enclose in quotes rather than two separate sets:

    $strQuery2 = "UPDATE user_usr
    SET amt_paid_usr='".$mc_gross."'
    , paypal_usr='".$txn_id."'
    , paymentdate_usr='".$fecha."'
    WHERE invoice_usr='".$custom."';

    UPDATE transactions
    SET amt_paid_trn='".$mc_gross."'
    , paypal_usr='".$txn_id."'
    , paymentdate_trn='".$fecha."'
    WHERE '".$item_name."' LIKE '%G%'
    AND gifter_trn='".$custom."'";


    $result = mysql_query($strQuery2) or die("Subscription - paypal_subscription_info, Query failed:<br>" . mysql_error() . "<br>" . mysql_errno());


    Thanks
    Nick

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think this is a php problem, the php interface can execute only one SQL statement at a time

    i think

    in any case you should really be looking at implementing a database transaction for this process
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    As has been mentioned in the previous post this should be split into two separate UPDATE statements and use a transaction to keep consistency. So this would be done with:

    Code:
    mysql_query("BEGIN");
    try {
      mysql_query("UPDATE ....");
      mysql_query("UPDATE ...");
      mysql_query("COMMIT");
    } catch (Exception $e) {
      mysql_query("ROLLBACK");
    }
    The try/catch has been added to make sure that if the processing fails that the transaction gets rolled back to the state it was prior to starting the transaction.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •