Transaction processing advice

[Shelly]

I am looking for some advice on the design of a transaction processing
sequence. As I xcurrently have it, the sequence is as follows:

1 - Gather all the information from the user other than the actual charging
information.
2 - Present the user with a summary of what he is about to purchase
3 - Give him the choice of card (via PayPal) or by check.
4 - If by check, go to a screen that he has to click the "Pay" button.
5 - After the button is clicked, the data are entered into the database and
emails are sent to the customer and the seller.
6 - If by Paypal, he is taken to the PayPal screen where charge information
is collected and processed. A successful return comes back to a thank you
screen on my server where the data are entered into the database. PayPal
handles sending the emails to the customer and the seller.

My problem is this: What happens if there is a failure in connecting to the
database after paying via PayPal? I use the same code (an included file)
for the connection and the same code ( a different included file) for the
insertion.

Last night the seller tested a transaction and got:

*Warning*: mysql_pconnect( ): Can't connect to MySQL server on
'dbet.valueweb. net' (115) in
*/nfs/cust/0/43/11/711340/web/dbConnect.php* on line *6*
Can't connect to MySQL server on 'dbet.valueweb. net' (115)

Line 6 in dbConnect is:

$dbLogin = mysql_pconnect( $hostname, $username_db, $password_db) or
die(mysql_error ());

He sent me an email and then when I tried I succeeded. Not connecting when
paying by check is bad enough, tbut what about AFTER the customer has
already paid? That would be terrible.

So, my questions are:

1 - Other than an intermittentcy on the server, are there any ideas why this
would happen?

and much, much, more importantly:

2 - What would you reccommend to bulletproof this so that the customer is
protected and the transaction gets processed. One idea I have is rather
than "die", that I send an email to the seller with the insertion query so
that it can be manually inserted later.

Thanks for any help.

Shelly

[Rik]

Re: Transaction processing advice

Shelly <sheldonlg.news @asap-consult.comwrot e:

So, my questions are:
>
1 - Other than an intermittentcy on the server, are there any ideas why
this
would happen?

Do you have a very, very good reason to use mysql_pconnect( )? If not, just
use mysql_connect() ....

and much, much, more importantly:
>
2 - What would you reccommend to bulletproof this so that the customer is
protected and the transaction gets processed. One idea I have is rather
than "die", that I send an email to the seller with the insertion query
so
that it can be manually inserted later.

Your database should be reachable under all circumstances.. . If this is
not the case, you might consider different hosting. Not a solution, but
more of a temporary solution, would be to log it to a file, and run a
cronjob that checks the file every x minutes, and tries to enter the data
that couldn't be entered before. Do not bother your customers with extra
actions if your script/database/server fails... Get a better
script/database/server.
--
Rik Wasmus

[Shelly]

Re: Transaction processing advice

Now **that** was fast!

"Rik" <luiheidsgoeroe @hotmail.comwro te in message
news:op.toj8ks1 7qnv3q9@misant. ..

Shelly <sheldonlg.news @asap-consult.comwrot e:
>

>So, my questions are:
>>
>1 - Other than an intermittentcy on the server, are there any ideas why
>this
>would happen?

>
Do you have a very, very good reason to use mysql_pconnect( )? If not, just
use mysql_connect() ....

Habit!. That is how I learned. I'll change it.

>and much, much, more importantly:
>>
>2 - What would you reccommend to bulletproof this so that the customer is
>protected and the transaction gets processed. One idea I have is rather
>than "die", that I send an email to the seller with the insertion query
>so
>that it can be manually inserted later.

>
Your database should be reachable under all circumstances.. . If this is

I agree.

not the case, you might consider different hosting. Not a solution, but
more of a temporary solution, would be to log it to a file, and run a
cronjob that checks the file every x minutes, and tries to enter the data
that couldn't be entered before. Do not bother your customers with extra
actions if your script/database/server fails... Get a better
script/database/server.

That is what I thought. It is a problem at the server end and should never
happen. So, I think for the time being I will go with my solution. Rather
than "die", I will check that $dbLogin has been set and if not set, send the
info via email to the seller for later insertion. I don't have control of
the server as it is from a hosting company and I can't set up a cron job,
and changing it right now is not practical. This was the only no connect
that I have come across from this server.

Thanks for the info and validation of my thoughts.

Shelly

[Gordon Burditt]

Re: Transaction processing advice

>1 - Gather all the information from the user other than the actual charging

>information.
>2 - Present the user with a summary of what he is about to purchase
>3 - Give him the choice of card (via PayPal) or by check.
>4 - If by check, go to a screen that he has to click the "Pay" button.
>5 - After the button is clicked, the data are entered into the database and
>emails are sent to the customer and the seller.
>6 - If by Paypal, he is taken to the PayPal screen where charge information
>is collected and processed. A successful return comes back to a thank you
>screen on my server where the data are entered into the database. PayPal
>handles sending the emails to the customer and the seller.
>
>My problem is this: What happens if there is a failure in connecting to the
>database after paying via PayPal? I use the same code (an included file)
>for the connection and the same code ( a different included file) for the
>insertion.

You shouldn't get a failure in the database. If it happens often,
get a new host, or upgrade your hardware. But just in case the
database server UPS has 2 minutes less life than the web server
UPS, log the transaction ANY and EVERY way you possibly can, for
later manual recovery. Log it to a file. Send yourself email.
Send it to your pager. Leave yourself voice mail. If possible,
use smoke signals and/or mental telepathy.

>Last night the seller tested a transaction and got:
>
>*Warning*: mysql_pconnect( ): Can't connect to MySQL server on
>'dbet.valueweb .net' (115) in
>*/nfs/cust/0/43/11/711340/web/dbConnect.php* on line *6*
>Can't connect to MySQL server on 'dbet.valueweb. net' (115)

A seller shouldn't be getting warning messages from PHP (these can
be a security issue, plus it's unfriendly. Would you consider it
friendly for a human cashier, when I gave her a Susan B. Anthony
dollar, then told her it wasn't a quarter, to say "Huh?", look at
the "quarter", repeat "what the hell" a few times, mumbled something
about counterfeit money, and go catatonic for 5 minutes until I got
another three cashiers to carry her off to someplace she could lie
down? Yes, that really happened. Not only to me, either. At
Wal-mart you can get the police called on you for trying to spend
a $2 bill. Anyway, your web site is being equally unfriendly. ).

Also, use of pconnect() is an invitation to run the server out of
allowable connections while leaving a huge number of perfectly good
ones idle.

>Line 6 in dbConnect is:
>
>$dbLogin = mysql_pconnect( $hostname, $username_db, $password_db) or
>die(mysql_erro r());

You should give an intelligible error to the CUSTOMER - like telling
them there was a problem and asking them to call customer service
and maybe warning them not to retry the order because of possible
duplicate billing. If you must spew technogeek babble to the
browser, tell the customer to print the page, have it available
when they call customer service, and ask the customer to spell it
to you when they call.

>He sent me an email and then when I tried I succeeded. Not connecting when
>paying by check is bad enough, tbut what about AFTER the customer has
>already paid? That would be terrible.

>So, my questions are:
>
>1 - Other than an intermittentcy on the server, are there any ideas why this
>would happen?

Fat-fingered admin leaned on the reset button or tripped over the power cord.
You have a problem with the number of allowable database connections (don't
use mysql_pconnect( )).

>and much, much, more importantly:

>2 - What would you reccommend to bulletproof this so that the customer is
>protected and the transaction gets processed. One idea I have is rather
>than "die", that I send an email to the seller with the insertion query so
>that it can be manually inserted later.

Also log it in a file in case the web server doesn't stay up long enough
for that email to get off.

Sometimes it is worthwhile to wait a few seconds and retry the connection
ONCE or TWICE. Do not retry indefinitely. The user's browser will time
out waiting, and you'll just ensure that if the database gets overloaded,
the web server does also. If you put in retry logic, log even SUCCESSFUL
retries. If you're constantly having to retry, something is wrong, look
at it and fix it before the problem gets intolerable for the customers.

[Paul Furman]

Re: Transaction processing advice

Shelly wrote:

I am looking for some advice on the design of a transaction processing
sequence. As I xcurrently have it, the sequence is as follows:
>
1 - Gather all the information from the user other than the actual charging
information.
2 - Present the user with a summary of what he is about to purchase
3 - Give him the choice of card (via PayPal) or by check.
4 - If by check, go to a screen that he has to click the "Pay" button.
5 - After the button is clicked, the data are entered into the database and
emails are sent to the customer and the seller.
6 - If by Paypal, he is taken to the PayPal screen where charge information
is collected and processed. A successful return comes back to a thank you
screen on my server where the data are entered into the database. PayPal
handles sending the emails to the customer and the seller.

I set up something similar with the CC server and my server sending
emails on confirmation, at least send yourself an email so you can see
them come in pairs & know it all worked.

I suppose the other thing would be to log something in your system, that
would also catch a situation where someone's credit card wasn't approved
or they had too much hassle with paypal & gave up.

My problem is this: What happens if there is a failure in connecting to the
database after paying via PayPal? I use the same code (an included file)
for the connection and the same code ( a different included file) for the
insertion.
>
Last night the seller tested a transaction and got:
>
*Warning*: mysql_pconnect( ): Can't connect to MySQL server on
'dbet.valueweb. net' (115) in
*/nfs/cust/0/43/11/711340/web/dbConnect.php* on line *6*
Can't connect to MySQL server on 'dbet.valueweb. net' (115)

Ack, yes, as mentioned, turn off all error reporting. I once got an
error similar to that where it spit out the admin password in the error
message!

He sent me an email and then when I tried I succeeded. Not connecting when
paying by check is bad enough, tbut what about AFTER the customer has
already paid? That would be terrible.