Updating database (Quantity in Stock)
Posted: Sun Mar 20, 2011 4:08 pm
Hello,
I'm trying to update the quantity in stock in my "clearance" table. When the user adds an item to the cart, I want it to decrease the stock quantity for that item by 1. Once in the shopping cart, the person can update the number of items he/she wants of that item. Once they ajust their quantity in the cart, I need to update the "Quantity in Stock" again in my cart table.
For example:
I have 3 Pomegranate Hand Creams in stock. The person adds one to the cart, so the stock quantity in my clearance table should be updated to 2. So now the person is viewing their cart and they decide they want 2 of the Pomegranate Hand Creams. They enter 2 into the quantity box and click update. I then need to take another one out of the stock. So my table should have only 1 in stock.
I tried coding it but if the person adds 1 to the cart, it updates my clearance table to 0 instead of 2. I'm not sure how to fix this or if there is an easier way to do this.
Here is my code if the item is a clearance Item:
Any help would be greatly appreciated.
Thanks in advance.
I'm trying to update the quantity in stock in my "clearance" table. When the user adds an item to the cart, I want it to decrease the stock quantity for that item by 1. Once in the shopping cart, the person can update the number of items he/she wants of that item. Once they ajust their quantity in the cart, I need to update the "Quantity in Stock" again in my cart table.
For example:
I have 3 Pomegranate Hand Creams in stock. The person adds one to the cart, so the stock quantity in my clearance table should be updated to 2. So now the person is viewing their cart and they decide they want 2 of the Pomegranate Hand Creams. They enter 2 into the quantity box and click update. I then need to take another one out of the stock. So my table should have only 1 in stock.
I tried coding it but if the person adds 1 to the cart, it updates my clearance table to 0 instead of 2. I'm not sure how to fix this or if there is an easier way to do this.
Here is my code if the item is a clearance Item:
Code: Select all
// if it is a clearance/sale item
case "13":
$scent=substr($each_item['scent'],0,4);
$clearId=substr($each_item['id'],2,4);
// Get product name and price
$sql=mysql_query("SELECT * FROM clearance WHERE productId='$clearId' LIMIT 1");
while ($row=mysql_fetch_array($sql)) {
$clearId=$row['productId'];
$product_name=$row["productName"];
$price=$row["price"];
$stock=$row["stock"];
}
# get product scent name
$sql2= "SELECT productscents.scentName FROM productscents WHERE productscents.scentId = '$scent'";
$result2 = mysql_query($sql2);
$row2 = mysql_fetch_array($result2);
$scent=$row2['scentName'];
if(isset ($_SESSION["cart_array"] )) {
// if the quantity entered is higher than the available stock set the quantity to the amount in stock
if($each_item['quantity']>$stock) {
$each_item['quantity']=$stock;
}
$subtotal=$price*$each_item['quantity'];
$total=$subtotal + $total;
// dynamic checkout button assembly
$x=$i+1;
$pp_checkout_btn.='<input type=hidden name="item_name_'.$x.'" value="'.$scent.' '.$product_name.'">
<input type=hidden name="amount_'.$x.'" value="'.$price.'">
<input type=hidden name="quantity_'.$x.'" value="'.$each_item['quantity'].'">';
// dynamic cart item display
$cartOutput.="<tr align=center>
<td width=5%><font color='#999999'>".$each_item['id']."</font></td>
<td width=25%><font color='#999999'>".$product_name."<br>* CLEARANCE * </font></td>
<td width=40%><font color='#999999'>".$scent."</font></td>
<td valign=center width=5%><font color='#999999'><form action=cart.php method=post>
<input class=box name=quantity type=text size=1 width=3 maxlength=3 onKeyPress='return check_qty(event);' value=".$each_item['quantity'].">
<input type=hidden name='item_to_adjust' value='".$item_id."'/>
<input type=hidden name='name' value='".$product_name."'/>
<input type=hidden name='scent' value='".$scent."'/>
<br><font color='#999999' size='-1'><a href=# onClick='submit()'>Update</a><br>
</font></td>
<td width=5%><font color='#999999'>$".$price.".00</font></td>
<td width=10%><font color='#999999'>$".$subtotal.".00</font></td>
</tr></form>
<tr> <td colspan=6><hr size=1 width=50% color='#CBB659'/></td></tr>";
$i++;
// update database Stock Quantity
if ($each_item['quantity'] !=0) {
$updateQuantity=$stock-$each_item['quantity'];
mysql_select_db('CopperCreek');
mysql_query("UPDATE clearance SET stock = '$updateQuantity' WHERE productId = '$clearId'");
}
// if the item is removed from the cart, put the quantity back into stock
if ($each_item['quantity'] ==0) {
$updateQuantity=$each_item['quantity']+$stock;
mysql_select_db('CopperCreek');
mysql_query("UPDATE clearance SET stock = '$updateQuantity' WHERE productId = '$clearId'");
}
} // end if statement to see if cart is empty
break;
Thanks in advance.