Want to check in the database if the txn_id has been used be
Moderator: General Moderators
Want to check in the database if the txn_id has been used be
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
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
Code: Select all
$result = mysql_query("SELECT txn_id FROM paypal_sales WHERE txn_id = '$_POST['txn_id']'");
if(!$result) {
echo 'The txn_id does not exist';
} else {
echo 'The txn_id exsists';
}
Last edited by ianhull on Sun Oct 29, 2006 12:16 pm, edited 1 time in total.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
I don't know your exact table structure, so I may be off a bit
First you want to check if the id exists in the database, correct? This is assuming the id is set via ?id=3423423423 and is strictly numeric
First you want to check if the id exists in the database, correct? This is assuming the id is set via ?id=3423423423 and is strictly numeric
Code: Select all
$id = intval($_GET['id']);
$result = mysql_query("SELECT txn_id FROM paypal_sales WHERE `txd_id` = $id") or die(mysql_error());
//id already exists
if (mysql_num_rows($result) > 0)
{
// run update where .. with WHERE `txd_id` = '. $id
}
else
{
// insert new transaction
}Hello,
Thanks, I think it helped, I've now done like this:
But 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
Thanks, I think it helped, I've now done like this:
Code: Select all
<?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
}
}
?>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
feyd | Please use
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:
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
feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Hello,
After a tip I changed the code to:Code: Select all
<?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: Select all
--
-- 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 ;Best Regards
Oskar R
feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]Hello,
OK, I got that working now in a seperate test script.
Here is what I added to the main script:
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:
Best Regards
Oskar R
OK, I got that working now in a seperate test script.
Here is what I added to the main script:
Code: Select all
//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??
}
}Here is the "main" script so far:
Code: Select all
<?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);
}
?>Oskar R