Paypal Ipn: Database Orders Table Not Updating

[peppericious1]

I'm doing an application whereby parents will be registering their kids in an orchestra. The cost of registration is a sliding cost if there's more than just one kid in a given family registering for the orchestra. So, 1st kid 100 euros, 2nd kid 80 euros, 3rd and subsequent kids 60 euros, etc.

 

The cost of registration is calculated in advance, before the registrant gets to the page with the paypal 'Pay Now' button, and is stored in a 'fee' session variable. I'm then passing the value of $_SESSION['fee'] to the paypal button... or at least that is my intention.

 

My ipn.php script - adapted from ch. 6 of Larry's book - is this:

 

<?php
session_start();
$req = 'cmd=_notify-validate';
// Add each received key=value pair to the request:
foreach ($_POST as $key => $value) {
$value = urlencode(stripslashes($value));
$req .= "&$key=$value";
}
// Open a socket connection to PayPal:
$fp = fsockopen ('ssl://www.sandbox.paypal.com', 443, $errno, $errstr, 30); // Test
//$fp = fsockopen ('ssl://www.paypal.com', 443, $errno, $errstr, 30); // Live
if (!$fp) { // If we couldn't connect, send an email:
trigger_error('Could not connect for the IPN!');
} else { // Send the request to PayPal:
$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";
fputs ($fp, $header . $req);
// Read in the response:
while (!feof($fp)) {
 $res = fgets ($fp, 1024);
 if (strcmp ($res, "VERIFIED") == 0) {  
  // Check for the right values:
  if ( isset($_POST['payment_status'])
&& ($_POST['payment_status'] == 'Completed')
&& ($_POST['receiver_email'] == 'hugh.n_1327507957_biz@gmail.com')
&& ($_POST['mc_gross'] == $_SESSION['fee'])
&& ($_POST['mc_currency']  == 'EUR')
&& (!empty($_POST['txn_id']))
  ) {  
// Need the database connection now:
include('includes/mysqli_connect.php');  
// Check for this transaction in the database:
$txn_id = mysqli_real_escape_string($dbc, $_POST['txn_id']);  
$q = "SELECT id FROM orders WHERE transaction_id='$txn_id'";
$r = mysqli_query ($dbc, $q);
if (mysqli_num_rows($r) == 0) { // Add this new transaction:  
 $uid = (isset($_POST['custom'])) ? (int) $_POST['custom'] : 0;
 $status = mysqli_real_escape_string($dbc, $_POST['payment_status']);
 $amount = (float) $_POST['mc_gross'];
 $q = "INSERT INTO orders (user_id, transaction_id, payment_status, payment_amount) VALUES ($uid, '$txn_id', '$status', $amount)";
 $r = mysqli_query ($dbc, $q);
 if (mysqli_affected_rows($dbc) == 1) {
  //
 } else { // Problem inserting the order!
  trigger_error('The transaction could not be stored in the orders table!');
 }  
} // The order has already been stored!
  } // The right values don't exist in $_POST!
 } elseif (strcmp ($res, "INVALID") == 0) {
  // log for manual investigation
 }
} // End of the WHILE loop.
// Close the connection:
fclose ($fp);
} // End of $fp IF-ELSE.
?>

 

 

The code for the page containing the button which takes registrants to paypal is this:

 

<?php
session_start();
$page_title = "CYO Registration | Payment";
 if($_SESSION['form3done'] != '1') {
  // echo 'this page accessed in error
  include('includes/header.php');
  ?>
   </div> <!-- c1 content closer -->
  </div> <!-- c1 closer -->
 <div id="c2">
   <div class="content">
	 <h1>Oops!</h1>
	 <p>You have accessed this page in error.</p>
   </div> <!-- c2 content closer -->
  </div> <!-- c2 closer -->
  <?php
 include('includes/footer.php');
} else {
include('includes/header.php');
$uid = $_SESSION['uid'];
$fee = $_SESSION['fee'];
?>
</div> <!-- c1 content closer -->
</div> <!-- c1 closer -->
 <div id="c2">
<div class="content">
  <?php
 // registration period
  $thisyear = date('Y');
 $nextyear = date('Y')+1;
  ?>
  <h1>CYO Registration Fee, <?php echo $thisyear . '–' . $nextyear; ?></h1>
  <p>A fee of <strong>€<?php echo $_SESSION['fee']?></strong> is now payable to complete your registration for CYO. Please click the button below to proceed.</p></p><em>(You will be momentarily redirected to PayPal where you can make your secure payment. You will then be returned here, on completion of your payment.)</em></p>
  <table width="100%" style='border: 1px solid gray;padding: 4px 0 0 10px;border-radius: 5px;margin-top: 30px;'>
	<tr>
	  <td width="80%" style='font-weight:bold;'>CYO Registration fee for <?php echo stripslashes($_SESSION['firstname']) . ' ' . stripslashes($_SESSION['lastname']) . " (" . $uid . ")";?></td>
	 <td width="20%">
		  <form action="https://www.sandbox.paypal.com/cgi-bin/webscr" method="post">
			<input type="hidden" name="cmd" value="_xclick">
			<input type="hidden" name="custom" value="<?php echo $uid;?>">
			<input type="hidden" name="business" value="ZRTUX4VF273MJ">
			<input type="hidden" name="lc" value="GB">
			<input type="hidden" name="item_name" value="Reg">
			<input type="hidden" name="amount" value="<?php echo $fee;?>">
			<input type="hidden" name="currency_code" value="EUR">
			<input type="hidden" name="button_subtype" value="services">
			<input type="hidden" name="no_note" value="1">
			<input type="hidden" name="no_shipping" value="1">
			<input type="hidden" name="rm" value="1">
			<input type="hidden" name="return" value="https://www.corkyouthorchestra.ie/registration_complete.php">
			<input type="hidden" name="cancel_return" value="https://www.corkyouthorchestra.ie/cancel_purchase.php">
			<input type="hidden" name="bn" value="PP-BuyNowBF:btn_paynow_SM.gif:NonHosted">
			<input type="image" src="https://www.sandbox.paypal.com/en_GB/i/btn/btn_paynow_SM.gif" border="0" name="submit" alt="PayPal — The safer, easier way to pay online.">
			<img alt="" border="0" src="https://www.sandbox.paypal.com/en_US/i/scr/pixel.gif" width="1" height="1">
		  </form>
  </td>
	</tr>
  </table>
</div> <!-- c2 content closer -->
</div> <!-- c2 closer -->
<?php
 include('includes/footer.php');
}

 

 

My db is not being updated however. My orders table is this:

 

CREATE TABLE IF NOT EXISTS `orders` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `user_id` int(10) unsigned default NULL,
 `transaction_id` varchar(19) NOT NULL,
 `payment_status` varchar(15) NOT NULL,
 `payment_amount` decimal(6,2) unsigned NOT NULL,
 `payment_date_time` timestamp NOT NULL default CURRENT_TIMESTAMP,
 PRIMARY KEY  (`id`),
 KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

 

If anyone could tell me why my db is not being updated, I would greatly appreciate it.

 

TIA.

[rob]

The cost of registration is calculated in advance, before the registrant gets to the page with the paypal 'Pay Now' button, and is stored in a 'fee' session variable. I'm then passing the value of $_SESSION['fee'] to the paypal button... or at least that is my intention.

 

 

Have you looked at the source code for that page to confirm it's being set correctly?

 

You should dump the contents of the information sent by paypal to your ipn script into a text file. This will enable you to see which conditions are met and whether your ipn script gets to the part where the sql query should be executed.

 

If it does then you'll have to go through the standard sql debugging to see where the error is.

[peppericious1]

Have you looked at the source code for that page to confirm it's being set correctly?

 

You should dump the contents of the information sent by paypal to your ipn script into a text file. This will enable you to see which conditions are met and whether your ipn script gets to the part where the sql query should be executed.

 

If it does then you'll have to go through the standard sql debugging to see where the error is.

 

i know the fee is being set correctly because it determines the price charged when I get to paypal and that amount is correct when I get to paypal.

 

Regarding your suggestion to dump the contents returned by Paypal to the ipn script into a text file, how would I do that, exactly? It's my first time doing a Paypal implementation so I'm brand new to this.

 

I've been very frustrated trying to figure out what POST variables are returned by PayPal to my ipn script. It returns lots of variables - in addition to those associated with my own button, right?... I've tried to find out from Paypal's own documentation but haven't been able to figure it out....

[rob]

2nd tip page 154 in the book: there's a script provided by Larry in the downloadable code if you're not sure how.

[peppericious1]

2nd tip page 154 in the book: there's a script provided by Larry in the downloadable code if you're not sure how.

 

 

... silly, silly me... The cause of my grief was a simple typo in the IPN notification URL.... duh! PayPal was never getting to the script at all.

 

Thanks in any case, Rob, for your help. Larry should put those sidebar tips in red... missed it completely on p154!