when item is purchased (through PayPal), subtract one number from stock

[davids_media]

I have somewhat of a dilemma.

 

When someone clicks on a Buy Now button and subsequently follows necessary steps to complete process of purchase, when that transaction is completed, in my product table, I want to subtract 1 from whatever value is in the field.

 

E.g. say product one is being purchased, prior to purchase in product table, there are 5 product one's in stock, when purchase is complete, subtract 1 from 5 (to get 4).

 

Here is my code

 

<?php

$title = "Like This Product, Buy It NOW!!!";

require ('includes/config.inc.php');

include ('./includes/header.html');

require (MYSQL);

include ('./includes/main.html');

if($id = isset($_GET['prodID']))
{
$query = "SELECT `prodID`, `product`, `prod_descr`, `image`, `price` FROM product WHERE `prodID`='{$_GET['prodID']}'";
$r = mysqli_query($dbc, $query);

$showHeader = true;
echo "<div id='right'>";

while($row = mysqli_fetch_array($r))
{
if($showHeader)
{
            //Display category header
echo "<h1>" . "<span>" . "# " . "</span>" . $row['product'] .  "<span>" . " #" . "</span>" . "</h1>";

echo "<div id='item'>"; // div class 'item'

echo "<div class='item_left'>";

echo "<p id='p_desc'>";
echo $row['prod_descr'];
echo "</p>";

echo "<p>" . "<span>" . "&pound" . $row['price'] . "</span>" . "</p>";

echo "</div>";

echo "<div class='item_right'>";

echo "<img src='db/images/".$row['image']."' />";
$showHeader = false;

echo "</div>";

?>

<p>
<form target="paypal" action="https://www.sandbox.paypal.com/cgi-bin/webscr" method="post">
<input type="hidden" name="cmd" value="_s-xclick">
<input type="hidden" name="hosted_button_id" value="7UCL9YCYYXL3J">
<input type="hidden" name="item_name" value="<?php echo $row['product']; ?>">
<input type="hidden" name="item_number" value="<?php echo $row['prodID']; ?>">
<input type="hidden" name="amount" value="<?php echo $row['price']; ?>">
<input type="hidden" name="currency_code" value="GBP">
<input type="image" src="https://www.sandbox.paypal.com/en_US/i/btn/btn_cart_LG.gif" border="0" name="submit" alt="PayPal - The safer, easier way to pay online!">
<img alt="" border="0" src="https://www.sandbox.paypal.com/en_US/i/scr/pixel.gif" width="1" height="1">
</form>
</p>

<p>
<form name="_xclick" action="https://www.paypal.com/cgi-bin/webscr" method="post">
<input type="hidden" name="cmd" value="_xclick">
<input type="hidden" name="business" value="me@mybusiness.com">
<input type="hidden" name="currency_code" value="GBP">
<input type="hidden" name="item_name" value="<?php echo $row['product']; ?>">
<input type="hidden" name="amount" value="<?php echo $row['price']; ?>">
<input type="image" src="http://www.paypal.com/en_US/i/btn/btn_buynow_LG.gif" border="0" name="submit" alt="Make payments with PayPal - it's fast, free and secure!">
</form>
</p>

<?php 

echo "</div>"; // End of div class 'item'

$strSQL = "SELECT prodID, product, price, image FROM product ORDER BY RAND() LIMIT 1";  
$objQuery = mysqli_query($dbc, $strSQL) or die ("Error Query [".$strSQL."]");

while($objResult = mysqli_fetch_array($objQuery))  
{ 
echo "<div class='love'>";
echo "<h6>Like this......you'll love this!!!</h6>";
echo "<ul>";
echo "<li>" . "<img src='db/images/" . $objResult['image'] . "' width='50' height='50' />" . "</li>";
echo "<br />";
echo "<li>" . "<a href='item.php?prodID={$objResult['prodID']}' title='{$objResult['product']}'>" . $objResult['product'] . "</a>" . " - " . "&pound" . $objResult['price'] . "</li>";
echo "</ul>";
echo "</div>";
}

}
}

?>

<?php

echo "</div>";

}

include ('./includes/footer.html');

?>

 

How is this achievable please?

[xyph]

UPDATE table SET stock = stock - 1 WHERE product_id = '$product_id'

[davids_media]

if you do not mind me asking, but where within my code should i use this statement please? reason being is that item.php primarily displays data with the aforementioned Buy Now and Add To Cart buttons.

[PFMaBiSmAd]

You should maintain your stock by using a credit/debit account table (i.e. the same way your checking or credit card account is maintained), not by having just a number that you add or subtract from. You should add a row to a `stock` table with a + or - quantity for each addition or subtraction for each product. To get the total at any point in time, you would simply sum the +/- quantity values for the product(s) in question. The table would have, at a minimum, columns for the product_id, quantity ( + or -), and the datetime the row was added. You would typically also have a status column that indicates things like - allocated (part of an order, but not actually removed from stock), pulled (actually removed from stock), and columns for anything else you might want to record about each addition or subtraction to stock.

 

The two main reasons for doing it this way are 1) to be able to detect and correct errors, 2) to maintain the stock history (you can produce reports showing usage vs time...)

 

Edit: Here's a more detailed version of this - http://www.phpfreaks.com/forums/index.php?topic=349637.msg1650171#msg1650171

[merylvingien]

If your just using paypal to sell, you need to check that they have actually made payment and not canceled before updating your tables.

Easiest way to do this is paypal ipn.

Lots of info available on the net on this.

[davids_media]

yes, I am just using it to sell products ONLY. that being said, a lot of the techniques I have used are derived from Larry Ullman's 2010 book "Effortless E-Commerce".

 

however, the IPN discussed in that book relates to subscription and renewal of content rather than buying physical products online so I may have to take a different approach. are they're any tutorials in relation to PayPal IPN's specifically for buying/selling physical products?

[merylvingien]

Ive used ipn for selling products. It makes no difference what your selling as you code the ipn return page to what you need.

 

So for example, if someone buys an item from your site, it is good to send them an email so they know you got the order. This will reassure the buyer (it does me when i buy stuff online)

 

In your specific case you are also going to want to update your database and alter the count of the specific item stock.

 

Based on this, you know what variables you need to capture. Buyers email address, payment completed, quantity buyer purchased, price they paid (need to do a check on this) and whatever else you can think of.

 

You can download the ipn example from paypal and just alter it to suit your needs.

[james_2012]

The real problem in selling physical products is really shipping costs.  You have to descide on the type of shipping costs that you want to add, e.g a percentage of the total cost, or a fixed shipping costs.  My product PHP-eSeller http://www.withinweb.com/phpeseller/ uses a that method for physical goods but it is really intended to sell digital goods.