[RESOLVED] Stock control

[buffy]

Hi everyone,

I was hoping I could get some ideas/suggestions on stock control.

Say I have 5 of an item in stock, if a customer selects 6 on the product page, when it goes through my "add item to cart" code, I also need it to:

1) Check the amount added against the stock level
2) If its more then the stock level, put the max amount available (cart quantity)
3) Display message to customer, something like "Quantity selected exceeds maximum allowed amount."

Then later on, when they send the order I guess I submit the stock then.

Does that make sense? Is there a better way to do it?

This doesn't seem to hard, its just working it in with my current script.
I had a lot of help from kows and I dont' want to break it ;)

(I have a field called stockamount in the product table)

Code (some code changed):

<?php

if(isset($_REQUEST['productid'], $_REQUEST['quantity']) && is_numeric($_REQUEST['productid']) && is_numeric($_REQUEST['quantity'])){
//product id is set, check if there is currently an order for this person
if(isset($_SESSION['OrderID']) && $_SESSION['OrderID'] > 0){
//already has an order, so we either add or update the order

//first check if this product exists on this person's order
extract(mysql_fetch_assoc(mysql_query("SELECT COUNT(*) as num FROM orderitem WHERE OrderID_FK='{$_SESSION['OrderID']}' AND ProductID_FK='{$_REQUEST['productid']}' LIMIT 1")));
if($num > 0){
//this item DOES exist on this person's order, so we increase its quantity.
$order_sql = "UPDATE orderitem SET OrderItemQuantity=(new quantity and price') AND id values LIMIT 1";
echo 'updated quantity of your existing order';
}else{
//this item does NOT exist on this person's order, so we need to get product information and insert a new orderitem
//getting product information (you will most likely need to change this, no idea how you store it)
extract(mysql_fetch_assoc(mysql_query("SELECT Price FROM product WHERE ProductID='{$_REQUEST['productid']}' LIMIT 1")));
if($ProductPrice > 0){
//insert a new order
$order_sql = "INSERT INTO orderitem (order details) VALUES('order values')";
echo 'added a new item to an existing order';
}else{
echo "that product doesn't exist! 2";
}
}
}else{
//new user, new order
//first, we create a new order
mysql_query("INSERT INTO orders (CustID, Auth) VALUES('{$_SESSION['customerid']}', '0')");
//get that order id
$_SESSION['OrderID'] = mysql_insert_id();
//get product information
extract(mysql_fetch_assoc(mysql_query("SELECT Price FROM product WHERE ProductID='{$_REQUEST['productid']}' LIMIT 1")));
if($ProductPrice > 0){
//now we add this item to the order
$order_sql = "INSERT INTO orderitem (order details) VALUES('order values')";
echo 'added a new order';
}else{
echo "that product doesn't exist! 1";
}
}
//we're done.
mysql_query($order_sql);
//inform customer product has been added to cart
?>
<?php
}
?>

[kows]

if I were you, I wouldn't even add anything to their order if they wanted 5 and only 3 were in stock. the user might not want to only buy 3, or something. you can just display how many are in stock on the item's description, and then it's up to the user to see that. but, either way, it would be pretty easy to add into your script. looking at the way it's set up now, it might be easier to remove the "product exists" checks and do one check in the beginning that gets the amount in stock and the price at the same time. I opted to just do it the way you described! leaving out everything but the relevant "markers":
<?php
if(isset($_REQUEST['productid'], $_REQUEST['quantity']) && is_numeric($_REQUEST['productid']) && is_numeric($_REQUEST['quantity'])){

extract(mysql_fetch_assoc(mysql_query("SELECT Price as price, stockamount FROM product WHERE ProductID='{$_REQUEST['productid']}' LIMIT 1")));
if($price && $stockamount){

$userstock = $_REQUEST['quantity'];

//do we have enough stock to satisfy this user?
$orderstock = ($stockamount >= $userstock) ? $userstock : $stockamount;
//IMPORTANT: use THIS variable when adding/updating the quantity to the order!

//did we change the user's quantity? for display later on.
$quantitychange = ($orderstock != $userstock) ? true : false;

//now, $orderstock should be equal to the maximum stock we have, or the requested stock
if($orderstock == 0){

//we're out of this item.
echo "we're out of this item!";

}else{

//item exists! go through normal logic:
if(isset($_SESSION['OrderID']) && $_SESSION['OrderID'] > 0){

//already has an order, so we either add or update the order
//bunch of stuff

}else{

//new user, new order
//bunch of other stuff

}
//we're done.
mysql_query($order_sql);

//did we end up changing the user's quantity?
if($quantitychange){
echo "the quantity you selected ({$userstock}) exceeded our current stock ({$stockamount}), and was changed to {$orderstock}.";
}

//inform customer product has been added to cart



} //end stock check if

}else{ //item check if

//item doesn't exist
echo "that item doesn't exist!";

}
}
?>


if you are going to use this as stock code, make sure that instead of using $ProductPrice, you use just $price (or change the "AS" in the SQL query at the beginning). you can also change the following (both times this logic is used), because the "item exist" check is used earlier on in the script instead:

extract(mysql_fetch_assoc(mysql_query("SELECT Price FROM product WHERE ProductID='{$_REQUEST['productid']}' LIMIT 1")));
if($ProductPrice > 0){
//insert a new order
$order_sql = "INSERT INTO orderitem (order details) VALUES('order values')";
echo 'added a new item to an existing order';
}else{
echo "that product doesn't exist! 2";
}
/****************************** to this ************************/

//insert a new order
$order_sql = "INSERT INTO orderitem (order details) VALUES('order values')";
echo 'added a new item to an existing order';


hope that wasn't all too messy, and that you can make sense of where to place the "new" code in the old code depending on the "markers."

also: not sure if you changed some of your code when posting it, but when you're using extract(), you're getting "Price" from the database (which would then be named $Price), but you're checking $ProductPrice instead. but, I would assume you changed this, since you don't seem to be actually having problems with what you're using.

[buffy]

Thank you kows.

We have decided to not show the stock amount. Only because we don't want people to easily see our stock levels. Of course they will be able to find out if they enter in a high number for each item. But least that way it takes a bit of effort :)

Your code was great - as always. I am just struggling with the placement.

In your example, you moved the "mysql_query($order_sql);" up to past half way, then continued with some else statements. This has got me a bit confused as to which else and brackets to shift.

I haven't tried lining up my curly brackets yet (I use crimson editor) as I will do that once I have the placement better.

I have left my code to what I am using, I just get a bit nervous with putting it on here, but I guess as long as my escape strings are there my secruity will be okay.

If you could give me a heads up as to whether I can just move/remove some stuff that'll be great. Thanks again for your help


<?php
if(isset($_REQUEST['productid'], $_REQUEST['quantity']) && is_numeric($_REQUEST['productid']) && is_numeric($_REQUEST['quantity'])){
//get stockamount and price
extract(mysql_fetch_assoc(mysql_query("SELECT ProductPrice as price, StockAmount FROM product WHERE ProductID='{$_REQUEST['productid']}' LIMIT 1")));

$stockamount = $StockAmount;

if($price && $stockamount){

$userstock = $_REQUEST['quantity'];

//do we have enough stock to satisfy this user?
$orderstock = ($stockamount >= $userstock) ? $userstock : $stockamount;
//IMPORTANT: use THIS variable when adding/updating the quantity to the order!

//did we change the user's quantity? for display later on.
$quantitychange = ($orderstock != $userstock) ? true : false;

//now, $orderstock should be equal to the maximum stock we have, or the requested stock
if($orderstock == 0){

//we're out of this item.
echo "we're out of this item!";

}else{
//item exists! go through normal logic:

//product id is set, check if there is currently an order for this person
if(isset($_SESSION['OrderID']) && $_SESSION['OrderID'] > 0){
//already has an order, so we either add or update the order

//first check if this product exists on this person's order
extract(mysql_fetch_assoc(mysql_query("SELECT COUNT(*) as num FROM orderitem WHERE OrderID_FK='{$_SESSION['OrderID']}' AND ProductID_FK='{$_REQUEST['productid']}' LIMIT 1")));
if($num > 0){
//this item DOES exist on this person's order, so we increase its quantity.
$order_sql = "UPDATE orderitem SET ItemQuantity=(ItemQuantity + orderstock, ItemTotal=((ItemQuantity+$orderstock)*$price) WHERE OrderID_FK='{$_SESSION['OrderID']}' AND ProductID_FK='{$_REQUEST['productid']}' LIMIT 1";

//did we end up changing the user's quantity?
if($quantitychange){
echo "the quantity you selected ({$userstock}) exceeded our current stock ({$stockamount}), and was changed to {$orderstock}.";
}

echo 'updated quantity of your existing order';
}else{
//insert a new order
$order_sql = "INSERT INTO orderitem (OrderID_FK, ProductID_FK, ItemQuantity, ItemUnitPrice, ItemTotal) VALUES('{$_SESSION['OrderID']}', '{$_REQUEST['productid']}', '$orderstock', '$price', '$total')";

//did we end up changing the user's quantity?
if($quantitychange){
echo "the quantity you selected ({$userstock}) exceeded our current stock ({$stockamount}), and was changed to {$orderstock}.";
}

echo 'added a new item to an existing order';
}
}else{
//new user, new order
mysql_query("INSERT INTO orders (CustID, Auth) VALUES('{$_SESSION['customerid']}', '0')");
//get that order id
$_SESSION['OrderID'] = mysql_insert_id();
//get product information
extract(mysql_fetch_assoc(mysql_query("SELECT ProductPrice FROM product WHERE ProductID='{$_REQUEST['productid']}' LIMIT 1")));
if($ProductPrice > 0){
//now we add this item to the order
$order_sql = "INSERT INTO orderitem (OrderID_FK, ProductID_FK, ItemQuantity, ItemUnitPrice, ItemTotal) VALUES('{$_SESSION['OrderID']}', '{$_REQUEST['productid']}', '$orderstock', '$price', '$total')";
echo 'added a new order';

//did we end up changing the user's quantity?
if($quantitychange){
echo "the quantity you selected ({$userstock}) exceeded our current stock ({$stockamount}), and was changed to {$orderstock}.";
}

}else{
//Product doesn't exsist message.
echo "that item doesn't exist!";
}
}
//we're done.
mysql_query($order_sql);
}
?>

[kows]

I took the liberty of spacing everything for you so that I could better make sense of everything and make sure it was all in order. with a few quick scans, I made a few changes and also removed what you had either A) not needed or B) had two or three iterations of which would be reduced to one iteration. I would suggest backing up your script just in case you or I missed something!

<?php
if(isset($_REQUEST['productid'], $_REQUEST['quantity']) && is_numeric($_REQUEST['productid']) && is_numeric($_REQUEST['quantity'])){
//get stockamount and price
extract(mysql_fetch_assoc(mysql_query("SELECT ProductPrice as price, StockAmount as stockamount FROM product WHERE ProductID='{$_REQUEST['productid']}' LIMIT 1")));

if($price && $stockamount){
$userstock = $_REQUEST['quantity'];

//do we have enough stock to satisfy this user?
$orderstock = ($stockamount >= $userstock) ? $userstock : $stockamount;
//IMPORTANT: use THIS variable when adding/updating the quantity to the order!

//did we change the user's quantity? for display later on.
$quantitychange = ($orderstock != $userstock) ? true : false;

//now, $orderstock should be equal to the maximum stock we have, or the requested stock
if($orderstock == 0){
//we're out of this item.
echo "we're out of this item!";
}else{
//item exists! go through normal logic:

//product id is set, check if there is currently an order for this person
if(isset($_SESSION['OrderID']) && $_SESSION['OrderID'] > 0){
//already has an order, so we either add or update the order

//first check if this product exists on this person's order
extract(mysql_fetch_assoc(mysql_query("SELECT COUNT(*) as num FROM orderitem WHERE OrderID_FK='{$_SESSION['OrderID']}' AND ProductID_FK='{$_REQUEST['productid']}' LIMIT 1")));

if($num > 0){
//this item DOES exist on this person's order, so we increase its quantity.
$order_sql = "UPDATE orderitem SET ItemQuantity=(ItemQuantity + $orderstock), ItemTotal=((ItemQuantity+$orderstock)*$price) WHERE OrderID_FK='{$_SESSION['OrderID']}' AND ProductID_FK='{$_REQUEST['productid']}' LIMIT 1";

echo 'updated quantity of your existing order';
}else{
//insert a new order
$order_sql = "INSERT INTO orderitem (OrderID_FK, ProductID_FK, ItemQuantity, ItemUnitPrice, ItemTotal) VALUES('{$_SESSION['OrderID']}', '{$_REQUEST['productid']}', '$orderstock', '$price', '$total')";

echo 'added a new item to an existing order';
}
}else{
//new user, new order
mysql_query("INSERT INTO orders (CustID, Auth) VALUES('{$_SESSION['customerid']}', '0')");

//get that order id
$_SESSION['OrderID'] = mysql_insert_id();

//now we add this item to the order
$order_sql = "INSERT INTO orderitem (OrderID_FK, ProductID_FK, ItemQuantity, ItemUnitPrice, ItemTotal) VALUES('{$_SESSION['OrderID']}', '{$_REQUEST['productid']}', '$orderstock', '$price', '$total')";
echo 'added a new order';

}
//we're done.
mysql_query($order_sql);

//did we end up changing the user's quantity?
if($quantitychange){
echo "the quantity you selected ({$userstock}) exceeded our current stock ({$stockamount}), and was changed to {$orderstock}.";
}
}else{
//item doesn't exist
echo "item doesn't exist!";
}
}
?>

[buffy]

Hi kows,

That was good thank you. I removed the else statement at the end, (was getting a T_ELSE error) since there was two elses and the first one was doing the 'item doesn't exisit' bit. And added a missing curly at end.

Doing some testing I realised the following senario occured:

1) Customer adds 99 items. System only allows the stock amount. Say 20
2) Customer adds 99 items again. System only allows the stock amount (20)
-- it then adds this to the exisiting quantity
-- total item amount is now 40

So I decided to add them as mentioned above. And if the total was more then the stock amount then the total should equal the stockamount.

EDIT: I now get the following errors, and although the message says the new total is correctly the maximum stock amount, it then adds another item to the order with the maximum stock amount (rather then setting the exisiting items quantity as the maximum stock amount).

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /www/...inc.php on line 38

Warning: extract() [function.extract]: First argument should be an array in /www/...inc.php on line 38

Code:

//first check if this product exists on this person's order
extract(mysql_fetch_assoc(mysql_query("SELECT ItemQuantity as oldquantity, COUNT(*) as num FROM orderitem WHERE OrderID_FK='{$_SESSION['OrderID']}' AND ProductID_FK='{$_REQUEST['productid']}' LIMIT 1")));

if($num > 0){
//this item DOES exist on this person's order, so we increase its quantity
//make sure if new total quantity greater then stock amount, that the new total = stock amount
$UpdatedQuantity = '';
$UpdatedQuantity = $oldquantity + $orderstock;
if ($UpdatedQuantity > $stockamount) {
//make new total orderstock amount the stockamount allowed
$orderstock = $stockamount;
//display message about maximum stockamount added
echo "Your updated total of ({$UpdatedQuantity}) exceeded our current stock ({$stockamount}), and was changed to {$orderstock}.";
//don't show generic quantity message at end
$quantitychange = false;
}
//Update orderitem with new quanity
$order_sql = "UPDATE orderitem SET ItemQuantity=$orderstock, ItemTotal=($orderstock*$price) WHERE OrderID_FK='{$_SESSION['OrderID']}' AND ProductID_FK='{$_REQUEST['productid']}' LIMIT 1";
echo 'updated quantity of your existing order';
}else{
//insert a new order item into exsisting order
$order_sql = "INSERT INTO orderitem (OrderID_FK, ProductID_FK, ItemQuantity, ItemUnitPrice, ItemTotal) VALUES('{$_SESSION['OrderID']}', '{$_REQUEST['productid']}', '$orderstock', '$price', '$total')";

echo 'added a new item to an existing order';
}

[kows]

if mysql_fetch_assoc() is returning an error, your SQL statement probably has an error in it! echo out your SQL statement and make sure that it isn't incorrect. this means $num would be set to null because extract() isn't returning any variables, so $num will never be > 0.

[buffy]

Thanks kows.


//first check if this product exists on this person's order
extract(mysql_fetch_assoc(mysql_query("SELECT ItemQuantity as oldquantity, COUNT(*) as num FROM orderitem WHERE OrderID_FK='{$_SESSION['OrderID']}' AND ProductID_FK='{$_REQUEST['productid']}' LIMIT 1")));


... Worked when I remove "ItemQuantity as oldquantity,"

Added OR die(mysql_error());

PHP says:
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

Added GROUP BY OrderID_FK before LIMIT (pointless in this instance?) which it worked when it was adding to an exisiting item, but not a new item.

New item (either under or over the stock amount) gave the extract error only: Warning: extract() [function.extract]: First argument should be an array in /...
I believe this is because ItemQuantity is NULL (no record of that item if the user hasn't entered it yet).

How can I factor in the NULL variable into my extract statement, do I have to do seperate extracts?

[kows]

that's usually when I just suppress the error (although this is usually bad). you could get around it, but I usually don't because I know what's going on.
@extract(stuff);
to get around it, you'd probably want to make a call to mysql_num_rows() on the mysql_query() call, and then check whether any records were returned, and then call extract() on mysql_fetch_assoc() of the mysql_query(). if that even makes sense.

but anyway, if the query is returning empty, then $num will be null and the ELSE will trigger, which should be what you want!