Custom DB insert after payment confirmation

[qwerpoiu338]

Greetings,

I'm trying to insert paypal_ipn_id and some other variables into a custom table on the Paypal payment is completed. But nothing gets inserted in the custom table.
Assuming the correct way to do is to define custom table in the database_tables.php file and use the defined name in the paypal.php file shown below:

function after_process() {    
global $insert_id, $db;    
if ($_SESSION['paypal_transaction_PDT_passed'] != true) {      
$_SESSION['order_created'] = '';      
unset($_SESSION['paypal_transaction_PDT_passed']);      
return false;    
} else {      
unset($_SESSION['paypal_transaction_PDT_passed']);      
$sql = "insert into " . TABLE_ORDERS_STATUS_HISTORY . " (comments, orders_id, orders_status_id, date_added) values (:orderComments, :orderID, :orderStatus, now() )";
      $sql = $db->bindVars($sql, ':orderComments', 'PayPal status: ' . $this->pdtData['payment_status'] . ' ' . ' @ ' . $this->pdtData['payment_date'] . "\n" . ' Trans ID:' . $this->pdtData['txn_id'] . "\n" . ' Amount: ' . $this->pdtData['mc_gross'] . ' ' . $this->pdtData['mc_currency'] . '.', 'string');
      $sql = $db->bindVars($sql, ':orderID', $insert_id, 'integer');
      $sql = $db->bindVars($sql, ':orderStatus', $this->order_status, 'integer');
      $db->Execute($sql);

 $var2 = 1;
 $var3= 0;
 $var4 = 0;

      $sql = "insert into " . TABLE_SUB_LIST . " (mem_id, field2, field3, field4) values (:ipnid, :var2, :var3, :var4)";
      $sql = $db->bindVars($sql, ':ipnid', $this->paypal_ipn_id, 'integer');
      $sql = $db->bindVars($sql, ':var2', $var2, 'integer');
      $sql = $db->bindVars($sql, ':var3', $var3, 'integer');
      $sql = $db->bindVars($sql, ':var4', $var4, 'integer');
      $db->Execute($sql);

 ipn_debug_email('PDT NOTICE :: Order added: ' . $insert_id . "\n" . 'PayPal status: ' . $this->pdtData['payment_status'] . ' ' . ' @ ' . $this->pdtData['payment_date'] . "\n" . ' Trans ID:' . $this->pdtData['txn_id'] . "\n" . ' Amount: ' . $this->pdtData['mc_gross'] . ' ' . $this->pdtData['mc_currency']);
    }
  } 

What did I do wrong? Please help.

[qwerpoiu338]

Anyone?

[DrByte]

Your code itself looks fine.

And, if your transaction is happening via the PayPal IPN Website Payments Standard module, *and* you have it correctly set up to use a PDT token, then it should work as long as PayPal's servers don't trigger the IPN notification before the customer gets back to your store from PayPal's site.

If you want to cause it to do the insert during the IPN handling process, you'll have to intercept the order generation code in the ipn_main_handler script instead.

Unfortunately PayPal is a complicated duck.

[qwerpoiu338]

Looked into the piece of code of ipn_main_handler. Not sure where I can add similar insert instructions into it.

[qwerpoiu338]

The sql insertion code seemed to be working within the case 'unique': condition. Can someone confirm that this is the correct place to insert data into custom table after purchase confirmation?

[DrByte]

The sql insertion code seemed to be working within the case 'unique': condition. Can someone confirm that this is the correct place to insert data into custom table after purchase confirmation?

Yes, and probably best after this line:
$order->send_order_email($insert_id, 2);