[RELEASE]Automatic Coin Delivery from PayPal IPN payment messages

[freeskier4lif3]

Here is a script to automatically send coins to your players based on payments made through PayPal. This does require a good amount of setup and I would recommend testing it before going live.

Note: This script is using the older mysql / mssql PHP functions. I highly recommend using PDO, and that you use this code only as a reference for getting your PayPal IPN setup.

Important!
This script assumes that you sent the users account name through PayPal IPN's 'custom' field when they made the payment. You have to edit your PayPal provided button code to add an input field with the name custom and value account name. This can be hidden if you have the information already by having the user logged into your website, or you can have the user enter their account name by having the field not hidden.

for example:

<input value='accountname' name='custom' type='hidden'>

Requirements
- PayPal account that can make payment buttons
- Web server with mssql and mysql php extensions enabled
- MySQL database, for logging payments
- Patience


Setup Setup on your sever side
- Open paypalipnconfig.php and fill out for your server information and what packages you will offer
- Open paypalDB.sql and copy it into your MySQL Database and run it, this creates a table called 'paypal_logs' which will store paypal payments and coins sent out.
- Done on your server's end...

PayPal Setup
- Create a button
- make sure to set a fixed donation amount
- click "Step 3: Customize advanced features
- click add advanced variables and add the following line to the text area with your path to the script

notify_url="http://www.mydomain.com/folder/paypalipn.php"

Error Logging
If there is an error with a payment, either an invalid IPN, or email, or user name, anything... it will be output to a file _paypal_error_log.txt in the same directory that holds the script. All error messages are timestamped so you can review errors and see what's going on with them.


Make sure to test the system before making it live on your server!

Enjoy!


The Code
paypalipnconfig.php

 Spoiler

<?php
// PaypalIPNConfig.php

// --- MYSQL CONFIG ---
define('MYSQL_HOST', '127.0.0.1'); // Mysql host address
define('MYSQL_USER', 'user'); // User name
define('MYSQL_PASS', 'password'); // User password
define('MYSQL_DB', 'paypal'); // Database name


// --- MSSQL CONFIG ---
define('MSSQL_HOST', '127.0.0.1'); // Mssql host address
define('MSSQL_USER', 'user'); // User name
define('MSSQL_PASS', 'password'); // User password

// --- PAYPAL CONFIG ---
define('PAYPAL_EMAIL', ''); // Your paypal email on which you recieve payments
define('CURRENCY', 'USD'); // The type of currency you set up in paypel
$paypal_packages = array(
// $ Price , Coin amount -- delimit arrays with a comma to add more
array(5.99, 1000),
array(9.99, 2000),
array(20.00, 5000)
);

?>

paypalipn.php

 Spoiler

<?php
include 'logger.php';
include 'paypalipnconfig.php';
include 'mysql.class.php';
include 'mssql.class.php';

$req = 'cmd=' . urlencode('_notify-validate');

foreach ($_POST as $key => $value) {
$value = urlencode(stripslashes($value));
$req .= "&$key=$value";
}


$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, 'https://www.paypal.com/cgi-bin/webscr');
curl_setopt($ch, CURLOPT_HEADER, 0);
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'));
$res = curl_exec($ch);
curl_close($ch);


// 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'];

$account = $_POST['custom'];
if($account == 0)
$acount = 'NO ACCOUNT';

$buff = sprintf("%s    %s    \$%0.2f    %s    %s    %s", $txn_id, $payment_status, $payment_amount, $receiver_email, $payer_email, $account);
$coins_sent = 0;

if (strcmp ($res, "VERIFIED") == 0)
{
// create our db connections
$db_mysql = new mysql();
$db_mysql->connect();

$db_mssql = new mssql();
$db_mssql->connect();

// check the payment_status is Completed
if($payment_status == 'Completed')
{

// check that txn_id has not been previously processed
$r = $db_mysql->query("SELECT txn_id FROM paypal_logs WHERE txn_id='".$txn_id."';");

if(mysql_num_rows($r) == 0)
{
// check that receiver_email is your Primary PayPal email
if($receiver_email == PAYPAL_EMAIL)
{
// check that payment_amount/payment_currency are correct
$validpackage = false;
for($i = 0; $i < count($paypal_packages); ++$i)
{
if($paypal_packages[$i][0] == $payment_amount && $payment_currency == CURRENCY)
{
$validpackage = true;
break;
}
}

// process payment if valid package
if($validpackage)
{
dbg_log('account = '.$account);

$r = $db_mssql->query("SELECT user_no FROM dbo.USER_PROFILE WHERE user_id='".$account."';");

if(mssql_num_rows($r))
{
$user_no = mssql_fetch_array($r);

mssql_select_db('cash');
$r = $db_mssql->query("UPDATE dbo.user_cash SET amount = amount + ".$paypal_packages[$i][1]." WHERE user_no='".$user_no[0]."';");

// Added coins succesfully!
if($r)
$coins_sent = 1;
else
dbg_log('ERROR SENDING COINS: '.$buff);
}
else
dbg_log('ACCOUNT NOT FOUND: '.$buff);

// Add record to MySQL
$db_mysql->query("INSERT INTO paypal_logs VALUES ('".$txn_id."',".$payment_amount.",'".$payer_email."','".$account."',".$paypal_packages[$i][1].",".$coins_sent.",CURRENT_TIMESTAMP);");

}
else
dbg_log('INVALID PACKAGE: '.$buff);
}
else
dbg_log('INVALID EMAIL: '.$buff);
}
else
dbg_log('DUPLICATE IPN: '.$buff);
}

}
else if (strcmp ($res, "INVALID") == 0) {
// log for manual investigation
  dbg_log('INVALID IPN: '.$buff);
}

?>

mysql.class.php

 Spoiler

<?php
// If configuration not loaded, quit
if(!defined('MYSQL_HOST'))
die;
/////////////////////////////////////////////
// CLASS: mysql
//  
// DESCRIPTION: a class used to connect to
//   and communicate with a mysql database
/////////////////////////////////////////////

class mysql
{
private $queryCount = 0; // count of processed queries
private $conn; // connection
private $database; // database name
private $is_connected = false; // Connection made to DB?
private $query = ''; // query string
private $q_start = 0; // query start time
private $q_finish = 0; // query finish time
private $q_time = 0; // query time


//////////////////////////////////////////////////
// connect() : establishes a connection to
// mysql server
//  
// PARAMS \\
//  in > $host : address of database
//  in > $user : user name
//  in > $pass : user password
//  in > $db   : database name
//  in > $stop : should the script stop if
//               connect failes?
//
//  return : true if successful connection made
//      false if connection failed
//////////////////////////////////////////////////
public function connect($host='',$user='',$pass='',$db='',$stop=1)
{
// If no parameters passed in use defaults
if(!$host)
{
$host=MYSQL_HOST;
$user=MYSQL_USER;
$pass=MYSQL_PASS;
$db=MYSQL_DB;
}

$this->database = $db;

if($stop) // if stop on fail
{
$this->conn = mysql_connect($host, $user, $pass);

if($this->conn)
{
if(!mysql_select_db($db, $this->conn))
{
//global $err_string;
//$err_string = 'ERROR : Can not select database \'$db\'';
//include GLOBAL_DIR.'err.htm';  // Display error page
die; // return false
}
}

$this->is_connected = true;
$this->query("SET NAMES 'utf8'");
return true;
}
else
{
$this->conn = @mysql_connect($host, $user, $pass);
if($this->conn)
{
if(!mysql_select_db($db, $this->conn))
return false;
$this->is_connected = true;
$this->query("SET NAMES 'utf8'");
return true;
}
else
return false;
}
}


//////////////////////////////////////////////////
// query() : query the connected mysql database
//  
// PARAMS \\
//  in > $query : string to send as query
//  in > $err : should we break on error?
//
//  return : query result if success
//      false if query failed
//////////////////////////////////////////////////
public function query($query, $err=1)
{
if(!$this->isConnected())
$this->connect();

//$this->startQuery($query);
$r = mysql_query($query, $this->conn);
//$this->finishQuery();

if(!$r)
{
if($err)
{
die($this->error($query, mysql_error()));
}
else
{
echo $this->error(mysql_error());
return false;
}
}

$this->queryCount++;
return $r;
}


//////////////////////////////////////////////////
// queryr() : query the connected mysql database
//  
// PARAMS \\
//  in > $query : string to send as query
//  in > $err : should we break on error?
//
//  return : query result if success
//      false if query failed, or numrows = 0
//////////////////////////////////////////////////
public function queryr($query, $err=1)
{
if(!$this->isConnected())
$this->connect();

$this->startQuery($query);
$r = mysql_query($query, $this->conn);
$this->finishQuery();

if(!$r)
{
if($err)
{
die($this->error($query, mysql_error()));
}
else
{
echo $this->error(mysql_error());
return false;
}
}

$this->queryCount++;

return (mysql_num_rows($r)==0) ? false : $r;
}


//////////////////////////////////////////////////
// queryz() : query the connected mysql database
//  
// PARAMS \\
//  in > $query : string to send as query
//  in > $err : should we break on error?
//
//  return : query result if success
//      false if query failed or no affected
//////////////////////////////////////////////////
public function queryz($query, $err=1)
{
if(!$this->isConnected())
$this->connect();

$this->startQuery($query);
$r = mysql_query($query, $this->conn);
$this->finishQuery();

if(!$r)
{
if($err)
{
die($this->error($query, mysql_error()));
}
else
{
echo $this->error(mysql_error());
return false;
}
}

$this->queryCount++;
$r=mysql_affected_rows($r);
return ($r==0) ? false : $r;
}


//////////////////////////////////////////////////
// GENERAL USE MYSQL FUNCTIONS
//////////////////////////////////////////////////

public function esc($r)
{
if(!$this->isConnected())
$this->connect();
return mysql_real_escape_string($r, $this->conn);
}

public function result($r)
{
return mysql_result($r,0);
}

public function num_rows($r)
{
return mysql_num_rows($r);
}

public function fetch_row($r)
{
return mysql_fetch_row($r);
}

public function fetch_object($r)
{
return mysql_fetch_object($r);
}

public function fetch_assoc($r)
{
return mysql_fetch_assoc($r);
}

public function insert_id()
{
return mysql_insert_id($this->conn);
}

private function error($err)
{
//global $auth;
//if($auth->lvl >= UL_ADMIN)
return "<p style='color:#ce0000'><em>MySQL Error</em></p>".$error.mysql_errno($this->conn).' '.mysql_error($this->conn);
//return "<p style='color:#ce0000'><em>MySQL Error</em></p>Please report error to administrator.";
}

private function startQuery($query)
{
$this->query = $query;
$this->q_start = getMicroTime();
$this->q_time = 0;

}

private function finishQuery()
{
if(!$this->query)
return;

$this->q_finish = getMicrotime();

$elapsed_time = $this->q_finish - $this->q_start;
$this->q_time += $elapsed_time;

if($log_slow_queries && $slow_query_time > 0)
{
$buff = '';

if(!file_exists(MYSQL_SLOW_QUERY_LOG))
$buff = '<?php exit();?>'."\n";

$buff .= sprintf("%s    %s    \n    %0.6f sec\n\n", date("Y-m-h H:i"), $this->query, $elapsed_time);

if($file = fopen(MYSQL_SLOW_QUERY_LOG, 'a'))
{
fwrite($file, $buff);
fclose($file);
}
}
else
$this->query=null;
}

public function isConnected() {
return $this->is_connected?true:false;
}

public function close()
{
if(!$this->isConnected())
return;
mysql_close($this->conn);
}
};

?>

mssql.class.php

 Spoiler

<?php
// If configuration not loaded, quit
if(!defined('MSSQL_HOST'))
die;

// If server does not have the mssql extension enabled, quit
if(!extension_loaded('mssql'))
die;

/////////////////////////////////////////////
// CLASS: mssql
//  
// DESCRIPTION: a class used to connect to
//   and communicate with a mssql database
//   with the mssql extension
/////////////////////////////////////////////
class mssql
{

private $conn;
private $db;
private $is_connected = false;

// connect() : connect to mssql server
public function connect($host='', $user='', $pass='')
{
if(!$host)
{
$host = MSSQL_HOST;
$user = MSSQL_USER;
$pass = MSSQL_PASS;
}

  $this->conn = mssql_connect($host, $user, $pass);
//if(!$conn)
// return error('ERROR : Cannot connect to MSSQL Server at '.$host);

$this->db = mssql_select_db('account', $this->conn);
//if(!$this->db)
// return error('ERROR : Cannot connect to MSSQL Database at '.$host);

$this->is_connected = true;
return true;
}

// query() : send a query to mssql server
public function query($query)
{
if(!$this->isConnected())
connect();

$r = mssql_query($query);
//if(!$r)
// error($query.' : '.mssql_get_last_message());
return $r;
}

// use() : change the database context
public function useDB($dbname)
{
if(!$this->isConnected())
connect();
$this->db = mssql_select_db($dbname, $this->conn);
//if(!$this->db)
// error('ERROR : Cannot connect to MSSQL Database at '.$host);
}

// error() : prints an error message and quits
public function error($err)
{
return "<p style='color:#ce0000'><em>$err</em></p>";
}

// isConnected() : returns true is connected to mssql, false if not
public function isConnected()
{
return $this->is_connected;
}

public function get_last_message(){
return mssql_get_last_message();
}

public function numrows($r){
return mssql_num_rows($r);
}

public function numfields($r){
return mssql_num_fields($r);
}

public function fetch_row($r){
return mssql_fetch_row($r);
}

public function fetch_array($r){
return mssql_fetch_array($r);
}

public function rows_affected($r){
return mssql_rows_affected($r);
}

};

?>

logger.php

 Spoiler

<?php
// debuglogger.php
//   used to log time stamped strings to a file

function dbg_log($msg, $logfile='_paypal_error_log.txt')
{
$buff = '';

if(!file_exists($logfile))
$buff = '<?php die; ?>'."\n";

$buff .= sprintf("%s    %s\n\n", date("Y-m-h H:i"), $msg);

$f = fopen($logfile, 'a');
fwrite($f, $buff);
fclose($f);
}


?>

Edit:Added new file below.
paypalipn.php -> this one stores your paypal logs in mssql. Just make a table with the same layout as the mysql example above in your mssql "cash" db.

 Spoiler

<?php
include 'logger.php';
include 'paypalipnconfig.php';
include 'mssql.class.php';

$req = 'cmd=' . urlencode('_notify-validate');
 
foreach ($_POST as $key => $value) {
	$value = urlencode(stripslashes($value));
	$req .= "&$key=$value";
}
 
 
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, 'https://www.paypal.com/cgi-bin/webscr');
curl_setopt($ch, CURLOPT_HEADER, 0);
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'));
$res = curl_exec($ch);

if(curl_errno($ch))
	dbg_log('cURL ERROR: '.curl_error($ch));
curl_close($ch);
 
 
// 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'];

$character = $_POST['custom'];
 
$buff = sprintf("%s\t%s\t\$%0.2f\t%s\t%s\t%s", $txn_id, $payment_status, $payment_amount, $receiver_email, $payer_email, $character);
$coins_sent = 0;

 
if($res == "VERIFIED") 
{

	$db_mssql = new mssql();
	$r = $db_mssql->connect();


	// check the payment_status is Completed
	if($payment_status == 'Completed')
	{

		mssql_select_db('cash');
		// check that txn_id has not been previously processed
		$r = $db_mssql->query("SELECT txn_id FROM dbo.paypal_logs WHERE txn_id='".$txn_id."';");

		if(mssql_num_rows($r) == 0)
		{


			// check that receiver_email is your Primary PayPal email
			if($receiver_email == PAYPAL_EMAIL)
			{
				// check that payment_amount/payment_currency are correct
				$validpackage = false;
				for($i = 0; $i < count($paypal_packages); ++$i)
				{
					if($paypal_packages[$i][0] == $payment_amount && $payment_currency == CURRENCY)
					{
						$validpackage = true;
						break;
					}
				}

				// process payment if valid package
				if($validpackage)
				{
					mssql_select_db('character');
					$r = $db_mssql->query("SELECT user_no FROM dbo.user_character WHERE character_name='".$character."';");
					if(mssql_num_rows($r))
					{
						$user_no = mssql_fetch_array($r);


						mssql_select_db('cash');
						$r = $db_mssql->query("UPDATE dbo.user_cash SET amount = amount + ".$paypal_packages[$i][1]." WHERE user_no='".$user_no[0]."';");

						// Added coins succesfully!
						if($r)
							$coins_sent = 1;
						else
							dbg_log('ERROR SENDING COINS: '.$buff);
					}
					else
						dbg_log('ACCOUNT NOT FOUND: '.$buff);

					mssql_select_db('cash');
					// Add record to MSSQL
					$db_mssql->query("INSERT INTO paypal_logs VALUES ('".$txn_id."',".$payment_amount.",'".$payer_email."','".$account."',".$paypal_packages[$i][1].",".$coins_sent.",CURRENT_TIMESTAMP);");

					}
				else
					dbg_log('INVALID PACKAGE: '.$buff);
			}
			else
				dbg_log('INVALID EMAIL: '.$buff);
		}
		else
			dbg_log('DUPLICATE IPN: '.$buff);
	}
	
}
else if ($res == "INVALID" ) {
	// log for manual investigation
 	dbg_log('INVALID IPN: '.$buff);
}

?>

[fastswipe]

Awesome great job bro, keep up the good work!

[Zektor]

Excellent tutorial, shouldn't be charging so much for installing this for others (I understand it's a service, but it doesn't take too long to configure IPN).

[freeskier4lif3]

Originally Posted by Zektor

Excellent tutorial, shouldn't be charging so much for installing this for others (I understand it's a service, but it doesn't take too long to configure IPN).

Thank you, and about charging that much... it's for time. Which is the reason I explained it well, if they can follow the majority of the tutorial and need help for like 30 mins they only owe me for 30 mins. But tech savvy people shouldn't really need any help if they google how to test IPN with paypal's sandbox.

Also, going to add a second paypalipn.php that stores the data in mssql and not in mysql for people that aren't running mysql for their website. Just make a table with the same fields as explained in the mysql one and you'll be set.

[killer2p]

with some edits couldn't it be used for Shaiya?

[Ultimo ☼]

What web server can use mssql extension? i use XAMPP but it can only use sqlsrv extension

[Bocc1337]

hello free i would like for you to set up the auto coin delivery system on my server.i tryed my best to do it my self and cant do it.. you can find me on skype as bocc1337 please i really need this to work. thx i will be watching for your reply.

[DivineDK]

So if I has followed the tutorial there 100%

So under the:
advanced variables I should put this and ONLY this?:
notify_url="http://server1.bloodteardekaron.com/donate/paypalipn.php"

and in the donate website I should have a form that says:
<input value='accountname' name='custom' type='hidden'>
Donate link
Donate amount(5)

<input value='accountname' name='custom' type='hidden'>
Donate link
donate Amount(50)

And it would know by the IPN how much that was send, and in the array it would give correct amount of DCoins?

[freeskier4lif3]

Yes, you configure the dollar amount to coin amount in paypalipnconfig.php and when an IPN message is received it looks at the received dollar amount and gives coins based on the array you set up.

[DivineDK]

So this is right for the posting?

<form action="https://www.paypal.com/cgi-bin/webscr" method="post" target="_top">
<input value='accountname' name='custom' type='text'>
<input type="hidden" name="cmd" value="_s-xclick">
<input type="hidden" name="hosted_button_id" value="FNL9D7EFRTMXF">
<input type="image" src="https://www.paypalobjects.com/en_US/US/i/btn/btn_donateCC_LG.gif" border="0" name="submit" alt="PayPal – den sikre og nemme måde at betale på nettet.">
<img alt="" border="0" src="https://www.paypalobjects.com/en_US/i/scr/pixel.gif" width="1" height="1">
</form>


and on paypal it is said to be 0.01 USD (for testing purposes)
and this in advanced variables:

address_override=1
notify_url=http://server1.bloodteargames.com/donate/paypalipn.php

Does that sound right?

[freeskier4lif3]

yea, looks good.

Also, you can use the paypal sandbox to test sending payments without actually sending real money. So you don't have to send an actual penny back and forth.

[DivineDK]

How?
As that code I just end to you, does not work at all, can I add you over skype?

As my friend donates, fill in the accountname, but nothing happens, we waited 10mins and no change of dcoins.

and how do I test with the sandbox?

as I had edited that curl thingy in the ipn.php, no change, no report of working or not working.

NVM I found out why, I had forgotten to portforward the port 443 on the server -.-

And that did not help it at all.

[Mystra-Shaiya]

Hey add me on skype: Mystra-Shaiya
I am trying to set this up for Shaiya server i just need help with the IPN setup because i am sure that i did everyting right ;o
Thanks!

[janvier123]

Originally Posted by Mystra-Shaiya

Hey add me on skype: Mystra-Shaiya
I am trying to set this up for Shaiya server i just need help with the IPN setup because i am sure that i did everyting right ;o
Thanks!

dude, really ?
this is the DEKARON section!

[xfactorbowler]

he could be confused, after all remember silk's release of the 1click dekaron an it being named shaiya, but yeah this is dekaron :P