inventory, writing query

Hi,
Once a purchase is made, I want the quantity to be subtracted from my inventory table. My table is set up with inventoryid, prodid, catid, size(if present) and quantity. All products are represented in this table. If I were not working with recordsets and just doing the script with php I would use the query:
$query = "UPDATE inventory set inventory = inventory - $inventory WHERE inventoryid = $inventoryid";
$result7b = mysql_query($query) or die(mysql_error());
and this would update my inventory.
This does not work in my confirm script. I have set up the following rs in the confirm script:
mysql_select_db($database_connMegan, $connMegan);
$query_rsInventory = "SELECT * FROM inventory INNER JOIN products ON products.prodid = inventory.inventoryid";
$rsInventory = mysql_query($query_rsInventory, $connMegan) or die(mysql_error());
$row_rsInventory = mysql_fetch_assoc($rsInventory);
$totalRows_rsInventory = mysql_num_rows($rsInventory);
and the following session:
<?php
if (!session_id()) session_start();
if($_SERVER["REQUEST_METHOD"] == "POST") {
$_SESSION["stock"] = "quantity= quantity- $quantity";
}
?>
How do I write the query, so that the quantity is subtracted on product purchase.
Thank you

You should be able to do this with a regular update query like the one that you have. You should do the math for the new inventory before you have the query, then use the new value for the inventory update.

So to start you would need to query for the inventory that is present filtering the rs on the product id. You would then store this inventory value in a variable and subtract the quantity purchased from this value. You would then store the difference in a new variable and use this variable in your inventory update.

To make this work with all of the items you would need to have a custom loop that would make this recordset and update query execute once for each item in the cart.

Hi,
I have decided that the inventory can be updated in the back office (I used my own coding for it). When the manager goes to process the item for shipping the inventory will be updated. In order to do that I added a new column to the order_items table for the inventoryid to be passed from the inventory table to the order_items table. In the confirm.php page I have the record set rsInventory which is inner joined with the products table. The record set brings up correct info when I test it. I added a session variable inventoryid. I added the information to the order_items query. When I get to the confirm page something is not working since I get the message:
Notice: Undefined variable: row_rsInventory in /Applications/MAMP/htdocs/megan_isaacs_cms/confirm.php on line 15

Notice: Undefined offset: 5 in /Applications/MAMP/htdocs/megan_isaacs_cms/WA_eCart/WA_eCart_Database_PHP.php on line 17
Column 'inventoryid' cannot be null
Not sure what I did wrong, since the ecart system uses the database.php script.
Thank you

In the code that you have on lines 12-17 are referencing a column from your inventory recordset. The recordset does not occur until after line 73. You must move this code to be after your recordset in order to specify a column from this recordset.

I did move the session under rsInventory and I still get a message:
Notice: Undefined offset: 5 in /Applications/MAMP/htdocs/megan_isaacs_cms/WA_eCart/WA_eCart_Database_PHP.php on line 17
Column 'inventoryid' cannot be null
I looked at the database.php and could not figure what it is asking for.
Thank you

The error is telling you that you cannot have a null value for the inventoryid column in your store order details server behavior that starts on line 178.

In this server behavior you are setting the inventoryid column to the value of the $_SESSION['inventoryid'] variable. This variable is being set to the recordset value for the inventoryID like this:

$_SESSION["inventoryid"] = "".$row_rsInventory['inventoryid']  ."";

Based on the result that you are getting it seems that this recordset is not getting a value for this column. Have you tested the recordset? It seems that there is no filtering occurring on this recordset so you would only ever get the first one in your db.

I think that if you got this recordset setting a value for the session variable it would work for you. To test if you are getting a value from the rs you can add in a die statement during the setting of this variable like this:

code starting on line 71:

if (!session_id()) session_start();

if($_SERVER["REQUEST_METHOD"] == "POST")     {

  $_SESSION["inventoryid"] = "".$row_rsInventory['inventoryid']  ."";

  die("the value of the rsInventory.inventoryid is: " . $row_rsInventory['inventoryid'] . " and the value of the session variable inventoryid is: " . $_SESSION["inventoryid"] . "");

}

This will let you know if the value you are setting this session variable to is present and what the values are after you set the variable. If you see the correct results here the next thing to check would be the value of this session variable when the store order details is occurring. You can add in the same die statement to the top of that server behavior and comment out the one here to see what the values are at that time.

Hi Eric,
Thank you for your reply it did help me to understand that I am not doing something correctly. Here is what I get as a result of the statement:
the value of the rsInventory.inventoryid is: 1 and the value of the session variable inventoryid is: 1
So as you said it is picking up the first db record. I have worked the query seven ways to sunday. Not sure how to change the session since I want to retrieve the inventoryid from the inventory table. Here is my latest query which returns to me the statement above:
mysql_select_db($database_connMegan, $connMegan);
$query_rsInventory = "SELECT inventory.inventoryid, inventory.prodid, inventory.catid, inventory.`size`, inventory.quantity, products.prodid, products.catid, products.itemnumber, products.price FROM inventory, products WHERE inventory.prodid = products.prodid AND inventory.inventoryid = inventory.inventoryid";
$rsInventory = mysql_query($query_rsInventory, $connMegan) or die(mysql_error());
$row_rsInventory = mysql_fetch_assoc($rsInventory);
$totalRows_rsInventory = mysql_num_rows($rsInventory);

if (!session_id()) session_start();
if($_SERVER["REQUEST_METHOD"] == "POST") {
$_SESSION["inventoryid"] = "".$row_rsInventory['inventoryid'] ."";
die("the value of the rsInventory.inventoryid is: " . $row_rsInventory['inventoryid'] . " and the value of the session variable inventoryid is: " . $_SESSION["inventoryid"] . "");
}
When I test the recordset, all of the product info lines up with the inventoryid info. Not sure what you mean by filtering.
Thank you

It is good that you were able to get the query worked out a little better. What I'm unsure of and why I mentioned that there is no filtering occurring is that it seems you are getting the inventory for items from your db, but there is no filtering for a particular item. With this recordset you would get back info about the inventory for all of the products.

If you are trying to update the inventory for a particular item then this will not work. You must ensure that you are getting the inventory for the specific item you are trying to update. If you are wanting to do this for each item in the cart that is purchased you will need to set it up differently.

Please post back and describe a little more about what you are hoping to accomplish as an end result with this and how it is working for you now. If the query and setup that you have are working for you then there is no need to change things.

Since my clients pieces are all custom made, her inventory is low and she can always make a piece. So all I want to do is have the inventoryid posted to the order_items table. Then when she goes into the back office and posts the order at shipping, the inventory is updated.
Thank you

It seems to me that you will not be storing the correct inventory id in your order details. Regardless of what items are purchased you will always have the same inventory id stored in the db.

To get it storing the inventory id for the specific item you would need to do some customizing. It would probably be easiest to add the recordset inside of the loop for the store order details server behavior. Then just filter it on the item id. This part is a little custom and will require some hand coding inside of the store order details.

You could also have a column in the cart to hold this value, then just store the value from the cart column rather than the recordset.