Yes, a database would help here. The issue is that you need to couple the display of products with their actual (or virtual) stock level. If you sell exclusively from the Web, then this is less complex than if you have a mix of online and physical sales, but it still requires a stock-keeping step. Every time you get new inventory, you need to increment the stock in hand, and every time your cart takes a sale, you have to decrement that same counter.
Depending on how your stock works, you can approach this one of two ways (probably more). First, if you’re selling one-of-a-kind handmade things, like Etsy does, you don’t have SKUs, you have individual Products. For each product you have to sell, there is a single row in a database table and when it’s sold, it’s taken out of inventory forever. For the second, if you’re selling mass-produced items that you stock in bulk, then you have one row in the database for each SKU (product type) and in that row you have an integer number of items in hand.
As far as making your inventory control your store goes, let’s assume you have the second option here. You make a table of products like this:
id | name | description | price | quantity
1 | oven mitts | Get a grip on hot things! | $10.00 | 245
2 | salsa | Spice up your next party! | $3.00 | 3651
3 | zonda | Don’t speak Italian? You will… | $300000 | 1
I like to use an Object-Relational Mapper (ORM) called MyActiveRecord to simplify connecting a MySQL database table with PHP code. I didn’t write it, but I am the maintainer of the library, and you can download it from my GitHub page: walterdavis (Walter Lee Davis) · GitHub What this does is abstract away all of the getting and setting code, so instead of writing a bunch of cryptic code, you get to do something more high-level. This, for example, will probably work, given a correct installation of MAR:
The first block of code “wraps” the database table, and gives us the handy “sell” function, which will only allow sales of products that are in stock.
The second is a complete form processor for the ultra-simplistic order form that follows.
The third is a little utility function that will show the user-entered quantity ordered if the form is submitted to itself and an error is generated.
The fourth is the entire form, generated in a loop over all products in the table. Because so much of the complexity of talking to the database is hidden away in the ORM, the first line encompasses ten or so lines of vanilla PHP/MySQL code, and returns a predictable array of products that have a positive inventory, in alphabetical order. The second starts the form code, the third starts the loop over the array of products. The next line makes one list item per product, with the name, price, and description, plus an order quantity field. The last line closes up the HTML structure and shows the form to the browser.
So the point of all this is as follows: the database keeps track of how many of each item is available at all times, and so when you sell one, that total is reduced by one. When you run out of product, it gets removed from the list of things for sale. Since the database generates the form, it won’t ever show anything that can’t be sold.
Now all of this comes back to another issue – race conditions. If you are lucky enough to be very popular on the Web, there’s a good chance that two people will decide to buy the same limited-quantity item. Let’s say they’re both sitting there fishing around for a credit card with the form otherwise filled in. The first one to place her order wins the “race”. The second one is sitting there with a stale form – when she loaded the page, the server had one to sell, but when she saves, she’ll get an error. This can get incredibly complex to solve. Many stores manage customer expectations by showing “limited stock” flags next to products that are below a certain limit.
So all this to say, yes, you are on the right track to solve this. You’ll need to track inventory, and I’ve left out the whole purchase side of things here – only decrementing stock if the payment goes through. Hopefully this gives you some ideas, anyway.
Walter
On Jan 3, 2013, at 7:42 AM, David Murray wrote:
So, how to proceed? Would it be possible to implement a database on an online server say, (and this is where my skills are very shaky) that would be able to reflect live on the site actual stock level and thereby make it impossible for a customer to order an out of stock item? Ideally I would still like to use Tim’s actions but can appreciate how this might be tricky. My domains are hosted with David Owen’sineedwebhosting.co.uk so available to me are the likes of oscommerce and zencart but I’m not sure or confident on how to integrate this with the existing design style and within Freeway (Pro).
offtopic mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options