Jump to content

SQL integration with PayPal


MarkT

Recommended Posts

Hello,

I want to create a paypal button, that lets people pay for stuff via paypal, I want it to send it to a paypal acc, use a listener, and if it's a verified payment, execute an SQL statement.

 

Anyone have any ideas how to go about this?

Link to comment
Share on other sites

Hello MarkT,

 

Interesting you bring this up because the past a couple of weeks, I've been experimenting with exactly what you mentioned with PayPal with a project I'm currently working on. I'll try to summarize:To have a listener known as in paypal which is called: instant payment notification(ipn), you're going to have to upgrade(or get) to a business account. You'd go to 'Profile' -> My Selling Tools and then look for 'instant payment notification' and click on 'update' to set the info for that. You're going to have to insert a url that points to a script on your site where the info regarding the most recent payment will be sent to from paypal letting you know if it's 'VERIFIED' or 'INVALID'. Then from there, you'd update your database for that user indicating they have paid if the return status is 'VERIFIED'. Update: If 'VERIFIED' is returned, it only means that the transaction was a 'VERIFIED' transaction. In other words, it means a valid transaction took place BUT does NOT MEAN the payment_status is good. You must check the payment_status in $_POST['payment_user'] to determine if the payment was successful or not.

 

The reason you need a business account to do this is because either they changed it or something else happened, but I noticed in the 'Personal' account, they don't have the 'instant payment notification' option anymore. I believe along time ago they did. Upgrading to business account is free, but the only thing is, per transaction, they will take like 30 cents from it I believe.

 

The good news is, paypal has a 'sandbox' site where you can use to test everything out before you go 'live'. You will need to go to the developer site(developer.paypal.com; can login in using your current actual paypal account) to create the sandbox accounts. There you can create 'seller' or/and 'buyer' accounts. You'd then go to the sandbox site: https://www.sandbox.paypal.com per which ever sandbox account you created to set up the account as you would do on a real account. So for example, you'd log into the seller account and create the button for the item you want to sell. Then you'd go and setup the instant payment notification settings as mentioned above for that pretend seller account. You would then paste the 'Buy Now' button code paypal supplies to you upon creating your button onto your site. To test the button, you'd go to your site (by the way, this can't be done on a local site, has to be live. Best to create a 'test' subdomain perhaps for all this to test everything etc) and click on the button to buynow, paypal will then redirect to their site to complete transaction. As that happens, you will enter the email and password you created for the 'buyer' account you created at the sandbox site. Once you log in, review what you're about to purchase, click 'pay'. As this happens, paypal will send information regarding this transaction to the 'instant payment notification' url you provided for the seller account. You will check to see if the payment is 'VERIFIED', if so, all is well, update database indicating user has paid, etc. (See above 'Update') Below is my 'testing' ipn script I currently have going. It uses cURL(recommended) to communicate with paypal for the transaction:

<?phpclass Paypal_IPN{   private $_url;       public function __construct($mode = 'live')   {        if ($mode == 'live')		{        	$this->_url = 'https://www.paypal.com/cgi-bin/webscr';		}		else		{        	$this->_url = 'https://www.sandbox.paypal.com/cgi-bin/webscr';		}    }    public function run()    {        $postFields = 'cmd=_notify-validate';        foreach($_POST as $key => $value)        {            $postFields .= "&$key=".urlencode($value);        }        $ch = curl_init();		        curl_setopt_array($ch, array(            CURLOPT_URL => $this->_url,	    CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,            CURLOPT_RETURNTRANSFER => true,            CURLOPT_SSL_VERIFYPEER => false,            CURLOPT_POST => true,            CURLOPT_POSTFIELDS => $postFields,	    CURLOPT_HEADER => true,			        ));        $result = curl_exec($ch);        curl_close($ch);		if(strpos($result, 'VERIFIED') != FALSE)		{			//this is just to see what is being outputted but most likely this is where you'll want to update database if payment was 'VERIFIED'                         // see above 'Update'; VERIFIED only means there was a valid transaction and NOT                        // if the payment was successful or not.  			file_put_contents('result.txt', $result . ' - ' .  var_export($_POST, true));		}    }}$paypal= new Paypal_IPN('sandbox');$paypal->run();?>

This is what an output looks like for the transaction in the result.txt file:

HTTP/1.1 100 ContinueHTTP/1.1 200 OKDate: Sun, 23 Mar 2014 04:36:08 GMTServer: ApacheX-Frame-Options: SAMEORIGINSet-Cookie: c9MWDuvPtT9GIMyPc3jwol1VSlO=t9cdRlHmh810vJqbRP414pqaQg7Y8XgwzDjtEp8HtQXHNMU7BrDjs1miWA0RMHBAHZ1gGhvI-RklC1JSWfK2cVz3Mx1jGyxhEJehjxroFMXIro9CtpKJJxvvtSstV3dSm-aI88cK4_6K60gqC0dbi-zHtvWSV4-E8s1sWpt5NcLTnIfeEGutWWuUTsEZbGI8Wx1_Nix_GaN16djn00Vdyx1VJXHnMKjhiKQZ9vseF8XkpDKJvkSry3IRy80C3aEMn3DqAIJOcTwP2YbdFXT1Gg6YAe26oHHdE9BfhnGvGG5Q7IdqXGiklTfG5ShD1kZFB14OiOy7YFDlXRPjP_LLf-KkWPvhMp5k_WzADSL5IP93RO4fTbAn6gvh2pHzOcYh4DRWBF9Ii8yprM3w4hGNw-j5YZkdyXICuQYSnE40mKaB0pZJHoGfbu8QWV4; domain=.paypal.com; path=/; Secure; HttpOnlySet-Cookie: cookie_check=yes; expires=Wed, 20-Mar-2024 04:36:08 GMT; domain=.paypal.com; path=/; Secure; HttpOnlySet-Cookie: navcmd=_notify-validate; domain=.paypal.com; path=/; Secure; HttpOnlySet-Cookie: navlns=0.0; expires=Tue, 22-Mar-2016 04:36:08 GMT; domain=.paypal.com; path=/; Secure; HttpOnlySet-Cookie: Apache=10.72.109.11.1395549368157218; path=/; expires=Tue, 15-Mar-44 04:36:08 GMTX-Cnection: closeSet-Cookie: X-PP-SILOVER=name%3DSANDBOX3.WEB.1%26silo_version%3D880%26app%3Dslingshot%26TIME%3D3093573203; domain=.paypal.com; path=/; Secure; HttpOnlySet-Cookie: X-PP-SILOVER=; Expires=Thu, 01 Jan 1970 00:00:01 GMTSet-Cookie: Apache=10.72.128.11.1395549368142336; path=/; expires=Tue, 15-Mar-44 04:36:08 GMTVary: Accept-EncodingStrict-Transport-Security: max-age=14400Transfer-Encoding: chunkedContent-Type: text/html; charset=UTF-8VERIFIED - array (  'mc_gross' => '10.00',  'protection_eligibility' => 'Eligible',  'address_status' => 'confirmed',  'payer_id' => '77FWLVRQMKQTU',  'tax' => '0.00',  'address_street' => '1 Main St',  'payment_date' => '21:35:54 Mar 22, 2014 PDT',  'payment_status' => 'Completed',  'charset' => 'windows-1252',  'address_zip' => '95131',  'first_name' => 'Bruce',  'mc_fee' => '0.59',  'address_country_code' => 'US',  'address_name' => 'Bruce Wayne',  'notify_version' => '3.7',  'custom' => '',  'payer_status' => 'verified',  'business' => 'd.nu-facilitator@gmail.com',  'address_country' => 'United States',  'address_city' => 'San Jose',  'quantity' => '1',  'verify_sign' => 'ABr7TF1VNJRHrFfJkVMfCcSa87ETA-vHI9gkqlaorJcID4GyrIyuZUoN',  'payer_email' => 'whocares_sowhat@yahoo.com',  'txn_id' => '9J104269XA701392G',  'payment_type' => 'instant',  'btn_id' => '2923468',  'last_name' => 'Wayne',  'address_state' => 'CA',  'receiver_email' => 'd.nu-facilitator@gmail.com',  'payment_fee' => '0.59',  'shipping_discount' => '0.00',  'insurance_amount' => '0.00',  'receiver_id' => 'FGA7J2A7XH3BA',  'txn_type' => 'web_accept',  'item_name' => 'Product',  'discount' => '0.00',  'mc_currency' => 'USD',  'item_number' => '',  'residence_country' => 'US',  'test_ipn' => '1',  'shipping_method' => 'Default',  'handling_amount' => '0.00',  'transaction_subject' => '',  'payment_gross' => '10.00',  'shipping' => '0.00',  'ipn_track_id' => 'd4b4a4bf77bb5',)

As you can see, it's 'VERIFIED' meaning payment was good. Payment_status determines if a payment was good or not. There you can also see all the info. You can see what's returned from curl_exec() in the $result which is where you'll check to see if it's 'VERIFIED' or 'INVALID'. For the sake of seeing what paypal POSTs back to your ipn file, I var_export $_POST to get an idea of the info (See above). All of that can be accessed like this for example: $_POST['first_name'], $_POST['shipping'] etc etc if you wish to do that to use certain info upon updating database table per transaction per user, etc.

 

The script above is a class, you don't necessarily need to have it as a class but I decided to go with that from a tutorial I came across on the net. A couple of things to remember: On the 'live' site, since payal is posting back to your site, it is required for you to have a SSL certificate. For that, you're going to have to add to the cURL options like the following:

$ch = curl_init();		        curl_setopt_array($ch, array(            CURLOPT_URL => $this->_url,	    CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,            CURLOPT_RETURNTRANSFER => true,            CURLOPT_SSL_VERIFYPEER => true, //this set to true	    CURLOPT_SSL_VERIFYHOST => 2, // this set            CURLOPT_CAINFO => "api_cert_chain.crt", // path to your SSL certicate here            CURLOPT_POST => true,            CURLOPT_POSTFIELDS => $postFields,	    CURLOPT_HEADER => true,			        ));

Make sure your port number 443 is open which most likely it is.

 

Another way to test the ipn script is by going to paypal sandbox site and going to the ipn simulator. It's a way to test your ipn script without having to make a buynow button, then going to the site clicking on it, etc.

 

Well hopefully this was of some use to you. Good luck! :good:Edited: see 'Update'.

Edited by Don E
  • Like 1
Link to comment
Share on other sites

Thanks Don,

How would you use variables from the;

VERIFIED - array ('mc_gross' => '10.00','protection_eligibility' => 'Eligible','address_status' => 'confirmed','payer_id' => '77FWLVRQMKQTU','tax' => '0.00','address_street' => '1 Main St','payment_date' => '21:35:54 Mar 22, 2014 PDT','payment_status' => 'Completed','charset' => 'windows-1252','address_zip' => '95131','first_name' => 'Bruce','mc_fee' => '0.59','address_country_code' => 'US','address_name' => 'Bruce Wayne','notify_version' => '3.7','custom' => '','payer_status' => 'verified','business' => 'd.nu-facilitator@gmail.com','address_country' => 'United States','address_city' => 'San Jose','quantity' => '1','verify_sign' => 'ABr7TF1VNJRHrFfJkVMfCcSa87ETA-vHI9gkqlaorJcID4GyrIyuZUoN','payer_email' => 'whocares_sowhat@yahoo.com','txn_id' => '9J104269XA701392G','payment_type' => 'instant','btn_id' => '2923468','last_name' => 'Wayne','address_state' => 'CA','receiver_email' => 'd.nu-facilitator@gmail.com','payment_fee' => '0.59','shipping_discount' => '0.00','insurance_amount' => '0.00','receiver_id' => 'FGA7J2A7XH3BA','txn_type' => 'web_accept','item_name' => 'Product','discount' => '0.00','mc_currency' => 'USD','item_number' => '','residence_country' => 'US','test_ipn' => '1','shipping_method' => 'Default','handling_amount' => '0.00','transaction_subject' => '','payment_gross' => '10.00','shipping' => '0.00','ipn_track_id' => 'd4b4a4bf77bb5',)

 

In an SQL query, for the if validated?

Link to comment
Share on other sites

You would use them as you would as you would from a regular form POST. For example: 'payer_status' => 'verified', would be $_POST['payer_status'] and 'payment_status' => 'Completed', would be $_POST['payment_status'];

 

So if you have a column in your database table that keeps track of each users status on payment, for example the column could be called 'payment_status' and it's fault value could be NULL or whatever you want to give to indicate they haven't paid yet. Then once they do pay and you get a 'VERIFIED' result like in the above script, you update that record for that user 'payment_status' to $_POST['payment_status'] which would be 'Completed'.

  • Like 1
Link to comment
Share on other sites

You would use them as you would as you would from a regular form POST. For example: 'payer_status' => 'verified', would be $_POST['payer_status'] and 'payment_status' => 'Completed', would be $_POST['payment_status'];

 

So if you have a column in your database table that keeps track of each users status on payment, for example the column could be called 'payment_status' and it's fault value could be NULL or whatever you want to give to indicate they haven't paid yet. Then once they do pay and you get a 'VERIFIED' result like in the above script, you update that record for that user 'payment_status' to $_POST['payment_status'] which would be 'Completed'.

Thanks.

 

Like.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...