Page 1 of 1

Updating database (Quantity in Stock)

Posted: Sun Mar 20, 2011 4:08 pm
by mrzebra
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:

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;
				
Any help would be greatly appreciated.

Thanks in advance.

Re: Updating database (Quantity in Stock)

Posted: Sun Mar 20, 2011 4:30 pm
by Jonah Bron
My advice: don't lower the quantity until the person has completed their order. What if they add it to their card, and then decide they don't want to go through with the purchase? You then have an invalid number in your table.

Re: Updating database (Quantity in Stock)

Posted: Sun Mar 20, 2011 5:39 pm
by mrzebra
Ok, so how do I do that? Should I add the code to the success.php page (person is taken there after the payment is complete)...??

Re: Updating database (Quantity in Stock)

Posted: Sun Mar 20, 2011 6:18 pm
by Jonah Bron
Yes, whatever page confirms the order should contain that code.

Re: Updating database (Quantity in Stock)

Posted: Sun Apr 03, 2011 12:28 pm
by mrzebra
Need some help here, for some reason it is taking double the ordered quantity out of stock. (If 2 of one product is ordered, it's taking 4 out of stock for the item).

I'm not sure why it is doing that. The correct item id, stock and quantity are displayed when I print those variables.

Here is my code:

Code: Select all

<?php 

// update clearance item "in stock" quantity
foreach ($_SESSION['cart_array'] as $each_item) {
    $stock=0;
	$quantity=0;
	$newQuantity=0;
	$id=0;   
   
	
	// if the item in the cart is a clearance item
    if ($each_item['product_id'] == 13) {
    	// get the clearance item ID and quantity of item in the cart
        $id=substr($each_item['id'],2,4);
        $quantity=$each_item['quantity'];
      
      
        //get the in stock quantity
        $sql=mysql_query("SELECT stock FROM clearance WHERE productId='$id' LIMIT 1");
        $row=mysql_fetch_array($sql);
        $stock=$row['stock'];
                     
        $newQuantity=$stock-$quantity;	

        // update the clearance item stock
        mysql_query("UPDATE clearance SET stock = '$newQuantity' WHERE productId = '$id' ");
               
               
	} // end if
	  
}

?>

Re: Updating database (Quantity in Stock)

Posted: Tue Apr 05, 2011 11:37 pm
by mrzebra
Does anyone have any ideas why it's taking the stock out twice for each clearance item in the cart? I tried moving my update table code outside the if statement and it didn't help. I tried doing a switch statement instead of an if statement and that didn't help....

Re: Updating database (Quantity in Stock)

Posted: Thu Apr 07, 2011 11:05 pm
by Jonah Bron
Print some variables in the if to find out if the code is being run twice, or the calculation is wrong.

Re: Updating database (Quantity in Stock)

Posted: Sat Apr 09, 2011 1:35 pm
by mrzebra
It is printing the correct quantity before the if statement. In the if statement, before and after the query, and at the end, outside of the if statement.

Re: Updating database (Quantity in Stock)

Posted: Mon Apr 11, 2011 4:21 pm
by Jonah Bron
Are you absolutely sure that the query isn't being run twice?

Re: Updating database (Quantity in Stock)

Posted: Mon Apr 11, 2011 11:23 pm
by mrzebra
yes, i ran the following code:

Code: Select all

<?php 

// update clearance item "in stock" quantity
foreach ($_SESSION['cart_array'] as $each_item) {
    
	$quantity=0;
	
	$id=0;   
   	
	
	// if the item in the cart is a clearance item
    if ($each_item['product_id'] == 13) {
    	
    	// get the clearance item ID and quantity of item in the cart
        $id=substr($each_item['id'],2,4);
        $quantity=$each_item['quantity'];
    	
		// update the clearance item stock
        $sql = "
		UPDATE clearance
		SET stock = stock - $quantity
		WHERE clearance.productId = '$id' 
		LIMIT 1";
        
        if (mysql_query($sql) == false) {
        	user_error("QUERY FAILED: " . mysql_error());
     	 } else {
         	// comment this out in production version:
         	user_error("DEBUG: Quantity updated: $sql");
      }
        
	 
    }	
	 
}

?>
It output the following (there were 2 clearance items in the cart ):

Notice: DEBUG: Quantity updated: UPDATE clearance SET stock = stock - 2 WHERE clearance.productId = '0002' LIMIT 1 in C:\wamp\www\CopperCreek\success.php on line 36

Notice: DEBUG: Quantity updated: UPDATE clearance SET stock = stock - 1 WHERE clearance.productId = '0003' LIMIT 1 in C:\wamp\www\CopperCreek\success.php on line 36


The quantities are correct, it's just taking double the stock out....

Re: Updating database (Quantity in Stock)

Posted: Wed Apr 13, 2011 7:31 pm
by Jonah Bron
That's very strange... very strange indeed. So, you checked the quantity in the database, ran the code once, and product '0002' was four less, and product '0003' was two less?

Re: Updating database (Quantity in Stock)

Posted: Wed Apr 13, 2011 8:52 pm
by mrzebra
yes that is correct. It's very frustrating. lol.

Re: Updating database (Quantity in Stock)

Posted: Wed Apr 13, 2011 9:43 pm
by Jonah Bron
Hm, well this isn't adding up. Is that all the code there is on that page?

Re: Updating database (Quantity in Stock)

Posted: Sun Apr 17, 2011 11:46 am
by mrzebra
here is my entire success.php page code:

Code: Select all

<?php
session_start();
error_reporting(E_ALL);
ini_set('display_errors','1');
require_once("connect.php");
?>

<?php 


// update clearance item "in stock" quantity
foreach ($_SESSION['cart_array'] as $each_item) {
    
	$quantity=0;
	
	$id=0;   
   	
	
	// if the item in the cart is a clearance item
    if ($each_item['product_id'] == 13) {
    	
    	// get the clearance item ID and quantity of item in the cart
        $id=substr($each_item['id'],2,4);
        $quantity=$each_item['quantity'];
    	
		// update the clearance item stock
        $sql = "
		UPDATE clearance
		SET stock = stock - '$quantity'
		WHERE productId = '$id' 
		LIMIT 1"; 
        	
        
	 
    }
    
	 
}

?>







<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
	
	<link rel="stylesheet" type="text/css" href="./css/navigationStyles.css"/>
	<script type="text/javascript" src="./js/jquery.js"></script>
	<script type="text/javascript" src="./js/sliding_effect.js"></script>
<script language="JavaScript">
var time = null
function move() {
window.location = 'home.php'
}

</script>
	<title>Your payment has been successfully recieved...</title>
</head>
<body bgcolor="#1d1d1d"> <!--  onload="timer=setTimeout('move()',5000)"> -->



	<div id="wrapper">
		<div id="main">
				<center><img src="./images/copperCreekLogo.gif"><br>
				<br>
				<br>
				<font size=+1 face=arial color="#999999">Thank you! Your order and payment have been sent.<br>
				If you are not redirected shortly, please click <a href=home.php>here</a></font>


				
				</center>		
		</div>
		
		
	<div id="sidebar" >
			<?php require ("menu.html"); ?> 
 
			
			
		
		
		</div>
	</div> <!-- End wrapper class -->






</body>
</html>