CODEWITHSUNDEEP
phpmysqldatabasepaypalpaypal-ipn
user983248
user983248

Reputation: 2688

Paypal IPN - store data on database, update and delete

I'm using this example to store in the database the info coming from PayPal, my problem is that the database create two records for the same sale, one with the status of 'Pending' and one with the status 'Completed', both for the same sale.

How can I change this part of the code so if there is a sale with the same 'txn_id' I just update the 'payment_status' row OR delete the whole thing before update it with the new info. Note that there is two rows that will be different, 'payment_status' and 'createdtime' .

function updatePayments($data){ 
    global $link;
    if(is_array($data)){                
        $sql = mysql_query("INSERT INTO `payments` (txnid, payment_amount, payment_status, item_name, receiver_email, payer_email, custom, itemid, createdtime) VALUES (
                '".$data['txn_id']."' ,
                '".$data['payment_amount']."' ,
                '".$data['payment_status']."' ,
                '".$data['item_name']."' ,
                '".$data['receiver_email']."' ,
                '".$data['payer_email']."' ,
                '".$data['custom']."' ,
                '".$data['itemid']."' ,
                '".date("Y-m-d H:i:s")."' 
                )", $link);
    return mysql_insert_id($link);
    }
}

Database structure

CREATE TABLE IF NOT EXISTS `payments` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `txnid` varchar(20) NOT NULL,
  `payment_amount` decimal(7,2) NOT NULL,
  `payment_status` varchar(25) NOT NULL,
  `item_name` varchar(50) NOT NULL,
  `receiver_email` varchar(50) NOT NULL,
  `payer_email` varchar(50) NOT NULL,
  `custom` varchar(25) NOT NULL,
  `itemid` varchar(25) NOT NULL,
  `createdtime` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Upvotes: 0

Views: 2272

Answers (1)

eggyal
eggyal

Reputation: 125835

Create a UNIQUE INDEX on txn_id (if one doesn't exist already) and then use either INSERT ... ON DUPLICATE KEY UPDATE or REPLACE in place of your existing INSERT.

UPDATE

In your case, to add the unique index:

ALTER TABLE payments ADD UNIQUE INDEX (txnid);

Then, append to the end of your INSERT statement:

ON DUPLICATE KEY UPDATE payment_status = '".$data['payment_status']"'

If you want to delete the existing record and replace it with your new one, just change the word INSERT to REPLACE instead.

Upvotes: 4

Related Questions