Transactions over several pages

boatingcow

Hi Folks,

This is a topic that has discussed before, but I'm not conviced that the ideal solution has been found. Hopefully some of the more experienced users will have come across this before. Does anyone have any comments, pointers or code that would help explain this problem?

The aim of the game is to allow people to book/order a product or service whether by simple selection or a complex shopping cart system. Assuming that we have a satisfactory shopping cart system and a third-party payment partner to accept credit cards, the problem is in ensuring that the order process is consistent. I'll explain the problem in detail below.

Using the 1,2,3, analogy of online shopping, imagine customer #1 starts an order, booking 'n' amount of product 'x'...

Page 1
Customer #1 confirms his order and personal details

Page 2 (off-site 3rd party payment partner)
Customer #1 pays for his order

Page 3
Customer #1 receives confirmation of his order

Now imagine customer #2 starts looking at the availability of product 'x' while customer #1 is in the middle of paying for his order. We want to make sure that customer #2 sees only the actual number of product 'x' LESS the 'n' that customer #1 is in the process of buying. Otherwise, we risk letting customer #2 buy the same products as customer #1, or worse, if for example there are only 3 of 'x' and customers #1 and #2 both order 2, we will have an over-order.

Now, there may be people out there saying, "use a table where 'n' products are 'reserved' and customer #2 will see the reserved number deducted from the actual quantity. Then when the order is confirmed, delete the entry and decrement the inventory table by 'n'..." The problem here is that if customer #1 closes his browser or it crashes half way through, we are left with orphaned entries in the table.

Other people might shout, "simply run a CRON job or an admin page than cleans up orphan entries after a certain expire period..." The problem with that is customer #2 might want to order product 'x' after customer #1 bails out half way and cannot until the reserved number is liberated. Similarly, customer #1 might come back after a crash and see that he cannot order his product 'x' because there is effectively a lock on the reserved number until the orphan clean-up completes. Not very professional.

Still others might say, "what about using a session which keeps record of the reserved number of product 'x'. The session would be destroyed on the event of closing the browser or if it crashed..." The problem here is that customer #2 will not automatically see the number of products customer #1 has reserved.

Ideally, here is what we'd like.

Page 1
Customer #1 confirms his order and personal details
php-pseudo: $sql_1 = "INSERT INTO customer_orders ('order_status','product_ID','order_quantity',...etc) VALUES ('processing','x','n',...etc)"
Customer #1 presses 'proceed' to take hime to page 2

Page 2 (off-site 3rd party payment partner)
Customer #1 pays for his order using WorldPay, or similar
At this point customer #2 would only be able to order the actual quantity LESS the reserved quantity from the customer_orders table
WorldPay server redirects a succesful payment to our page 3a, or an unsuccesful one to 3b

Page 3a
Customer #1 receives confirmation of his order
php-pseudo: $sql_2 = "UPDATE customer_orders SET order_status = 'complete',...etc"
php-pseudo: $sql_3 = "UPDATE inventory SET quantity = ($actual_quantity - $order_quantity),...etc"

Page 3b
Customer #1 receives notification that his order was unsuccesful
php-pseudo: $sql_4 = "UPDATE customer_orders SET order_status = 'unsuccesful',...etc"

END

I see the main problem being in the event of the browser being closed or crashing, we'd like the equivalent of $sql_4 UPDATE statement to execute.

Now, any ideas?

Robbie

For previous threads, see:
Transactions over multiple php pages. How?
http://www.phpbuilder.com/board/showthread.php?s=&threadid=10259261

Transactions over several pages
http://www.phpbuilder.com/board/showthread.php?s=&threadid=10278923

Web Transactions in SSJS
http://developer.netscape.com/docs/technote/ssjs/webtrans/webtrans.html

melody

I have to tell you that I faced this problem a few months back when developing a site for a customer. The only viable answer is to flush the reserved to database and then clean up the orphans by way of a cron job that you describe. Since the web is stateless, there's no way to tell if contact has been lost with the browser. (i.e. in a browser crash and to free up those reserved products.)

The solution we came up with for the customer was a custom-coded Java applet that maintained contact with the server. If the browser crashed, the server would know about it and could free up the reserved items. That, however, is a big thing to implement (and pricey).

I'd suggest going with a cron job every 10 minutes to clean out 'orphaned' orders.

Sorry about that!

HTH
Melody

boatingcow

Thanks Melody,

I have spoken to a few people about the success of Java applets maintaining a connection which would then be destroyed on closing or crashing. CRON jobs unfortunately however, aren't always a possibility when running on a shared linux host, or more often, on a shared Windows box.

I understand the 'stateless' problem, and one way round the problem might be to use a small or 'invisible' frame in the document which meta-refreshes, or even a small flash movie that connects every so often, say every minute, or just before the session expires. (Thanks to Pete) This way, if a user pauses for a loo-break or a cup of tea, leaving the browser open will maintain the session. Should the browser be closed or crash, then the session should expire normally.

The only problem with this is maintaining the session through the departure to the third-party page...

Using frames with a third-party secure page will raise eyebrows when the security certificate doesn't match the domain.
Using a pop-up window raises eyebrows in terms of flow - it should be a 1,2,3 process, not a 1, ...1, 3 process.

Maybe someone has some experience of using Java applets, frames or Flash to overcome this problem?

Thanks again.

Robbie

Roger_Ramjet

Interesting problem.

As my pay service provider pointed out, you can not relie on getting a callback (your page 3A/😎, be it confirm or fail; it is the internet after all.

So for the past 3 years we have downloaded a daily transaction report from our payment provider and imported that into our backoffice systems. Fortunately, we are collecting money on overdue accounts not proccessing sales orders, so stock control, speed of proccessing, etc have never been an issue.

However, I am developing our next generation of systems and looking into our payment provider's SOAP & XMLRPC interfaces. As I understand it, these variously provide real-time validation and automated transaction reporting. Of course they also require that I use a secure server to host my transaction interface instead of redirecting the user to the 3rd party server and payment page.

I do believe that one or the other would answer your requirements, since both should allow you to maintain session state while processing the card transaction, receiving the callback, and then processing the order. I would recomend a timeout on callback, after which I would query the transaction report directly.

I cannot give you a detailed solution as I am still learning about it myself. Go to http://www.secpay.com/services.html to see for yourself, or ask your own payment provider about their available interfaces.