Want to check in the database if the txn_id has been used before
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.
<?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'] == '') {
//it doesn't exist
}
else {
if ($row['payment_status'] == 'pending') {
//it does exist but is pending ...
}
}
?>
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).
<?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";
}
}
?>
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.
i think txd_id should be txn_id, as there is no column named txd_id
A field is the same as a column. It's just a stupid idea of saying columns instead of fields. Although it makes sence: when you take a look at your table, you will see rows and columns, so it's not hard to find what it is.
This was rather easy to see
UnrealEd
__________________
"Good judgement comes from experience, and experience comes from bad judgement." - Fred Brooks
Hello,
OK, I got that working now in a seperate test script.
Here is what I added to the main script:
PHP Code:
//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??
}
}
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:
PHP Code:
<?php
// read the post from PayPal system and add 'cmd'
$req = 'cmd=_notify-validate';
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() {
//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());
//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();
//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();
Linear Mode
