Paypal Ipn Doesn't Update Database

[h1F5solomon]

Hello guys.

 

I'm using the PayPal IPN to update my database when someone is buying something from my site. The payment goes through but it doesn't update my database.

 

I've tried a few solutions, like "or die(mysql_error())" and I've also been spending my last two days on google but I can't find anything.

I could really need some help with this.

 

Here's my form:

<?php
include 'core/init.php';
require 'paypal/glob.inc.php';
protect_page();
include 'includes/overall/header.php';
?>

<?php
		if (is_vip()) {
			echo 'You are already a Premium Member';
		} else {
		?>
<form action="https://www.paypal.com/cgi-bin/webscr" method="POST">
	<input type="hidden" name="cmd" value="_xclick">
	<input type="hidden" name="business" value="MY EMAIL@gmail.com">
	<input type="hidden" name="item_name" value="Premium Membership">
	<input type="hidden" name="amount" value="0.01">
	<input type="hidden" name="no_shipping" value="1">
	<input type="hidden" name="no_note" value="1">
	<input type="hidden" name="currency_code" value="EUR">
	<input type="hidden" name="lc" value="EU">
	<input type="hidden" name="bn" value="PP-BuyNowBF">
	<input type="hidden" name="return" value="http://www.PRIVATE.eu/thanks.php">
	<input type="hidden" name="cancel_return" value="http://www.PRIVATE.eu/">
	<input type="hidden" name="rm" value="2">
	<input type="hidden" name="notify_url" value="http://www.PRIVATE.eu/ipn.php" />
	<input type="hidden" name="custom" value="<?php echo $_SESSION['user_id']; ?>">
	<input type="submit" value="Upgrade to Premium" />
</form>
<?php
		}
?>


<?php include 'includes/overall/footer.php';?>

Here's my ipn.php

 

<?php
include 'core/init.php';
// 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.0\r\n";
$header .= "Content-Type: application/x-www-form-urlencoded\r\n";
$header .= "Content-Length: " . strlen($req) . "\r\n\r\n";
$fp = fsockopen ('ssl://www.paypal.com', 443, $errno, $errstr, 30); 

// Assign posted variables to local variables
$item_name = $_POST['item_name'];
$item_number = $_POST['item_number'];
$payment_status = $_POST['payment_status'];
$payment_amount = $_POST['mc_gross'];
$payment_currency = $_POST['mc_currency'];
$txn_id = $_POST['txn_id'];
$receiver_email = $_POST['receiver_email'];
$payer_email = $_POST['payer_email'];
$user_id = $_POST['custom']; // Our user's ID

if (!$fp) {
	// HTTP ERROR
} else {
fputs ($fp, $header . $req);
while(!feof($fp)) {
$res = fgets ($fps, 1024);
		
if (strcmp ($res, "VERIFIED") == 0) {
	if ($payment_status == 'Completed') { 
					
	$txn_id_check = mysql_query("SELECT `txn_id` FROM `log` WHERE `txn_id` = '".$txn_id."'");
	if (mysql_num_rows($txn_id_check) !=1) {
		if ($receiver_email == 'MYEMAIL@gmail.com') {
			if ($payment_amount == '0.01' && $payment_currency == 'EUR') {
				// add txn_id to database
				$log_query = mysql_query("INSERT INTO `log` VALUES ('','".$txn_id."','".$payer_email."') ");
						
				// update premium to 1
				$update_premium = mysql_query("UPDATE `users` SET vip = 1 WHERE `user_id` = '".$user_id."'");  
								
			}
		}
	}
} 
			
			
} else if(strcmp ($res, "INVALID") == 0) {
// Log for manual investigation
}
}
	fclose ($fp);
}
?>

I found a thread here on the forums: http://forums.phpfreaks.com/topic/269707-paypal-ipn-doesnt-update-database/

But the help that person got didn't help me.

 

I hope you can help me.

Sincerely,

Solomon

Edited July 17, 2013 by h1F5solomon

[fastsol]

The code you are using to talk with paypal is outdated.  I ran into the same problem initially.  Here is a link to the current code straight from paypal https://developer.paypal.com/webapps/developer/docs/classic/ipn/ht_ipn/ and a link to the current pdf documentation https://www.paypalobjects.com/webstatic/en_US/developer/docs/pdf/ipnguide.pdf

Do you have a sandbox account to test with at paypal?  If not you will want to sign up for that so you can do live testing of your script.  Make sure to add "sandbox." to the form action in front of the paypal.com and also in the ipn page on this line

$ch = curl_init('https://www.sandbox.paypal.com/cgi-bin/webscr');

Then once it's all working you remove the sandbox part and it will go back to normal workings on the main paypal site.  Honestly I have done a fair amount of testing with the ipn and sandbox and I still find it confusing so you may get very frustrated with this before you're done.

[h1F5solomon]

I'm very, very thankful for your help, however when it comes to this I'm by 100% totally clueless.

With the code I have right now I just followed a tutorial from phpacademy.

 

I know I probably shouldn't be working with this when not knowing enough about these stuff but in my "team" I'm working in, I'm the most experienced of us all.

 

And no, I do not have an sandbox account. I simply try the code out with 0.01 eur.

 

Edit: I've read through the first link you sent, but I still have no idea what It's saying.

Sorry for this.

Edited July 17, 2013 by h1F5solomon

[fastsol]

I know of the tutorial you watched as I was a long time member on that forum too. The code Alex uses is very outdated for paypal, hence the new stuff I linked to. Here is a reworked version using your code and the new paypal stuff.

I also added some security. Plus a couple lines of code to build a string of data for the posted vars to be inserted into the db. You'll need to add a column in your db `log` table to hold this info and set it as text type. This will allow you to see everything that paypal is sending and their according names and values.  I commented the areas in the code below.

<?php

 //reading raw POST data from input stream. reading pot data from $_POST may cause serialization issues since POST data may contain arrays
  $raw_post_data = file_get_contents('php://input');
  $raw_post_array = explode('&', $raw_post_data);
  $myPost = array();
  foreach ($raw_post_array as $keyval)
  {
      $keyval = explode ('=', $keyval);
      if (count($keyval) == 2)
         $myPost[$keyval[0]] = urldecode($keyval[1]);
  }
  // read the post from PayPal system and add 'cmd'
  $req = 'cmd=_notify-validate';
  if(function_exists('get_magic_quotes_gpc'))
  {
       $get_magic_quotes_exits = true;
  } 
  foreach ($myPost as $key => $value)
  {        
       if($get_magic_quotes_exits == true && get_magic_quotes_gpc() == 1)
       { 
            $value = urlencode(stripslashes($value)); 
       }
       else
       {
            $value = urlencode($value);
       }
       $req .= "&$key=$value";
  }
 
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, 'https://www.paypal.com/cgi-bin/webscr');
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $req);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 1);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 2);
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Host: www.paypal.com'));
// In wamp like environment where the root authority certificate doesn't comes in the bundle, you need
// to download 'cacert.pem' from "http://curl.haxx.se/docs/caextract.html" and set the directory path 
// of the certificate as shown below.
// curl_setopt($ch, CURLOPT_CAINFO, dirname(__FILE__) . '/cacert.pem');
$res = curl_exec($ch);
curl_close($ch);
 
if (strcmp ($res, "VERIFIED") == 0) {
	// check the payment_status is Completed
	// check that txn_id has not been previously processed
	// check that receiver_email is your Primary PayPal email
	// check that payment_amount/payment_currency are correct
	// process payment
	
	// Assign posted variables to local variables
    $item_name = mysql_real_escape_string($_POST['item_name']);
    $item_number = mysql_real_escape_string($_POST['item_number']);
    $payment_status = mysql_real_escape_string($_POST['payment_status']);
    $payment_amount = mysql_real_escape_string($_POST['mc_gross']);
    $payment_currency = mysql_real_escape_string($_POST['mc_currency']);
    $txn_id = mysql_real_escape_string($_POST['txn_id']);
    $receiver_email = mysql_real_escape_string($_POST['receiver_email']);
    $payer_email = mysql_real_escape_string($_POST['payer_email']);
    $user_id = (int)$_POST['custom']; // Our user's ID set to int assuming it's supposed to be a number.

	if ($payment_status == 'Completed') 
	{
		// Builds a string to insert into the db so you can see everything that has come across from paypal.
		// Pairs are separated by commas and paired key-to-value with a / forward slash
		foreach($_POST as $k => $v)
		{ $valu.= $k.' / '.$v.', '; }

    	$txn_id_check = mysql_query("SELECT `txn_id` FROM `log` WHERE `txn_id` = '".$txn_id."'");
    	if (mysql_num_rows($txn_id_check) !=1) 
    	{
    		if ($receiver_email == 'MYEMAIL@gmail.com') 
    		{
    			if ($payment_amount == '0.01' && $payment_currency == 'EUR') 
    			{
    				// add txn_id to database
    				// Add a column to hold the $valu var info
    				$log_query = mysql_query("INSERT INTO `log` VALUES ('','".$txn_id."','".$payer_email."', '".$valu."') ");
    				// update premium to 1
    				$update_premium = mysql_query("UPDATE `users` SET `vip` = 1 WHERE `user_id` = '".$user_id."'");
    			}
    		}
    	}
    }

}
else if (strcmp ($res, "INVALID") == 0) {
	// log for manual investigation
	//$db->query("INSERT INTO `".PURCHASES."` SET `test` = 'not valid response'");
}

?>

[h1F5solomon]

Wow man. Visit me in Sweden and I'll take you out for some beers. I will also give you a true russian bear-hug.

Last spoon-feeding question: What field should I actually put inside the 'log' db? I mean, what should I call it.

 

I get the security fixes, but I don't get the $valu variable

I'd be very happy if you could explain that variable for me

 

Edit: Okay, I added a field called "info" in the "log" db and it works perfectly.

I don't know how to thank you mate. I'm so very happy right now.

THANK YOU

Edited July 17, 2013 by h1F5solomon

[fastsol]

Great, glad it worked right away for you. The $valu thing is really just for diagnostics during testing, development and for future diagnostics if you find consistent errors coming across, you can look at what was sent everytime by paypal and diagnose from there. I even have a testing script i use when making complex stuff for the ipn cause you can't get error feedback when the ipn script is live.

[h1F5solomon]

Thanks again mate.

 

Instead of making a new thread I'll just post a small problem here instead.

on the site I have a profile for each member and I also have a textfield saying "Rank", which of course is gonna display which rank the person are.

 

Me, who is an admin it should say "Rank: Admin" and for someone who's Premium it should say "Rank: Premium", and for a normal user it says "Rank: Standard".

Since I have to grab the information from the Database to specify each person I need to use this:

<?php echo $profile_data['type']; ?>

This specifies if you're an admin or not. 1  = admin, 0 = Non admin.

If I just type it like this: "Rank: <?php CODE ?>" it will display the numbers and not the text itself.

How do I fix it, so it display text instead of numbers?

 

It's the same code for premium, but instead of "type" you put premium.

 

Here's the $profile_data:

$profile_data	= user_data($user_id, 'user_id', 'first_name', 'last_name', 'email', 'username', 'user_age', 'user_from', 'imagelocation', 'type', 'vip');

Edited July 18, 2013 by h1F5solomon

[fastsol]

How are you distinguishing between the 3 ranks, your db is only set to be 1 or 0 for the `type`, that would only work for 2 ranks unless `vip` is for premium?

[h1F5solomon]

How are you distinguishing between the 3 ranks, your db is only set to be 1 or 0 for the `type`, that would only work for 2 ranks unless `vip` is for premium?

 

Yes indeed. Vip = Premium.

 

Edit: In PM you told me to mark it as resolved, but I don't have an Edit button on my first post O.o

Edited July 18, 2013 by h1F5solomon

[fastsol]

Here is your rank code.

echo ($profile_data['vip'] == 1) ? 'Premium' : (($profile_data['type'] == 1) ? 'Admin' : 'Standard');

As for setting the topic as solved, I don't know for sure cause I have never started a topic on here, only answered them.  From what I can tell there should be a button under each reply that allows you to mark it as "Answered", you're suppose to choose the reply that best answered the topic issue, beyond that I couldn't tell ya what to do.

[h1F5solomon]

Done and done! Thank you again mate.