hi i want to create a stock level count

[alpha1]

$sqlCommand = "CREATE TABLE products (
	 		 id int(11) NOT NULL auto_increment,
			 product_name varchar(255) NOT NULL,
	 		 price varchar(16) NOT NULL,
			 details text NOT NULL,
			 category varchar(16) NOT NULL,
			 subcategory varchar(16) NOT NULL,
	 		 m_tag text NOT NULL,
			 date_added date NOT NULL,				 
	 		 PRIMARY KEY (id),
	 		 UNIQUE KEY product_name (product_name)
	 		 ) ";.

 

this is currently my database, i would like to create a stock level count so im assuming i need a new field called (stock)

 

also on my homepage i would like a list of best selling items so im probably going to need a (total sold)

 

my website has a basket, when you click check out it goes to paypal so you can pay for your transaction. i have a ipn script to verify with payal.

 

but i dont have a clue how to update my stock level after each transaction. can someone please help and point me in the right direction

[Psycho]

I would suggest a separate table to store any changes YOU make to stock. this would typically only include when you add new product, but their might be occasions when you would remove stock (spoilage, theft, etc.). Then you take the total from that table for a particular product and subtract the total ordered for that product in your orders table.

[alpha1]

thank you

 

but how would i update my ordered table after the transaction is complete by paypal, would that be done by my IPN script?

 

this is the ipn script

<?php
// Check to see there are posted variables coming into the script
if ($_SERVER['REQUEST_METHOD'] != "POST") die ("No Post Variables");
// Initialize the $req variable and add CMD key value pair
$req = 'cmd=_notify-validate';
// Read the post from PayPal
foreach ($_POST as $key => $value) {
    $value = urlencode(stripslashes($value));
    $req .= "&$key=$value";
}
// Now Post all of that back to PayPal's server using curl, and validate everything with PayPal
// We will use CURL instead of PHP for this for a more universally operable script (fsockopen has issues on some environments)
//$url = "https://www.sandbox.paypal.com/cgi-bin/webscr";
$url = "https://www.paypal.com/cgi-bin/webscr";
$curl_result=$curl_err='';
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL,$url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $req);
curl_setopt($ch, CURLOPT_HTTPHEADER, array("Content-Type: application/x-www-form-urlencoded", "Content-Length: " . strlen($req)));
curl_setopt($ch, CURLOPT_HEADER , 0);   
curl_setopt($ch, CURLOPT_VERBOSE, 1);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
curl_setopt($ch, CURLOPT_TIMEOUT, 30);
$curl_result = @curl_exec($ch);
$curl_err = curl_error($ch);
curl_close($ch);

$req = str_replace("&", "\n", $req);  // Make it a nice list in case we want to email it to ourselves for reporting

// Check that the result verifies
if (strpos($curl_result, "VERIFIED") !== false) {
    $req .= "\n\nPaypal Verified OK";
} else {
$req .= "\n\nData NOT verified from Paypal!";
mail("xx", "IPN interaction not verified", "$req", "From: xx" );
exit();
}

/* CHECK THESE 4 THINGS BEFORE PROCESSING THE TRANSACTION, HANDLE THEM AS YOU WISH
1. Make sure that business email returned is your business email
2. Make sure that the transaction's payment status is "completed"
3. Make sure there are no duplicate txn_id
4. Make sure the payment amount matches what you charge for items. (Defeat Price-Jacking) */

// Check Number 1 ------------------------------------------------------------------------------------------------------------
$receiver_email = $_POST['receiver_email'];
if ($receiver_email != "xx") {
$message = "Investigate why and how receiver email is wrong. Email = " . $_POST['receiver_email'] . "\n\n\n$req";
    mail("xx", "Receiver Email is incorrect", $message, "From: xx" );
    exit(); // exit script
}
// Check number 2 ------------------------------------------------------------------------------------------------------------
if ($_POST['payment_status'] != "Completed") {
// Handle how you think you should if a payment is not complete yet, a few scenarios can cause a transaction to be incomplete
}
// Connect to database ------------------------------------------------------------------------------------------------------
require_once 'connect_to_mysql.php';
// Check number 3 ------------------------------------------------------------------------------------------------------------
$this_txn = $_POST['txn_id'];
$sql = mysql_query("SELECT id FROM transactions WHERE txn_id='$this_txn' LIMIT 1");
$numRows = mysql_num_rows($sql);
if ($numRows > 0) {
    $message = "Duplicate transaction ID occured so we killed the IPN script. \n\n\n$req";
    mail("xx", "Duplicate txn_id in the IPN system", $message, "From:xxx" );
    exit(); // exit script
} 
// Check number 4 ------------------------------------------------------------------------------------------------------------
$product_id_string = $_POST['custom'];
$product_id_string = rtrim($product_id_string, ","); // remove last comma
// Explode the string, make it an array, then query all the prices out, add them up, and make sure they match the payment_gross amount
$id_str_array = explode(",", $product_id_string); // Uses Comma(,) as delimiter(break point)
$fullAmount = 0;
foreach ($id_str_array as $key => $value) {
    
$id_quantity_pair = explode("-", $value); // Uses Hyphen(-) as delimiter to separate product ID from its quantity
$product_id = $id_quantity_pair[0]; // Get the product ID
$product_quantity = $id_quantity_pair[1]; // Get the quantity
$sql = mysql_query("SELECT price FROM products WHERE id='$product_id' LIMIT 1");
    while($row = mysql_fetch_array($sql)){
	$product_price = $row["price"];
}
$product_price = $product_price * $product_quantity;
$fullAmount = $fullAmount + $product_price;
}
$fullAmount = number_format($fullAmount, 2);
$grossAmount = $_POST['mc_gross']; 
if ($fullAmount != $grossAmount) {
        $message = "Possible Price Jack: " . $_POST['payment_gross'] . " != $fullAmount \n\n\n$req";
        mail("xxx", "Price Jack or Bad Programming", $message, "From: xxx" );
        exit(); // exit script
} 
// END ALL SECURITY CHECKS NOW IN THE DATABASE IT GOES ------------------------------------
////////////////////////////////////////////////////
// Homework - Examples of assigning local variables from the POST variables
$txn_id = $_POST['txn_id'];
$payer_email = $_POST['payer_email'];
$custom = $_POST['custom'];
// Place the transaction into the database
$sql = mysql_query("INSERT INTO transactions (product_id_array, payer_email, first_name, last_name, payment_date, mc_gross, payment_currency, txn_id, receiver_email, payment_type, payment_status, txn_type, payer_status, address_street, address_city, address_state, address_zip, address_country, address_status, notify_version, verify_sign, payer_id, mc_currency, mc_fee) 
   VALUES('$custom','$payer_email','$first_name','$last_name','$payment_date','$mc_gross','$payment_currency','$txn_id','$receiver_email','$payment_type','$payment_status','$txn_type','$payer_status','$address_street','$address_city','$address_state','$address_zip','$address_country','$address_status','$notify_version','$verify_sign','$payer_id','$mc_currency','$mc_fee')") or die ("unable to execute the query");

mysql_close();
// Mail yourself the details
mail("xx", "NORMAL IPN RESULT YAY MONEY!", $req, "From: xx");
?>

[Psycho]

thank you

 

but how would i update my ordered table after the transaction is complete by paypal, would that be done by my IPN script?

 

Don't you keep track of orders already? Shouldn't be anything additional you would need to do.

[alpha1]

no currently all it does is get an order and email me letting me know, i need to put it into my system so i can keep a recprd of it also

[PFMaBiSmAd]

You would keep track of stock using a credit/debit balance system, the same as your checking account or credit-card account. You would not keep just a count, but keep information about each transaction that added to or subtracted from the total. The current total would be calculated by summing the added/subtracted amounts. The following addresses only the actual tracking of stock -

 

When you receive/add stock, you would add a row to your `stock` table that list the who, what, quantity, when, where, why information - who add that stock, what it is (its id), quantity (as a positive number), when it was added, where it is at (in case you store same items in more than one location), and why it was added (stock replenishment, return restocked, ...) By storing and using the id, you would have just one `stock` table that contains the records for all items.

 

When you remove stock, you would add a row for each item, listing who pulled the item, what (its id), quantity (as a negative number), when it was pulled, where it was pulled from (in case you store same items in more than one location and you want to track where items were pulled from), and why it is being deducted/pulled (shipped as part of an order, breakage, loss due to theft, shipped as a replacement to a customer, sample, ...)

 

You would query for the current quantity in stock by summing the quantity column (plus and minus numbers) and grouping by the id(s) you are interested in (one specific id, a list of specific id's, all id's,...)

 

All of this is separate from order tracking, where you would keep track of what items (by their id's) are part of each order and what the order status is (ordered, payment confirmed, released for shipping, shipped, received.) Any automation related to confirmation from your payment gateway would just change the status of an order. Each step in the order process would again be a separate recored in a table so that you can keep track of dates, payment confirmation numbers, who/what entered the information manually/automatically, notes associated with the status for that step,...

 

You would query your `stock` table to find out if an item needs to be reordered because it is below your minimum stock level for that item and what sort of messages you display on your product page (quantity on hand, out of stock/back ordered) and while a visitor is adding the item to their cart and during order processing (you have selected more than the available quantity, do you wish to reduce the quantity or place the order with the selected quantity? , the following items are back ordered and will ship later...)

[alpha1]

thank you, you've been very helpful. im going to start working on this and see how it goes