Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Feb 2013
    Posts
    14
    Thanks
    3
    Thanked 0 Times in 0 Posts

    IPN to mysql database help

    Hi,

    I think I've done everything correct to allow IPN on paypal to add the customers details etc to my mysql database.
    I'm using code that was on the internet and when I do a test purchase I get "sent" message from paypals IPN which is good.
    But it's just not updating my database
    I have the tables created "paypal_payment_info" & "paypal_cart_info"

    I am doing this live and not using sandbox.

    Any idea's? Thanks

    PHP Code:
    <?php

    // Revision Notes
    // 11/04/11 - changed post back url from https://www.paypal.com/cgi-bin/webscr to https://ipnpb.paypal.com/cgi-bin/webscr
    // For more info see below:
    // https://www.x.com/content/bulletin-ip-address-expansion-paypal-services
    // "ACTION REQUIRED: if you are using IPN (Instant Payment Notification) for Order Management and your IPN listener script is behind a firewall that uses ACL (Access Control List) rules which restrict outbound traffic to a limited number of IP addresses, then you may need to do one of the following: 
    // To continue posting back to https://www.paypal.com  to perform IPN validation you will need to update your firewall ACL to allow outbound access to *any* IP address for the servers that host your IPN script
    // OR Alternatively, you will need to modify  your IPN script to post back IPNs to the newly created URL https://ipnpb.paypal.com using HTTPS (port 443) and update firewall ACL rules to allow outbound access to the ipnpb.paypal.com IP ranges (see end of message)."


    /////////////////////////////////////////////////
    /////////////Begin Script below./////////////////
    /////////////////////////////////////////////////

    // read the post from PayPal system and add 'cmd'
    $req 'cmd=_notify-validate';
    foreach (
    $_POST as $key => $value) {
    $value urlencode(stripslashes($value));
    $req .= "&$key=$value";
    }
    // post back to PayPal system to validate
    $header "POST /cgi-bin/webscr HTTP/1.1\r\n";
    $header .= "Content-Type: application/x-www-form-urlencoded\r\n";
    $header .= "Content-Length: " strlen($req) . "\r\n\r\n";

    // If testing on Sandbox use:
    //$fp = fsockopen ('ssl://www.sandbox.paypal.com', 443, $errno, $errstr, 30);

    $fp fsockopen ('ssl://ipnpb.paypal.com'443$errno$errstr30);


    // assign posted variables to local variables
    $item_name $_POST['item_name'];
    $business $_POST['business'];
    $item_number $_POST['item_number'];
    $payment_status $_POST['payment_status'];
    $mc_gross $_POST['mc_gross'];
    $payment_currency $_POST['mc_currency'];
    $txn_id $_POST['txn_id'];
    $receiver_email $_POST['receiver_email'];
    $receiver_id $_POST['receiver_id'];
    $quantity $_POST['quantity'];
    $num_cart_items $_POST['num_cart_items'];
    $payment_date $_POST['payment_date'];
    $first_name $_POST['first_name'];
    $last_name $_POST['last_name'];
    $payment_type $_POST['payment_type'];
    $payment_status $_POST['payment_status'];
    $payment_gross $_POST['payment_gross'];
    $payment_fee $_POST['payment_fee'];
    $settle_amount $_POST['settle_amount'];
    $memo $_POST['memo'];
    $payer_email $_POST['payer_email'];
    $txn_type $_POST['txn_type'];
    $payer_status $_POST['payer_status'];
    $address_street $_POST['address_street'];
    $address_city $_POST['address_city'];
    $address_state $_POST['address_state'];
    $address_zip $_POST['address_zip'];
    $address_country $_POST['address_country'];
    $address_status $_POST['address_status'];
    $item_number $_POST['item_number'];
    $tax $_POST['tax'];
    $option_name1 $_POST['option_name1'];
    $option_selection1 $_POST['option_selection1'];
    $option_name2 $_POST['option_name2'];
    $option_selection2 $_POST['option_selection2'];
    $for_auction $_POST['for_auction'];
    $invoice $_POST['invoice'];
    $custom $_POST['custom'];
    $notify_version $_POST['notify_version'];
    $verify_sign $_POST['verify_sign'];
    $payer_business_name $_POST['payer_business_name'];
    $payer_id =$_POST['payer_id'];
    $mc_currency $_POST['mc_currency'];
    $mc_fee $_POST['mc_fee'];
    $exchange_rate $_POST['exchange_rate'];
    $settle_currency  $_POST['settle_currency'];
    $parent_txn_id  $_POST['parent_txn_id'];
    $pending_reason $_POST['pending_reason'];
    $reason_code $_POST['reason_code'];


    // subscription specific vars

    $subscr_id $_POST['subscr_id'];
    $subscr_date $_POST['subscr_date'];
    $subscr_effective  $_POST['subscr_effective'];
    $period1 $_POST['period1'];
    $period2 $_POST['period2'];
    $period3 $_POST['period3'];
    $amount1 $_POST['amount1'];
    $amount2 $_POST['amount2'];
    $amount3 $_POST['amount3'];
    $mc_amount1 $_POST['mc_amount1'];
    $mc_amount2 $_POST['mc_amount2'];
    $mc_amount3 $_POST['mcamount3'];
    $recurring $_POST['recurring'];
    $reattempt $_POST['reattempt'];
    $retry_at $_POST['retry_at'];
    $recur_times $_POST['recur_times'];
    $username $_POST['username'];
    $password $_POST['password'];

    //auction specific vars

    $for_auction $_POST['for_auction'];
    $auction_closing_date  $_POST['auction_closing_date'];
    $auction_multi_item  $_POST['auction_multi_item'];
    $auction_buyer_id  $_POST['auction_buyer_id'];



    //DB connect creds and email 
    $notify_email =  "xxxxx@hotmail.co.uk";         //email address to which debug emails are sent to
    $DB_Server "213.171.xxx.xxxx"//your MySQL Server
    $DB_Username "my_username"//your MySQL User Name
    $DB_Password "xxxxx"//your MySQL Password
    $DB_DBName "xxxxxx"//your MySQL Database Name


    if (!$fp) {
    // HTTP ERROR
    } else {
    fputs ($fp$header $req);
    while (!
    feof($fp)) {
    $res fgets ($fp1024);
    if (
    strcmp ($res"VERIFIED") == 0) {



    //create MySQL connection
    $Connect = @mysql_connect($DB_Server$DB_Username$DB_Password)
    or die(
    "Couldn't connect to MySQL:<br>" mysql_error() . "<br>" mysql_errno());


    //select database
    $Db = @mysql_select_db($DB_DBName$Connect)
    or die(
    "Couldn't select database:<br>" mysql_error(). "<br>" mysql_errno());


    $fecha date("m")."/".date("d")."/".date("Y");
    $fecha date("Y").date("m").date("d");

    //check if transaction ID has been processed before
    $checkquery "select txnid from paypal_payment_info where txnid='".$txn_id."'";
    $sihay mysql_query($checkquery) or die("Duplicate txn id check query failed:<br>" mysql_error() . "<br>" mysql_errno());
    $nm mysql_num_rows($sihay);
    if (
    $nm == 0){

    //execute query



        
    if ($txn_type == "cart"){
        
    $strQuery "insert into paypal_payment_info(paymentstatus,buyer_email,firstname,lastname,street,city,state,zipcode,country,mc_gross,mc_fee,memo,paymenttype,paymentdate,txnid,pendingreason,reasoncode,tax,datecreation) values ('".$payment_status."','".$payer_email."','".$first_name."','".$last_name."','".$address_street."','".$address_city."','".$address_state."','".$address_zip."','".$address_country."','".$mc_gross."','".$mc_fee."','".$memo."','".$payment_type."','".$payment_date."','".$txn_id."','".$pending_reason."','".$reason_code."','".$tax."','".$fecha."')";

         
    $result mysql_query($strQuery) or die("Cart - paypal_payment_info, Query failed:<br>" mysql_error() . "<br>" mysql_errno());
         for (
    $i 1$i <= $num_cart_items$i++) {
             
    $itemname "item_name".$i;
             
    $itemnumber "item_number".$i;
             
    $on0 "option_name1_".$i;
             
    $os0 "option_selection1_".$i;
             
    $on1 "option_name2_".$i;
             
    $os1 "option_selection2_".$i;
             
    $quantity "quantity".$i;

             
    $struery "insert into paypal_cart_info(txnid,itemnumber,itemname,os0,on0,os1,on1,quantity,invoice,custom) values ('".$txn_id."','".$_POST[$itemnumber]."','".$_POST[$itemname]."','".$_POST[$on0]."','".$_POST[$os0]."','".$_POST[$on1]."','".$_POST[$os1]."','".$_POST[$quantity]."','".$invoice."','".$custom."')";
             
    $result mysql_query($struery) or die("Cart - paypal_cart_info, Query failed:<br>" mysql_error() . "<br>" mysql_errno());

         }
        }



        else{
         
    $strQuery "insert into paypal_payment_info(paymentstatus,buyer_email,firstname,lastname,street,city,state,zipcode,country,mc_gross,mc_fee,itemnumber,itemname,os0,on0,os1,on1,quantity,memo,paymenttype,paymentdate,txnid,pendingreason,reasoncode,tax,datecreation) values ('".$payment_status."','".$payer_email."','".$first_name."','".$last_name."','".$address_street."','".$address_city."','".$address_state."','".$address_zip."','".$address_country."','".$mc_gross."','".$mc_fee."','".$item_number."','".$item_name."','".$option_name1."','".$option_selection1."','".$option_name2."','".$option_selection2."','".$quantity."','".$memo."','".$payment_type."','".$payment_date."','".$txn_id."','".$pending_reason."','".$reason_code."','".$tax."','".$fecha."')";
         
    $result mysql_query("insert into paypal_payment_info(paymentstatus,buyer_email,firstname,lastname,street,city,state,zipcode,country,mc_gross,mc_fee,itemnumber,itemname,os0,on0,os1,on1,quantity,memo,paymenttype,paymentdate,txnid,pendingreason,reasoncode,tax,datecreation) values ('".$payment_status."','".$payer_email."','".$first_name."','".$last_name."','".$address_street."','".$address_city."','".$address_state."','".$address_zip."','".$address_country."','".$mc_gross."','".$mc_fee."','".$item_number."','".$item_name."','".$option_name1."','".$option_selection1."','".$option_name2."','".$option_selection2."','".$quantity."','".$memo."','".$payment_type."','".$payment_date."','".$txn_id."','".$pending_reason."','".$reason_code."','".$tax."','".$fecha."')") or die("Default - paypal_payment_info, Query failed:<br>" mysql_error() . "<br>" mysql_errno());
        }


        
    // send an email in any case
     
    echo "Verified";
         
    mail($notify_email"VERIFIED IPN""$res\n $req\n $strQuery\n $struery\n  $strQuery2");
    }
    else {
    // send an email
    mail($notify_email"VERIFIED DUPLICATED TRANSACTION""$res\n $req \n $strQuery\n $struery\n  $strQuery2");
    }

        
    //subscription handling branch
        
    if ( $txn_type == "subscr_signup"  ||  $txn_type == "subscr_payment"  ) {

          
    // insert subscriber payment info into paypal_payment_info table
          
    $strQuery "insert into paypal_payment_info(paymentstatus,buyer_email,firstname,lastname,street,city,state,zipcode,country,mc_gross,mc_fee,memo,paymenttype,paymentdate,txnid,pendingreason,reasoncode,tax,datecreation) values ('".$payment_status."','".$payer_email."','".$first_name."','".$last_name."','".$address_street."','".$address_city."','".$address_state."','".$address_zip."','".$address_country."','".$mc_gross."','".$mc_fee."','".$memo."','".$payment_type."','".$payment_date."','".$txn_id."','".$pending_reason."','".$reason_code."','".$tax."','".$fecha."')";
          
    $result mysql_query($strQuery) or die("Subscription - paypal_payment_info, Query failed:<br>" mysql_error() . "<br>" mysql_errno());


             
    // insert subscriber info into paypal_subscription_info table
            
    $strQuery2 "insert into paypal_subscription_info(subscr_id , sub_event, subscr_date ,subscr_effective,period1,period2, period3, amount1 ,amount2 ,amount3,  mc_amount1,  mc_amount2,  mc_amount3, recurring, reattempt,retry_at, recur_times, username ,password, payment_txn_id, subscriber_emailaddress, datecreation) values ('".$subscr_id."', '".$txn_type."','".$subscr_date."','".$subscr_effective."','".$period1."','".$period2."','".$period3."','".$amount1."','".$amount2."','".$amount3."','".$mc_amount1."','".$mc_amount2."','".$mc_amount3."','".$recurring."','".$reattempt."','".$retry_at."','".$recur_times."','".$username."','".$password."', '".$txn_id."','".$payer_email."','".$fecha."')";
            
    $result mysql_query($strQuery2) or die("Subscription - paypal_subscription_info, Query failed:<br>" mysql_error() . "<br>" mysql_errno());


                 
    mail($notify_email"VERIFIED IPN""$res\n $req\n $strQuery\n $struery\n  $strQuery2");

        }
    }

    // if the IPN POST was 'INVALID'...do this


    else if (strcmp ($res"INVALID") == 0) {
    // log for manual investigation

    mail($notify_email"INVALID IPN""$res\n $req");
    }
    }
    fclose ($fp);
    }
    ?>


    </body>
    </html>

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    I don't see how this has anything, directly, to do with MySQL.

    I think it's entirely an issue with how to work with PayPal from PHP code.

    Somebody correct me if I'm wrong.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,623
    Thanks
    0
    Thanked 645 Times in 635 Posts
    You are not going to be able to see any of the SQL error messages with the way that you are simply outputting them to nowhere (since the script doesn't have a screen attached to display them on. Perhaps to aid in the debugging you should have the error messages insert ed into the body of the email being sent so that you can see them - at least until you figure out what is wrong.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #4
    New Coder
    Join Date
    Feb 2013
    Posts
    14
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks felgall

    I will give that a go.

    Thanks,

    P.S. sorry if this is in wrong location, mydatabase is mysql so I guessed this would be the place. sorry.


  •  

    Posting Permissions

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