reserving a product in an Ecommerce website

[bnashenas1984]

Hi everyone
I'm sure there are many open source Ecommerce scripts out there but because of some reasons i'm building my own.

I'm having a problem with reserving products.

Here's what I need :
when the administrator of this webshop adds a new product he also need to choose the quantity of that product available in stock which is not so hard to do with PHP and MySQL. Each time a customer purchases a product the script will decrease the quantity in database and when all of them are purchased then the product will no longer be available on the website. But the problem occurs when several customers buy the same product at the same time.

Lets say there's a product with the quantity = 1 . what happens when 2 customers buy it at the same time? I mean there should be a way to reserve that product when ever a user adds it to his shopping cart.

I could come up with some ways to do this job, Like decreasing the quantity when a user adds that product to his shoping cart and increasing it when he cancels the payment. But these ways generate a high trafik to MySQL and this way might not be so safe. because if the user closes the browser without canceling the payment then the quantity will not be increased.

Any idea will be appreciated
Thanks

[TheServant]

I don't think that the MySQL traffic will be overrun with updating a value when it's selected. I think you will need to add a few things because not only do you need to update the stock, if that's what you change, but you need to see if a user session has timed out.
So if you have three tables: Stock, Cart and User...

Stock columns: item_id, item_name, item_quantity
Cart columns: cart_id, user_id, item_id, item_quantity
User columns: user_id, user_name, last_activity

This means that everytime a user clicks on something (navigates) you need to update their last activity witha time stamp. So if a user doesn't move for say 30mins, then you can free whatever he has in his cart.

Now the tricky part is how often, or how you want to check if their activity has been longer than say 30mins. You could run a cron job every hour, or 30 mins which looks for any expired sessions and systematically checks carts to see if the user is still logged in (check activity has not expired) with some table JOINS and then release that stock.

You are probably thinking that this is a longa nd resource consuming way about it, and it could be if you run your crons *too* often, but the major problem is when a browser is closed it does not send a note to all the website it's visited to say, "I'm logging off", so the only real way to do it is with databases or sessions.

Having said that, there could be a way to attach a session_id to a the cart to avoid having to store last activity in the database, and simply check if the session is still considered active, or still exists. I haven;t done this before, so I can't comment, but I imagine it has it's own flaws as well.

MySQL is very good, so don't be scared to use it for resource reasons. You could cause issues, but a couple MySQL queries will not break the bank.
If you had a cron job running every 5mins, updating 10,000 rows 6 times, then we might advise a different route, but just try use it and if it's too resource consuming then think of a different way. Most things can be optimized, but you have to start somewhere.

[Atli]

Hey.

How do you store the shopping cart for your customers?

If you were to store the cart itself inside the database, you could count how many units of a particular item are currently in people's carts and reduce the available amount by that amount.

And you could have the carts "timeout" by adding a column that keeps track of when the user was last active, and only count items in carts that were active within a given time frame.

[bnashenas1984]

Thank you TheServant and Atli for the answers.I realy appreciate it.

As I understand i need to have some tables to keep users activity and their reserved products.

I will work on it and if there is any problem i'll post another question.

Thanks again

[TheServant]

Glad we could help. That's the logic that I would start with, but come back with your code and we can try and help you optimize that.