Associate value with primary key

[four0four]

How can I associate the value from one column with a primary key?

For example, I'm using the e-mail address for each user as the user name and primary key. These user names are stored in the "username" column.

Each user has a unique transaction ID for their subscription order stored in the "trans_id" column.

How can I search for a transction ID and associate it with the main username, and then update that row by changing a column called "paid" to a value called "yes"?

Thanks! I appreciate the help!

[Old Pedant]

I give up, why do you *need* to "associate" the UNIQUE trans_id with anything, just to do such an update????


UPDATE table SET paid = 'yes' WHERE trans_id = [some unique value];

What's wrong with that???

If you then want to *report* the username/email address--maybe in order to send an acknowledgement email?--then you could follow it up with
SELECT email_address FROM table WHERE trans_id = [that same unique value];

But I don't see why the two actions need to be associated with each other, at all.

[oracleguy]

Your transaction table would just have a column for the user ID. And that column would be linked to the primary key in the users table. That is how you would link a transaction to a user.

[four0four]

I give up, why do you *need* to "associate" the UNIQUE trans_id with anything, just to do such an update????


UPDATE table SET paid = 'yes' WHERE trans_id = [some unique value];

What's wrong with that???

If you then want to *report* the username/email address--maybe in order to send an acknowledgement email?--then you could follow it up with
SELECT email_address FROM table WHERE trans_id = [that same unique value];

But I don't see why the two actions need to be associated with each other, at all.

Well, the Paypal IPN sends back a unique transaction ID to my script indicating the status of a transaction. If a subscription is cancelled over a period of time, or if the payment fails, I need to change the status of a user.

Therefore, I'm using the transaction ID that PayPal generates at the time of purchase to associate with each user. Otherwise if PayPal sends a notification to my script with the transaction ID, I have no way of knowing which user's status should be changed.

Your transaction table would just have a column for the user ID. And that column would be linked to the primary key in the users table. That is how you would link a transaction to a user.

I see, so I need to create an additional table just for transactions?

Is there a way to accomplish this with just one table?

[Old Pedant]

For this kind/purpose of transaction id, no reason you need it in a separate table.

oracleguy was assuming that each user might have multiple transactions on record. If you only keep one per user, then you are fine.

[four0four]

For this kind/purpose of transaction id, no reason you need it in a separate table.

oracleguy was assuming that each user might have multiple transactions on record. If you only keep one per user, then you are fine.

Ok, so I only need 1 table then. That simplifies things.

As oracleguy suggested, how do I link the "trans_id" column with my primary key?

All I have to work with is the transaction ID that PayPal sends to my script, so I need to get that transaction ID and update the user record it's associated with.

I've been Googling for info on how to do this for days now and haven't found anything.

Thanks again!

[Old Pedant]

I think my head hurts.

Don't *YOU* specify the transaction id to PAYPAL???

You must, otherwise how would you possibly know which of your payment request methods PayPal is responding to?

So you simply store the trans_id in the DB *BEFORE* calling PayPal, so that it's there when PayPal comes back with the confirmation.

Nothing else makes sense to me.

[four0four]

I think my head hurts.

Don't *YOU* specify the transaction id to PAYPAL???

You must, otherwise how would you possibly know which of your payment request methods PayPal is responding to?

So you simply store the trans_id in the DB *BEFORE* calling PayPal, so that it's there when PayPal comes back with the confirmation.

Nothing else makes sense to me.

I know, it's confusing, but I'll try to explain it better...

Sure, I could generate my own custom transaction ID, but if a user registers for a payed subscription and then cancels the subscription two months from now, or the payment fails, PayPal will send a notification to my script.

Therefore, my script always needs to be waiting (listening) for payment notifications indicating whether a subscription was cancelled or if the payment failed.

If the script suddenly receives a notification that the user cancelled their subscription, I need to update their account status accordingly.

Here's some info on the PayPal IPN: https://www.paypal.com/ipn

[Old Pedant]

Okay, so *YOU* have to have some way to associate *YOUR* information in the DB with the information that PayPal sends you. The transaction id that PayPal talks about is just an arbitrary number and all you need to do is make sure you don't process the same transaction id twice. I don't see why you want to associate a transaction id with a single person; I think you just want to keep a list of transaction ids that PayPal has already sent you and ignore any duplicate you receive. After all, suppose that you get *TWO* LEGITIMATE transcactions for the same user (perhaps initial payment and then renewal?). You don't *want* to store a single transaction id for that user.

And the transaction id isn't going to help you *find* the right user, as I read the API. It's just an arbitrary non-repeating value.

You need to find your user by the email address:
payer_email =

Or, *IF* you implement it, with the value of the custom field:
custom =

Am I still missing something??

[four0four]

Okay, so *YOU* have to have some way to associate *YOUR* information in the DB with the information that PayPal sends you. The transaction id that PayPal talks about is just an arbitrary number and all you need to do is make sure you don't process the same transaction id twice. I don't see why you want to associate a transaction id with a single person; I think you just want to keep a list of transaction ids that PayPal has already sent you and ignore any duplicate you receive. After all, suppose that you get *TWO* LEGITIMATE transcactions for the same user (perhaps initial payment and then renewal?). You don't *want* to store a single transaction id for that user.

And the transaction id isn't going to help you *find* the right user, as I read the API. It's just an arbitrary non-repeating value.

You need to find your user by the email address:
payer_email =

Or, *IF* you implement it, with the value of the custom field:
custom =

Am I still missing something??

Yes, that's exactly it! :)

I was considering using the payer's e-mail to associate the transaction, but what if they have a separate e-mail address just for PayPal? I suppose during user registration I could ask for their e-mail address and then have them specify their PayPal e-mail address if it varies from their main one?

I guess the only reliable solution is to use a custom field, as you suggested. That way I can generate my own transaction ID, store it in the DB, and then send it to PayPal to associate with each user, right?

So back to my original problem:

How would I associate it?

This is what I have so far:


$query = "SELECT username, txnid, paid FROM users WHERE txnid = '$info'";


$info would be the custom field that PayPal sends to my script.

But then I need to get the username from that row and change the "paid" column to "yes".

[Old Pedant]

See my post #2.

FWIW, I would probably use a GUID for that custom value. If not that, then some simple encryption of the userid. You could even use the bare userid.

I agree completely with your concern that they might use a different email address for paypal than for you. Good catch. I missed that one completely. So, yes, you *need* the custom value sent from you to paypal and then back to you.

[four0four]

See my post #2.

FWIW, I would probably use a GUID for that custom value. If not that, then some simple encryption of the userid. You could even use the bare userid.

I agree completely with your concern that they might use a different email address for paypal than for you. Good catch. I missed that one completely. So, yes, you *need* the custom value sent from you to paypal and then back to you.

Thanks for bearing with me! I really appreciate your help. :)