PHP Shopping Cart - When to reduce item quantity in the stock table?

Hi,

I have generated an online shopping cart to be used on my website.  The cart content is stored in an Associated Array stored in a PHP session variable.

I have opted to use Worldpay to handle all credit card transactions.

My question is: at what stage of the shopping process should I update the quantity fields in the stock table to reflect the purchase?

Obviously I do not want to do this before payment has been taken for obvious reasons...

Worldpay offer a callback service so that a script on my website can be run after a successful transaction has been processed.  What I plan to do is write the order information to a partially completed table in the database before the credit card transaction takes place.  Then, when Worldpay issue the callback, information will be deleted from the partial orders table, written to a completed orders table and the quantity fields in the stock table updated accordingly.

If the callback fails, I will still have the basket ID of the order, thus emabling me to manually update the stock table with the information held in the partial orders table.

Please can anyone give their thoughts / opinions of this method?  The advice I have received from these pages in the past has been invaluable.

Thanks


 
rvr_1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

minichickenCommented:
Hi rvr_1

I think you are on the right track, as I also recently created a e-commerce site with shooping functionalities. You method is almost the same is the way I did it.

This is how it goes:
1.) I have 3 DB tables just for the purchase process, namely CART (stores items in cart - just a temp storage of the items that will be purchased - if transaction was successful then move all items to PURCHASED else delete them all), ORDER (stores the order details), PURCHASED (stores the items that are purchased - permanently - if transaction was successful).

2.) Both CART and PURCHASED has ORDER_ID as the foreign key in the table. As 1 ORDER can have many CART items and 1 ORDER can have many PURCHASED items.

3.) So when the user is still in his shopping process, all the items will be added or removed from his shopping cart, so there will be items get inserted into CART and deleted from CART.

4.) Just before the user checks out and get redirected to the payment gateway, an ORDER number or ID is generated and passed to the payment gateway in order to get passed back from the CALLback.

5.) So now, getting back to your site with the Callback, If the transaction was successful then insert every item with the ORDER number or ID that was passed back from CART to PURCHASED. Then delete everything in CART where the Order ID = the order number from call back. Of course only successful, then you update your STOCK table.

6.) If the transaction failed, then just delete everything from CART with the appropriate ORDER id, or you can give the user the option to checkout again. You wont need to update your STOCK table if unsuccessful.

Just some of my thoughts.... hope you find it useful...
0
hernst42Commented:
I think you should update the stock just before you submit the information to check the creditcard. Else it might be possible that you run out of itmes for another customer which purchase is also currently in progress.
You may also have a table where you have those elements stored that are curently checked. If a user request an element that is out of stock, but some itmes are in the transit que you might send him a note to check again.

If the check of the credit card fails, readd the quantities to the stock and remove the numbers from the trnasit-queue.

It depends on your attitude. Sells some items less, but the customer gets everything he wanted directly or tell him, after he purchased that some items may take a longer time to deliver. I thing the longterm success will be with the 1st attitude.
0
rvr_1Author Commented:
Thanks for your comments - I found them really useful :o)

Just one thought regarding security - what process have you used to stop a hacker bypassing the Payment gateway.

I.E. If a hacker is able to obtain the Order ID that is passed to the payment gateway - what is to stop them just calling the script that handles the callback and passing the Order ID to this script - thus simulating a completed order without payment being taken?

Are PHP Session variables able to hold their value throughout the payment gateway process so that they can be used for verification in the PHP callback script?

Many Thanks!
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

frugleCommented:
First of all, the script that handles the callback should check that the information that is being submitted is actually coming from the payment gateway.

The hacker probably won't know exactly which variables are being passed back to the server - iirc worldpay has an account passphrase that it uses to signal a completed transaction which is customised in your worldpay account. If this is not received you have to assume that the process has been interrupted and flag it for manual inspection.

Regarding sessions, I'm not sure but I think that once you leave your domain your session ends... check http://uk2.php.net/manual/en/ref.session.php to be certain.

Mike
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
minichickenCommented:
Normally, when you submits info to the payment gateway, there should be a checksum that is calculated from your WorldPay Vendor ID with total amount and other variables. This is verified at the payment gateway. Again once the payment process is done, the payment gateway should then generate another checksum from other variables and get sent back to your site with the CallBack. On you site you then should check the checksum values to see if they match up. The reason for the checksum is that if there was an interupt by a hacker, the values of the CallBack will change and therefore will result a different checksum value and hence will not match the initial checksum, which is invalid and you will know that there is an interupt.

Regarding sessions>> the session will expire if not instantly in a few seconds or minutes once you leave your domain.... but I think you can config that on the setup on your web server, but I would recommend to keep session alive once the user has left the domain, due to security reasons.

0
rvr_1Author Commented:
Thanks - invaluable advice as always.

You mentioned in your post about calculating the checksum values on my site.

Could you please give me a little more information about how this is achieved?

Many Thanks!
0
minichickenCommented:
Hi rvr_1

I don't know if WorldPay already supports the checksum security, if they do then you dont have to worry about it and just follow their routine.
You might want to look at the Zend articles on the md5 algorithm for checksum, if WorldPay does not support the checksum routine.
The article has 3 parts, pretty comprehensive, with sample codes and explains why HTTP data transfer is not secure and how to apply the md5 algorithm.

http://www.zend.com/zend/spotlight/securevariablepart1.php
http://www.zend.com/zend/spotlight/securevariablepart2.php
http://www.zend.com/zend/spotlight/securevariablepart3.php

Hope you find it usefule....
0
minichickenCommented:
I recommend {minichicken} as accepted answer and {frugle} as assisting answer.

Thanks :)
0
frugleCommented:
you would!  :-)

I agree.

Mike
0
hernst42Commented:
my recommendation:
    Split: minichicken {http:#12434109} & hernst42 {http:#12434168} & frugle {http:#12439742}
0
minichickenCommented:
oh... sorry hernst42 , I missed your comment in this question.

so my recommendation is -> Split: minichicken {http:#12434109} & hernst42 {http:#12434168} & frugle {http:#12439742}
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.