Want to check in the database if the txn_id has been used before

oskare100 · October 29, 2006

Hello,
When Paypal runs my IPN script I save all transaction details in a table in a database.

The payment_status can be pending (if echeck and so on) or completed. I store the payment_status in the the table among with all other transaction details. Each transaction has an uniqe txn_id, I do also save the txn_id in the table.

When I payment arrives as completed I first want to check if it is a new payment that has arrived with a new txn_id or if it is an old pending payment with transaction details and a txn_id that is already in the table that has been cleared and completed.

It could also be an txn_id (transaction) that my script already has created an account for so if the payment_status in my table is "completed" for that txn_id I want it to do something else (send me an email or log it).

I don't know how advanced that it but I'm stuck and I can't figure out how to do that so please, I need some help : (

Here is what I did come up with:
$result = mysql_query("SELECT txn_id FROM paypal_sales");

But then I don't know how to find if that txn_id has been used or if the payment_status is completed or pending.

Best Regards
Oskar R

shocker-z · October 29, 2006

[code]
$txn_id=CHANGE_ME
$result = mysql_query("SELECT txn_id,status FROM paypal_sales WHERE txn_id = '$txnid'");
$row = mysql_fetch_array($result);
if ($row['txn_id'] == '') {
//txn_id does not exist
}
else {
if ($row['status'] == 'pending') {
//this means that the status of the txn_id is pending do that code here
}
}
[/code]

If that helps at all? (code not tested and you will need to change stuff like 'status' and so on accordingly)

*I'm not sure on how you are passing/creating the txn_id so please change 'CHANGE_ME' for that variable e.g. $_GET['txn_id']

Regards
Liam

oskare100 · October 29, 2006

Hello,
Thanks, I think it helped, I've now done like this:
[code=php:0]<?php
$txn_id = "12345678910";
//Connect to MySQL
mysql_connect("localhost", "test", "test") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

$result = mysql_query("SELECT txn_id,payment_status FROM paypal_sales WHERE txd_id = '$txn_id'");
$row = mysql_fetch_array($result);
if ($row['txn_id'] == '') {
//the txn_id doesn't exist
}
else {
if ($row['payment_status'] == 'pending') {
//the txn_id does exist but is pending
}
}
?>[/code]

And when I run that script I get this error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/httpd/vhosts/com/httpdocs/row.php on line 9

What can be the problem? I've tried to change things and so on but nothing helped (I'm not that good at PHP).

Best Regards
Oskar R

oskare100 · October 29, 2006

Hello again,
After a tip I changed the code to:
[code=php:0]<?php
$txn_id = "12345678910";
//Connect to MySQL
mysql_connect("localhost", "test", "30053005") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

$sql = "SELECT txn_id,payment_status FROM paypal_sales WHERE txd_id = '$txn_id'";
$result = mysql_query($sql) or die( mysql_error() );
$row = mysql_fetch_array($result);
if ($row['txn_id'] == '') {
echo "the txnid does not exist";
}
else {
if ($row['payment_status'] == 'pending') {
echo "the txnid does exist but is pending";
}
}
?>[/code]

And now I get the error "Unknown column 'txd_id' in 'where clause'". so, what is a column then? In myphpadmin is say "field". Here is the structure of my paypal_sales table:

[CODE]--
-- Table structure for table `paypal_sales`
--

CREATE TABLE `paypal_sales` (
`invoice` int(10) unsigned NOT NULL auto_increment,
`receiver_email` varchar(60) default NULL,
`item_name` varchar(100) default NULL,
`item_number` varchar(10) default NULL,
`quantity` varchar(6) default NULL,
`payment_status` varchar(10) default NULL,
`pending_reason` varchar(10) default NULL,
`payment_date` varchar(20) default NULL,
`mc_gross` varchar(20) default NULL,
`mc_fee` varchar(20) default NULL,
`tax` varchar(20) default NULL,
`mc_currency` char(3) default NULL,
`txn_id` varchar(20) default NULL,
`txn_type` varchar(10) default NULL,
`first_name` varchar(30) default NULL,
`last_name` varchar(40) default NULL,
`address_street` varchar(50) default NULL,
`address_city` varchar(30) default NULL,
`address_state` varchar(30) default NULL,
`address_zip` varchar(20) default NULL,
`address_country` varchar(30) default NULL,
`address_status` varchar(10) default NULL,
`payer_email` varchar(60) default NULL,
`payer_status` varchar(10) default NULL,
`payment_type` varchar(10) default NULL,
`notify_version` varchar(10) default NULL,
`verify_sign` varchar(10) default NULL,
`referrer_id` varchar(10) default NULL,
`memo` varchar(255) default NULL,
`for_auction` varchar(20) default NULL,
`auction_buyer_id` varchar(64) default NULL,
`auction_closing_date` varchar(20) default NULL,
`auction_multi_item` varchar(20) default NULL,
`account_username` varchar(100) default NULL,
`account_password` varchar(20) default NULL,
`account_email` varchar(100) default NULL,
`account_group` varchar(20) default NULL,
PRIMARY KEY (`invoice`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;[/CODE]

So what should I do to make it do what I want (as in the first post)? I mean if field and column is not the same thing. Because the field txn_id surely exist and that's where I want the script to select from and see if the txn_id is already in use and if it is in use, if the payment status is pending.

Best Regards
Oskar R

oskare100 · October 30, 2006

Hello,
OK, I got that working now in a seperate test script.

Here is what I added to the main script:
[code=php:0]//Check if the transaction already is in the database
$sql = "select txn_id , payment_status from paypal_sales where txn_id = '$txn_id'";
$result = mysql_query($sql) or die( mysql_error() );
$row = mysql_fetch_array($result);
if ($row['txn_id'] == '') {
// It's a new order - continue
echo "it's a new payment";
}
if ($row['payment_status'] == 'pending') {
// It's an old order that has been cleared - change status
$sql2 = "update `paypal_sales` set `payment_status` = 'Completed' where txn_id = '$txn_id'";
$result2 = mysql_query($sql2) or die( mysql_error() );
echo "it's an old payment that has cleared";
}
if ($payment_status == "Refunded"){
echo "payment status refunded";
if ($row['txn_id'] == "$txn_id") {
// The payment is in the database but has been refunded.
echo "the payment id is in the databse";
if ($row['payment_status'] == 'Completed') {
// The payment has been completed and the account has been created.
echo "the payment status is complete - delete the account";
$sql3 = "select account_username from paypal_sales where txn_id = '$txn_id'";
$result3 = mysql_query($sql3) or die( mysql_error() );
$del_account_username = mysql_result($result3);

// Prepare to delete account
$sql4 = "select username from dl_users where username = '$del_account_username'";
$result4 = mysql_query($sql4) or die( mysql_error() );
$row4 = mysql_fetch_array($result);
$del_account_group_id = $row4['group'];
$del_account_email = $row4['email'];
$del_account_regKey = $row4['regKey'];
$del_account_iplog = $row4['iplog'];

// Delete account
$sql5 = "DELETE FROM dl_users WHERE username = '$del_account_username'";
$result5 = mysql_query($sql5) or die( mysql_error() );

//Change payment status
$sql5 = "update `paypal_sales` set `payment_status` = 'Refunded' where txn_id = '$txn_id'";
$result5 = mysql_query($sql5) or die( mysql_error() );

die;
}
if ($row['payment_status'] == 'Pending') {
// The payment has been pending.
//Change payment status
$sql6 = "update `paypal_sales` set `payment_status` = 'Refunded' where txn_id = '$txn_id'";
$result6 = mysql_query($sql5) or die( mysql_error() );
echo "the payment status is pending, changed is to Refunded";

die;
}
} else {
// The payment has been refunded but didn't exist in the database??
}
}[/code]

And now that part doesn't work at all + that it doesn't echo anything, nor my notes neither any error messages. This was added in the beginning of the script after it has connected to the database. What can be the problem? I've tried to change thing over and over but it still doesn't echo anything...

Best Regards
Oskar R

oskare100 · October 30, 2006

Hello,
OK, I got that working now in a seperate test script.

Here is what I added to the main script:
[code=php:0]//Check if the transaction already is in the database
$sql = "select txn_id , payment_status from paypal_sales where txn_id = '$txn_id'";
$result = mysql_query($sql) or die( mysql_error() );
$row = mysql_fetch_array($result);
if ($row['txn_id'] == '') {
// It's a new order - continue
echo "it's a new payment";
}
if ($row['payment_status'] == 'pending') {
// It's an old order that has been cleared - change status
$sql2 = "update `paypal_sales` set `payment_status` = 'Completed' where txn_id = '$txn_id'";
$result2 = mysql_query($sql2) or die( mysql_error() );
echo "it's an old payment that has cleared";
}
if ($payment_status == "Refunded"){
echo "payment status refunded";
if ($row['txn_id'] == "$txn_id") {
// The payment is in the database but has been refunded.
echo "the payment id is in the databse";
if ($row['payment_status'] == 'Completed') {
// The payment has been completed and the account has been created.
echo "the payment status is complete - delete the account";
$sql3 = "select account_username from paypal_sales where txn_id = '$txn_id'";
$result3 = mysql_query($sql3) or die( mysql_error() );
$del_account_username = mysql_result($result3);

// Prepare to delete account
$sql4 = "select username from dl_users where username = '$del_account_username'";
$result4 = mysql_query($sql4) or die( mysql_error() );
$row4 = mysql_fetch_array($result);
$del_account_group_id = $row4['group'];
$del_account_email = $row4['email'];
$del_account_regKey = $row4['regKey'];
$del_account_iplog = $row4['iplog'];

// Delete account
$sql5 = "DELETE FROM dl_users WHERE username = '$del_account_username'";
$result5 = mysql_query($sql5) or die( mysql_error() );

//Change payment status
$sql5 = "update `paypal_sales` set `payment_status` = 'Refunded' where txn_id = '$txn_id'";
$result5 = mysql_query($sql5) or die( mysql_error() );

die;
}
if ($row['payment_status'] == 'Pending') {
// The payment has been pending.
//Change payment status
$sql6 = "update `paypal_sales` set `payment_status` = 'Refunded' where txn_id = '$txn_id'";
$result6 = mysql_query($sql5) or die( mysql_error() );
echo "the payment status is pending, changed is to Refunded";

die;
}
} else {
// The payment has been refunded but didn't exist in the database??
}
}[/code]

And now that part doesn't work at all + that it doesn't echo anything, nor my notes neither any error messages. This was added in the beginning of the script after it has connected to the database. What can be the problem? I've tried to change thing over and over but it still doesn't echo anything...

Here is the "main" script so far:
[code=php:0]<?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 /pp/index.php 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 ('www.belahost.com', 80, $errno, $errstr, 30);

//Assign posted variables to local variables
//Basic Information
$business = $_POST['business'];
$receiver_email = $_POST['receiver_email'];
$receiver_id = $_POST['receiver_id'];
$item_name = $_POST['item_name'];
$item_number = $_POST['item_number'];
$quantity= $_POST['quantity'];

//Advanced and Custom Information
$invoice = $_POST['invoice'];
$custom = $_POST['custom'];
$memo = $_POST['memo'];
$tax = $_POST['tax'];
$option_name1 = $_POST['option_name1'];
$option_selection1 = $_POST['option_selection1'];
$option_name2 = $_POST['option_name2'];
$option_selection2 = $_POST['option_selection2'];

//Shopping Cart Information
$num_cart_items = $_POST['num_cart_items'];

//Transaction Information
$payment_status = $_POST['payment_status'];
$pending_reason = $_POST['pending_reason'];
$reason_code = $_POST['reason_code'];
$txn_id = $_POST['txn_id'];
$parent_txn_id = $_POST['parent_txn_id'];
$txn_type = $_POST['txn_type'];
$payment_type = $_POST['payment_type'];

//Currency and Exchange Information
$mc_gross = $_POST['mc_gross'];
$mc_fee = $_POST['mc_fee'];
$mc_currency = $_POST['mc_currency'];
$settle_amount = $_POST['settle_amount'];
$settle_currency = $_POST['settle_currency'];
$exchange_rate = $_POST['exchange_rate'];
$payment_gross = $_POST['payment_gross'];
$payment_fee = $_POST['payment_fee'];

//Auction Information
$for_auction = $_POST['for_auction'];
$auction_buyer_id = $_POST['auction_buyer_id'];
$auction_closing_date= $_POST['auction_closing_date'];
$auction_multi_item = $_POST['auction_multi_item'];

//Buyer Information
$first_name = $_POST['first_name'];
$last_name = $_POST['last_name'];
$payer_business_name = $_POST['payer_business_name'];
$address_street = $_POST['address_street'];
$address_city = $_POST['address_city'];
$address_state = $_POST['address_state'];
$address_zip= $_POST['address_zip'];
$address_country = $_POST['address_country'];
$address_status = $_POST['address_status'];
$payer_email = $_POST['payer_email'];
$payer_id = $_POST['payer_id'];
$payer_status= $_POST['payer_status'];

if (!$fp) {
// HTTP ERROR
} else {
fputs ($fp, $header . $req);
while (!feof($fp)) {
$res = fgets ($fp, 1024);
if (strcmp ($res, "VERIFIED") == 0) {

// Check if the payment_status is Completed
if ($payment_status == "Completed")
{

//Connect to MySQL
mysql_connect("localhost", "test", "30053005") or die(mysql_error());

//Select file system database
mysql_select_db("test") or die(mysql_error());
echo "connected to database";

//Check if the transaction already is in the database
$sql = "select txn_id , payment_status from paypal_sales where txn_id = '$txn_id'";
$result = mysql_query($sql) or die( mysql_error() );
$row = mysql_fetch_array($result);
if ($row['txn_id'] == '') {
// It's a new order - continue
echo "it's a new payment";
}
if ($row['payment_status'] == 'pending') {
// It's an old order that has been cleared - change status
$sql2 = "update `paypal_sales` set `payment_status` = 'Completed' where txn_id = '$txn_id'";
$result2 = mysql_query($sql2) or die( mysql_error() );
echo "it's an old payment that has cleared";
}
if ($payment_status == "Refunded"){
echo "payment status refunded";
if ($row['txn_id'] == "$txn_id") {
// The payment is in the database but has been refunded.
echo "the payment id is in the databse";
if ($row['payment_status'] == 'Completed') {
// The payment has been completed and the account has been created.
echo "the payment status is complete - delete the account";
$sql3 = "select account_username from paypal_sales where txn_id = '$txn_id'";
$result3 = mysql_query($sql3) or die( mysql_error() );
$del_account_username = mysql_result($result3);

// Prepare to delete account
$sql4 = "select username from dl_users where username = '$del_account_username'";
$result4 = mysql_query($sql4) or die( mysql_error() );
$row4 = mysql_fetch_array($result);
$del_account_group_id = $row4['group'];
$del_account_email = $row4['email'];
$del_account_regKey = $row4['regKey'];
$del_account_iplog = $row4['iplog'];

// Delete account
$sql5 = "DELETE FROM dl_users WHERE username = '$del_account_username'";
$result5 = mysql_query($sql5) or die( mysql_error() );

//Change payment status
$sql5 = "update `paypal_sales` set `payment_status` = 'Refunded' where txn_id = '$txn_id'";
$result5 = mysql_query($sql5) or die( mysql_error() );

die;
}
if ($row['payment_status'] == 'Pending') {
// The payment has been pending.
//Change payment status
$sql6 = "update `paypal_sales` set `payment_status` = 'Refunded' where txn_id = '$txn_id'";
$result6 = mysql_query($sql5) or die( mysql_error() );
echo "the payment status is pending, changed is to Refunded";

die;
}
} else {
// The payment has been refunded but didn't exist in the database??
}
}

//generate the password
function createRandomPassword() {

$chars = "abcdefghijkmnopqrstuvwxyz023456789";
srand((double)microtime()*1000000);
$i = 0;
$pass = '' ;

while ($i <= 7) {
$num = rand() % 30;
$tmp = substr($chars, $num, 1);
$pass = $pass . $tmp;
$i++;
}

return $pass;

}

$password = createRandomPassword();
$password_encrypt = md5("$password");

//Add group ID
$group_id="4";

//Add user to the download system
mysql_query("INSERT INTO dl_users (username, password, `group`, email) VALUES('$payer_email', '$password_encrypt', '$group_id', '$payer_email') ")
or die(mysql_error());

//Add transaction and user details to the database
if ($row['txn_id'] == '') {
mysql_query("INSERT INTO paypal_sales (invoice, receiver_email, item_name, item_number, quantity, payment_status, pending_reason, payment_date, mc_gross, mc_fee, tax, mc_currency, txn_id, txn_type, first_name, last_name, address_street, address_city, address_state, address_zip, address_country, address_status, payer_email, payer_status, payment_type, notify_version, verify_sign, referrer_id, memo, for_auction, auction_buyer_id, auction_closing_date, auction_multi_item, account_username, account_password, `account_group`, account_email) VALUES('$invoice', '$receiver_email', '$item_name', '$item_number', '$quantity', '$payment_status', '$pending_reason', '$payment_date', '$mc_gross', '$mc_fee', '$tax', '$mc_currency', '$txn_id', '$txn_type', '$first_name', '$last_name', '$address_street', '$address_city', '$address_state', '$address_zip', '$address_country', '$address_status', '$payer_email', '$payer_status', '$payment_type', '$notify_version', '$verify_sign', '$referrer_id', '$memo', '$for_auction', '$auction_buyer_id', '$auction_closing_date', '$auction_multi_item', '$payer_email', '$password', '$group_id', '$payer_email') ")
or die(mysql_error());
}

//If it is an acution payment
if ($for_auction == "true"){

//Send welcome message (auction payment)
$to = $payer_email;
$subject = ': delivery information';
$message = "
Hello,
Congratulations! You have won one of our auctions. You can now login to our download system and download the files.
Download syst....
";
$headers = 'From: no-reply@test.com' . "\r\n" .
'Reply-To: test@gmail.com' . "\r\n" .
'X-Mailer: PHP/' . phpversion();

mail($to, $subject, $message, $headers);
unset($to, $subject, $message, $headers);

// Send standard payment message to
$to = $receiver_email;
$subject = ': Auction payment account created...';
$message = "
Account details:
Username: $payer_email
Password: $password ....
";
$headers = 'From: no-reply@test.com' . "\r\n" .
'Reply-To: test@gmail.com' . "\r\n" .
'X-Mailer: PHP/' . phpversion();

mail($to, $subject, $message, $headers);
unset($to, $subject, $message, $headers);

//Else it is a standard payment
} else {

//Send welcome message (standard payment)
$to = $payer_email;
$subject = ': delivery information';
$message = "
Hello,
Thank you for your purchase. You can now login to our download system and download the files.
Download system ....
";
$headers = 'From: no-reply@test.com' . "\r\n" .
'Reply-To: test@gmail.com' . "\r\n" .
'X-Mailer: PHP/' . phpversion();

mail($to, $subject, $message, $headers);
unset($to, $subject, $message, $headers);

// Send standard payment message to
$to = $receiver_email;
$subject = ' Standard payment account created...';
$message = "
Account details:
Username: $payer_email
Password: $password ...
";
$headers = 'From: no-reply@test.com' . "\r\n" .
'Reply-To: test@gmail.com' . "\r\n" .
'X-Mailer: PHP/' . phpversion();

mail($to, $subject, $message, $headers);
unset($to, $subject, $message, $headers);
}
}
else if ($payment_status == "Pending")
{
//Send pending message to buyer
$to = $payer_email;
$subject = ': Pending payment';
$message = "
Hello,
Your payment is pe....
";
$headers = 'From: no-reply@test.com' . "\r\n" .
'Reply-To: test@gmail.com' . "\r\n" .
'X-Mailer: PHP/' . phpversion();

mail($to, $subject, $message, $headers);
unset($to, $subject, $message, $headers);

// Send pending message to
$to = $receiver_email;
$subject = ': Pending PayPal Transaction...';
$message = "
Transaction details:
Pending reason: $pending_reason
Amount: $mc_gross $mc_currency ...
";
$headers = 'From: no-reply@test.com' . "\r\n" .
'Reply-To: test@gmail.com' . "\r\n" .
'X-Mailer: PHP/' . phpversion();

mail($to, $subject, $message, $headers);
unset($to, $subject, $message, $headers);
}

else if (strcmp ($res, "INVALID") == 0) {
// log for manual investigation
// Send invalid message to buyer
$to = $payer_email;
$subject = ': An error has occurred';
$message = "
Hello,
An error occur....
";
$headers = 'From: no-reply@test.com' . "\r\n" .
'Reply-To: test@gmail.com' . "\r\n" .
'X-Mailer: PHP/' . phpversion();

mail($to, $subject, $message, $headers);
unset($to, $subject, $message, $headers);

// Send invalid message to
$to = $receiver_email;
$subject = ': Invalid PayPal Transaction...';
$message = "
Transaction details:
An invalid transaction requires your attention
";
$headers = 'From: no-reply@test.com' . "\r\n" .
'Reply-To: test@gmail.com' . "\r\n" .
'X-Mailer: PHP/' . phpversion();

mail($to, $subject, $message, $headers);
unset($to, $subject, $message, $headers);
}
}
}
fclose ($fp);
}
?>[/code]

Best Regards
Oskar R

oskare100 · October 30, 2006

Hello again,
I really need help with this. Is there anything else I can post to make it easier for you to help?

Best Regards
Oskar R

Sign In

Want to check in the database if the txn_id has been used before

Recommended Posts

oskare100

Link to comment

Share on other sites

shocker-z

Link to comment

Share on other sites

oskare100

Link to comment

Share on other sites

oskare100

Link to comment

Share on other sites

oskare100

Link to comment

Share on other sites

oskare100

Link to comment

Share on other sites

oskare100

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information