Product Batch Price updating Cart

I got following tables: Products | ProductBatch

Product contains: ID, ProductName, CatID,
Product Batch contains, ID, ProductID, ProductPrice, ExpDate
Each product can have different batch of products so price do vary so price is added on product batch table.

Now on product detail page i got a ionSlider as to choose how many quantity of product is required.
Screenshots of eCart & ATC button attached.

When a product has only one batch then product with BatchName is found on cart.
But when there 2+ batches then when i choose a slider's quantity it doesn't add relevent quantity. But enter's other slider's values.

Why is this happening & how do we fix it?

i moved form within the repeat region.. seems to work. but is it safe?

Also how can we display message on cart if in this case the product quantity is out of stock.

I followed up using this: http://www.webassist.com/tutorials/Inventory-management-shopping-cart-IV

I tried a lot . but i think some changes are required in here:

$query_WADAProducts = sprintf("SELECT ProductStock - (SELECT Coalesce(SUM(DetailQuantity),0) FROM orders Inner Join orderdetails ON orderdetails.DetailOrderID = orders.OrderID WHERE OrderDate > ProductUpdateDate AND DetailProductID = ProductID) AS RemainingInventory FROM products WHERE ProductID = %s", GetSQLValueString($IDParam_WADAProducts, "int"));
$WADAProducts = mysql_query($query_WADAProducts, $Sample_Connection) or die(mysql_error());
$row_WADAProducts = mysql_fetch_assoc($WADAProducts);
$totalRows_WADAProducts = mysql_num_rows($WADAProducts);
?>

My tables are Products & ProducBatch
Product contains: ID, ProductName, CatID,
ProductBatch contains, ID, ProductID, ProductPrice, ExpDate, Quantity, BatchName.

i changed the sql to :

SELECT Quantity - (SELECT Coalesce(SUM(DetailQuantity),0) FROM ProductBatch, orders Inner Join orderdetails ON orderdetails.DetailOrderID = orders.OrderID WHERE OrderDate > ProductUpdateDate AND DetailProductID = Products.ID) AS RemainingInventory FROM products WHERE ProductID = %s

as my Quantity is present on ProductBatch table & column as Quantity.

But in cart i m getting error as : Unknown column 'Quantity' in 'field list'

What is the name of the quantity field in your products table? That error implies that it is not 'Quantity'.

There is no Quantity field in Products table. Quantity field is in ProducBatch.

ProductBatch contains : ID, ProductID, BatchName, Quantity, ExpDate & etc...

Quantity is in the outer Select statement which only returns results from the products table.

Your query is wrong and I could only guess without knowing your table structure.... Maybe what you are going for is:

SELECT Quantity - (SELECT Coalesce(SUM(DetailQuantity),0) FROM orders Inner Join orderdetails ON orderdetails.DetailOrderID = orders.OrderID WHERE OrderDate > ProductUpdateDate AND DetailProductID = Products.ID) AS RemainingInventory FROM products INNER JOIN ProductsBatch on products.ProductID = ProductsBatch.ProductID WHERE ProductID = %s

I have attached table structure of Products & ProductBatch.

After adding the sql product doesn't seem to add to cart at all. Cart is empty.
Cart.php also attached.

I'm afraid this is beyond what I can effectively do on this forum. I'd suggest a premier support ticket to look into this and help you write the proper SQL statement. I'm not even sure where and how this is all used or how it fits into your site... there is just too much custom research necessary to do effectively here.

Well cart was appearing empty dude to earlier test orders .

Your sql works awesomely. But Quantity not on proper product.

i added two product's batch in cart of same ProductID. But quantity ain't responding well in cart.

ProductBatch data attached. & also cart screenshot attached.
1 - BATCH: DSF234 Quantity : 500 it is valid in cart.
2 - BATCH: SNKFJ-12 Quantity: 123 it is not valid in cart. (displays 945)
3 - BATCH: XYZ Quantity : 945. (2,3 and same product but different batch products)