...

View Full Version : problem with insert and check for dupes



davehaz
04-27-2006, 04:33 AM
howdy,
I have written a script that checks to see if a transaction id is in the database, if it isn't, it is supposed to insert some data, it seems that the insert is happening first before the check. Here is the code I am working with.


<?
// read the post from PayPal system and add 'cmd'
$req = 'cmd=_notify-validate';
$file = fopen("/home/brightside/public_html/monitor/pp_file.txt",'w');
foreach ($_POST as $key => $value) {
$value = urlencode(stripslashes($value));
$req .= "&$key=$value";
}
// post back to PayPal system to validate
$header .= "POST /cgi-bin/webscr 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.sandbox.paypal.com', 80, $errno, $errstr, 30);

// 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'];
$subscr_id = $_POST['subscr_id'];
$receiver_email = $_POST['receiver_email'];
$payer_email = $_POST['payer_email'];
$last_name = $_POST['last_name'];
$txn_type = $_POST['txn_type'];
$residence_country = $_POST['residence_country'];
$amount = $_POST['amount3'];
$recurring = $_POST['recurring'];
$verify_sign = $_POST['verify_sign'];
$payer_status = $_POST['payer_status'];
$first_name = $_POST['first_name'];
$payer_id = $_POST['payer_id'];
$reattempt = $_POST['reattempt'];
$subscr_date=$_POST['subscr_date'];
$charset= $_POST['charset'];
$period = $_POST['period3'];
$mc_amount = $_POST['mc_amount3'];
foreach($_POST as $key => $value){
$str .= "$key=$value\r\n"; }
//fputs($file,$str);


if (!$fp) {
// HTTP ERROR
} else {
fputs ($fp, $header . $req);
while (!feof($fp)) {
$res = fgets ($fp, 1024);
if (strcmp ($res, "VERIFIED") == 0) {
$msg="string compare for VERIFIED works\r\n"; fputs($file,$msg);
//if ($payment_status == 'COMPLETED'){$msg="Payment status check passed $payment_status";fputs($file,$msg);} else {$msg="Payment status check failed $payment_status";fputs($file,$msg);}

// check that receiver_email is your Primary PayPal email
if($receiver_email != 'dave@davehaz.com'){$msg="email check didnt pass. $receiver_email\r\n";fputs($file,$msg);}
else {$msg="the email check passed. $receiver_email\r\n"; fputs($file,$msg);}
// check that payment_amount/payment_currency are correct
if($payment_currency == 'USD'){$msg="the currency check passed. $payment_currency\r\n"; fputs($file,$msg); }
else {$msg="the currency check failed. $payment_currency\r\n"; fputs($file,$msg);}
if($amount == '30.00'||$amount == '15.00'){$msg="the amount check passed. $amount\r\n"; fputs($file,$msg);}
else {$msg="the amount check failed. $amount\r\n"; fputs($file,$msg);}
// process payment
// check that txn_id has not been previously processed
$idck = "SELECT * FROM webmonitor WHERE subscr_id = '$subscr_id'";
$idres = mysql_query($idck);
$idrow = mysql_num_rows($idres);
if($idrow >= '1'){$msg="There is a Subscription ID duplicate with this transaction .$idrow \r\n";fputs($file,$msg);}
else {$msg="the subscription id check passed. $idrow\r\n";fputs($file,$msg);}
$insert = "INSERT INTO webmonitor (txn_type,
subscr_id,
last_name,
residence_country,
mc_currency,
item_name,
amount3,
recurring,
verify_sign,
payer_status,
payer_email,
first_name,
receiver_email,
payer_id,
reattempt,
item_number,
subscr_date,
period3,
mc_amount3) VALUES ('$txn_type',
'$subscr_id',
'$last_name',
'$residence_country',
'$payment_currency',
'$item_name',
'$amount',
'$recurring',
'$verify_sign',
'$payer_status',
'$payer_email',
'$first_name',
'$receiver_email',
'$payer_id',
'$reattempt',
'$item_number',
'$subscr_date',
'$period',
'$mc_amount')";
$inres = mysql_query($insert);
$inrow = mysql_affected_rows();
//fputs($file,$insert);
// working thru here
$errmsg = mysql_error();
if($inrow == '1'){$msg="\r\n inserting the data. $errmsg $inrow\r\n ";fputs($file,$msg);} else {$msg="\r\n problem inserting the data. $errmsg $inrow"; fputs($file,$msg);}
}
else if (strcmp ($res, "INVALID") == 0) {
// log for manual investigation
}
}
fclose ($fp);
}
fclose($file);
?>

would you please take a look and let me know what is going on, in the database, before I run the script I go in and delete the record I am working with so I know it isn't there, the insert is occurring though. and that is what is causing my check to fail.

thanks.

chump2877
04-27-2006, 04:50 AM
The insert appears to be occurring regardless of your duplication check...put the insert inside your conditional statement, like this, and see if it helps:


/// process payment
// check that txn_id has not been previously processed

$idck = "SELECT * FROM webmonitor WHERE subscr_id = '$subscr_id'";
$idres = mysql_query($idck);
$idrow = mysql_num_rows($idres);

if($idrow >= '1')
{
$msg="There is a Subscription ID duplicate with this transaction .$idrow \r\n";
fputs($file,$msg);
}
else
{
$msg="the subscription id check passed.$idrow\r\n";
fputs($file,$msg);

$insert = "INSERT INTO webmonitor (txn_type,
subscr_id,
last_name,
residence_country,
mc_currency,
item_name,
amount3,
recurring,
verify_sign,
payer_status,
payer_email,
first_name,
receiver_email,
payer_id,
reattempt,
item_number,
subscr_date,
period3,
mc_amount3) VALUES ('$txn_type',
'$subscr_id',
'$last_name',
'$residence_country',
'$payment_currency',
'$item_name',
'$amount',
'$recurring',
'$verify_sign',
'$payer_status',
'$payer_email',
'$first_name',
'$receiver_email',
'$payer_id',
'$reattempt',
'$item_number',
'$subscr_date',
'$period',
'$mc_amount')";
$inres = mysql_query($insert);
$inrow = mysql_affected_rows();

//fputs($file,$insert);
// working thru here
$errmsg = mysql_error();
if($inrow == '1')
{
$msg="\r\n inserting the data. $errmsg $inrow\r\n ";
fputs($file,$msg);
}
else
{
$msg="\r\n problem inserting the data. $errmsg $inrow"; fputs($file,$msg);
}

}

ralph l mayo
04-27-2006, 07:19 AM
If you make subscr_id a unique or primary key you can use something like "INSERT INTO table (fields) VALUES (values) ON DUPLICATE KEY UPDATE subscr_id = subscr_id" to make the check unnecessary. If there's a conflict it'll only set the id to the same value it had before, which is pretty close to doing nothing.

edit: You can still show an error if you want, I think the query returns 1 for a successful insert and 2 for a succesful update.

GJay
04-27-2006, 09:14 AM
as well, make sure you've set up a decent model in the database:
ALTER TABLE tablename ADD CONSTRAINT some_uniq_key UNIQUE('fieldname');

Then, if your error-checking falls down, you aren't going to be left with a messed up database, just perhaps a confusing message to the user.

davehaz
04-27-2006, 09:39 PM
thanks for the help,
I have just decided to make subscr_id a unique field, and eliminate the check.

I am having a serious problem with


$row = mysql_affected_rows();


let's say i have a simple insert such as


$insert = "INSERT INTO db (field1,field2,field3) VALUES ('$a','$b','$c')";
$inres = mysql_query($insert);
$inrow = mysql_affected_rows($inres);

the insert occurs, puts the right data in the right place but I cannot get $inrow to be a '1' , the way I have the code written above $inrow is empty. if I write the code as below $inrow is a '-1' even though the insert occurred.


$insert = "INSERT INTO db (field1,field2,field3) VALUES ('$a','$b','$c')";
$inres = mysql_query($insert);
$inrow = mysql_affected_rows();


what is the correct way to write mysql_affected_rows()?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum