Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Apr 2005
    Posts
    388
    Thanks
    0
    Thanked 0 Times in 0 Posts

    problem with insert and check for dupes

    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.
    PHP Code:
    <?
    // 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$errstr30);

    // 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 ($fp1024);
    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.

  • #2
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,827
    Thanks
    19
    Thanked 157 Times in 148 Posts
    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:
    PHP Code:
    /// 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);
    }
        

    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :-)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!
    ♪♪ …Need Web Hosting For My YouTube-To-Mp3 Conversion Software? Check Here !!… ♪♪

  • #3
    Regular Coder ralph l mayo's Avatar
    Join Date
    Nov 2005
    Posts
    951
    Thanks
    1
    Thanked 31 Times in 29 Posts
    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.

  • #4
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    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.

  • #5
    Regular Coder
    Join Date
    Apr 2005
    Posts
    388
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Code:
    $row =  mysql_affected_rows();
    let's say i have a simple insert such as
    PHP Code:
    $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.
    PHP Code:
    $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()?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •