Keeping track of "Inventory"

[paruby]

I have items in my DB as inventory.  The table has 2 main fields for this, a total inventory # (per item), and a "remaining" field that will count down as the item is ordered.  My problem is keeping track of the "remaining" number.  There are two ways on the web page a user can change the # of an item they want, by entering a number in a form field which they can only enter a number up to the current # of items left at the time they load the page, and by using up and down arrows.  Each click of the arrow is an addition or deduction of 1 item.  In this case, I can keep track of how many are left, because I know it is always +1 or -1.  The issue is with the manually entered field.  For example, if item1 has 5 left, and a user types in 3, I can easily just subtract 3 from the inventory, and get 2.  But, if the user then types in 4 for item1, I do not know that the last value they entered was 3, to get the difference of 1, and subtract 1 more from inventory, nor do I know that when they first started, there were 5 left, and subtract 4 to get 1.  This gets even more confusing if there are 2 users trying to get the same item...

 

This must be a common thing, but again, I cannot get it figured out...

 

In advance, thank you to all who have replied and helped on my other posts lately.  These questions all revolve around the same site I am building, and have maybe bit off more than I can chew at a reasonable pace...

 

Pete

[Psycho]

I'm not really following your scenarios. Are you trying to prevent the user from ordering too much before they submit the page (javascript) or after they sybmit the page (PHP)? In either case the solution is pretty simple.

 

If you want to prevent the user from submitting a request for too many items then when the form page loads you need to query the current available quantity and store that as a JS variable. Then, when the user attempts to submit the page run a validation function comparing the ordered quantity to the available quantity that you saved when the page loaded.

 

On the receiving page you would do basically the same thing but do it all on the server-side.

 

I guess I am having a problem understanding what you are trying to explain here

For example, if item1 has 5 left, and a user types in 3, I can easily just subtract 3 from the inventory, and get 2.  But, if the user then types in 4 for item1, I do not know that the last value they entered was 3...

Are you taking about a 2nd order? If so, what does it matter that they ordered 3 in the last submission? the available quantity should have been updated before the 2nd submission.

 

And, as for the issue with multiple users, there really isn't a foolproof method - which is why you would have to validate the available quantity in PHP anyway. Basically, the first person to order wins. If there is 1 unit left of a product and user A and user B have the order page open at the same time, whoever places their order first should "win".

[paruby]

Thank you for the reply mjdamato. 

 

ibelieve I have the first part done, not to have the user enter a number of items greater than what is currently available. 

 

For the part in question, I looking specifically at the case where the user will enter "3" for the item, the code will immediately run to subtract the inventory from 5 to 3, then they change their mind, and want to order 4, and change the value in the field to 4.  The DB already has 2 available, and since it is running as AJAX, my number available is still 5, and allows them to change from 3 to 4.

[Psycho]

I would never advise using AJAX for this, but it's your application. I would always have the user do an explicit submission before doing ANYTHING with quantity. What about the situation where someone enters in an amount equal to all the remaining quantity but never submits the order? No one would be able to order that product.

 

As for your question though. If a user enters in a value and you immediately subtract the available amount, then the user changes the amount, you will have to keep track of what the user previously had entered into the field. I see two options:

 

1) you can keep track of this in the JavaScript code. When the user changes the value you need to pass the previously entered value in the AJAX call so you know how to do the calculation in the PHP code

 

2) You can keep track of the value in PHP. If the user has entered a quantity and you are subtracting it from the available quantity in the DB then you should have a corresponding record in the DB to account for it. But, since the user has not officially ordered the product I don't know where you would keep track of it. That is just one reason why I think what you are trying to do is too problematic to consider. An even more significant problem is if the user enters a value, you subtract from the available quantity, and then the user closes their browser without placing the order. Now, you have a reduced available quantity where no order was placed. Even if you do move the quantity to some temporary order table you would have no way to know when the user closes their browser to move the quantity back to the available count.

 

This is just a bad concept on multiple levels (IMHO). Good luck.

[PFMaBiSmAd]

You should not simply keep a count of the items in your inventory. You should set this up as a balance account, where you insert a separate row for each addition/subtraction. When you receive stock, you insert a row with the item id, the quantity, date received, and your purchase order number. When someone purchases an item, you insert a row with the item id, the quantity (as a negative number), date ordered, and the order number (which ties the information back to who ordered it and what the status is of each item.) To get the current balance or any or all items, you simply group by the item id and do a SUM() of the quantity column.

 

To expand on the above, if someone selects a quantity of an item you can insert a row for them for that item and the quantity, but have a status column that indicates the order is pending and the quantity is 'allocated' against a possible order. If they adjust the quantity in their row or remove that item, you just need to alter their row for that item. When they place the order, you change the status to 'ordered'. If they don't complete the order, you can remove their rows after a time.

[paruby]

Thank you both for the help.  I believe I can get to what I am envisioning from here.

 

Pete