php code help

[mrzebra81]

Hi,

 

I'm trying to update the stock in my clearance table after payment has been received. I have this code on my success.php page and the problem is that it is taking twice as many out of stock as it should be....if 1 clearance item is ordered it is taking 2 out of stock, etc.

 

Here is my code:

<?php 
echo "<font color=red>CART COUNT: ";
echo count($_SESSION['cart_array']);
echo "</font><br>";

// 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";
        
        if (mysql_query($sql) == false) {
        	user_error("QUERY FAILED: " . mysql_error());
     	 } else {
         	// comment this out in production version:
         	user_error("DEBUG: Quantity updated: $sql");
      }
        

    }	

}

?>

 

Here is the output I get when I have 3 items in the shopping cart (2 are clearance items):

CART COUNT: 3

 

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

 

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

 

 

 

I have no idea what the problem is....any help would be appreciated. I've been looking at this for a couple weeks now.

[monkeytooth]

stock = stock - $quantity ? Not sure if that makes sense, I could be wrong though. Anyway in the DB assuming that does work functionally.. is "stock" an INT value..

also whats the value of $quantity when it reaches the query, how does it initally get set? maybe what ever sets $quantity is setting it at a value not expected?

[PFMaBiSmAd]

What does the following show for the actual data (you never know, your data could be something other than what you think) -

 

echo '<pre>',print_r($_SESSION['cart_array'],true),'</pre>';

 

It's likely that the browser is requesting the page twice or your code is doing something to cause the posted code to be executed twice. I recommend logging (see the error_log function) the microtime and the actual query statement to a file so that you can see if there are duplicate sets of queries being executed and what time interval is between them.

 

Also, how is the code that you posted being requested? By the browser, by a cron job, by the payment gateway notification?

 

[mrzebra81]

So echo <pre>...prints out the following:

 

Array

(

    [0] => Array

        (

            [id] => 40032

            [product_id] => 4

            [scent] => 0032

            [quantity] => 1

        )

 

    [1] => Array

        (

            [id] => 130002

            [product_id] => 13

            [scent] => 0004

            [quantity] => 1

        )

 

    [2] => Array

        (

            [id] => 130003

            [product_id] => 13

            [scent] => 0006

            [quantity] => 1

        )

 

)

 

 

When the user completes the paypal payment, they are taken to the success.php page and the code to update the "clearance" table is executed.

[mrzebra81]

stock is an int value in the database. $quantity is set to 0 at the start and then if the id of the item in the cart is "13" the quantity is set to the quantity value in the cart for that item.

[PFMaBiSmAd]

I don't think that paypal redirecting the user to your page would cause your page to be requested twice, but the browser could do this on its own (different browsers request pages twice for different reasons) or you could be doing some url rewriting that causes it.

 

I would set the quantity in the session variable to zero (or remove it completely) after the item has been updated in the database so that if the page is ever requested again, even if the visitor refreshes the page, the quantity in the database won't be affected.

 

 

 

 

[mrzebra81]

I still can't get it to work when i unset the session cart...nothing gets updated. I added the unset line after the foreach loop.

[PFMaBiSmAd]

I added the unset line after the foreach loop.

 

I think you should probably post your current code.

[PFMaBiSmAd]

Some comments about what you are currently doing -

 

1) Just because paypal redirects the visitor back to your site, does not mean that the payment was successful and completed. You should read the paypal documentation for Payment Data Transfer (PDT) and Instant Payment Notification (IPN). You would only consider items as being purchased if the transaction is successful.

 

2) 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.

[mrzebra81]

I'm using this from paypal:

 

return

Optional

The URL to which the payer’s browser is redirected after completing the payment; for example, a URL on your site that displays a “Thank you for your payment” page.

 

Default – The browser is redirected to a PayPal web page.

 

 

So the user is redirected to my success.php after the payment is completed. It is on this page that i'm trying to update the clearance "in stock" quantity using what was in the user's shopping cart.

 

So my paypal button uses this:

<input type=hidden name="return" value="http://www.coppercreekbathandbody.ca/success.php">

 

Here is my entire success.php page code:

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

[PFMaBiSmAd]

In the code you posted above, you removed the lines of code that were executing your query, so of course "nothing gets updated."

 

Assuming you are doing this for a real ecommerce site, your success.php code MUST check back with the paypal site to determine if the payment was successful. The Payment Data Transfer (PDT) documentation that I mentioned shows how to do this. There's even a php code example available on the paypal PDT page. Anyone can (and they will) browse to your code and cause it to be executed, thereby altering your stock counts and making it look like they payed for something that they did not.

[dcro2]

Even if you only update the stock of products using this page (and not if someone's payment went through) you should be using IPN or PDT with verification from PayPal:

 

[mrzebra81]

Ok, looks like that will be a very good idea. Didn't realize that the price can be changed like that in the paypal cart. Lol. I will have to fix that up and look at using IPN. I should be able to use their php example as a template correct?