Updating database (Quantity in Stock)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mrzebra
Forum Commoner
Posts: 37
Joined: Tue Feb 22, 2011 10:30 pm

Updating database (Quantity in Stock)

Post 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.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Updating database (Quantity in Stock)

Post 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.
mrzebra
Forum Commoner
Posts: 37
Joined: Tue Feb 22, 2011 10:30 pm

Re: Updating database (Quantity in Stock)

Post 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)...??
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Updating database (Quantity in Stock)

Post by Jonah Bron »

Yes, whatever page confirms the order should contain that code.
mrzebra
Forum Commoner
Posts: 37
Joined: Tue Feb 22, 2011 10:30 pm

Re: Updating database (Quantity in Stock)

Post 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
	  
}

?>
mrzebra
Forum Commoner
Posts: 37
Joined: Tue Feb 22, 2011 10:30 pm

Re: Updating database (Quantity in Stock)

Post 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....
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Updating database (Quantity in Stock)

Post by Jonah Bron »

Print some variables in the if to find out if the code is being run twice, or the calculation is wrong.
mrzebra
Forum Commoner
Posts: 37
Joined: Tue Feb 22, 2011 10:30 pm

Re: Updating database (Quantity in Stock)

Post 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.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Updating database (Quantity in Stock)

Post by Jonah Bron »

Are you absolutely sure that the query isn't being run twice?
mrzebra
Forum Commoner
Posts: 37
Joined: Tue Feb 22, 2011 10:30 pm

Re: Updating database (Quantity in Stock)

Post 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....
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Updating database (Quantity in Stock)

Post 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?
mrzebra
Forum Commoner
Posts: 37
Joined: Tue Feb 22, 2011 10:30 pm

Re: Updating database (Quantity in Stock)

Post by mrzebra »

yes that is correct. It's very frustrating. lol.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Updating database (Quantity in Stock)

Post by Jonah Bron »

Hm, well this isn't adding up. Is that all the code there is on that page?
mrzebra
Forum Commoner
Posts: 37
Joined: Tue Feb 22, 2011 10:30 pm

Re: Updating database (Quantity in Stock)

Post 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>

Post Reply