How to update stock quantity according to their batch numbers
WARNING Your current code is vulnerable to SQL injection. Make sure to always use parameterized prepared statements and never use die(mysqli_erro($con))
in your code.
You can solve the problem without any PHP logic. It is always better to do such operations in one step in SQL. However, this task is not very easy to do in one step, but it is possible with one trick. The trick is to use a temporary variable in the UPDATE
statement to keep track of used inventory.
$con->begin_transaction();
$con->query('SET @qty = 7');
$stmt = $con->prepare('UPDATE purchase_order
SET qty_avbl = qty_avbl-(@bought := LEAST(@qty,qty_avbl)),
qty_avbl = if(@qty := @qty-@bought, qty_avbl, qty_avbl)
WHERE pid = ?
ORDER BY batch_number');
$stmt->bind_param('s', $pid);
$stmt->execute();
$con->commit();
Query for update stock quantity in transaction
Is code below the solution (fiddle)?
with cte as(
select * from(
select *, case when cumulsum <= TotalRequiredQuantity then 0 else cumulsum-TotalRequiredQuantity end NewQuantity
from(
select *, 20 TotalRequiredQuantity,/*Set valid quantitity*/
sum(stock_quantity) over(partition by item_code order by stock_expired) cumulsum
from stocks_table
where item_code = 'I0015'/*Set valid item_code*/
)q
)q1
where stock_quantity>=NewQuantity)
update stocks_table st
join cte on st.id=cte.id
set st.stock_quantity = NewQuantity
without common table expression:
update stocks_table st
join(
select * from(
select *
,case when cumulsum <= TotalRequiredQuantity then 0 else cumulsum-TotalRequiredQuantity end NewQuantity
from(
select *, 20 TotalRequiredQuantity,/*Set valid quantitity*/
sum(stock_quantity) over(partition by item_code order by stock_expired) cumulsum
from stocks_table
where item_code = 'I0015'/*Set valid item_code*/
)q
)q1
where stock_quantity>=NewQuantity
)cte on st.id=cte.id
set st.stock_quantity = NewQuantity
How to insert stock quantity and update into another table?
What you're missing is that you need to concatenate the queries, with the use of =
assignment operator, you've just overridden the former string, so it should be like this
foreach($_POST['fk_product'] as $index => $searchid){
$sql = "INSERT INTO supplier (supplier_name, date_in, fk_product, quantity_in) VALUES ('".$_POST['supplier_name']."','".$_POST['date_in']."','".$_POST['fk_product'][$index]."', '".$_POST["quantity_in"][$index]."')";
$quantity_bal = $_POST['quantity_bal'];
if($quantity_bal == 0){
//I'm presuming that the quantity_ori is a variable here 'coz you're trying to sum up with the original to the latest one
$sql .= "UPDATE product set quantity_bal= $quantity_ori + '".$_POST["quantity_in"][$index]."' WHERE id_produk = '".$_GET['id']."'";
}else if($quantity_bal != 0){
$sql .= "UPDATE product set quantity_bal= $quantity_bal + '".$_POST["quantity_in"][$index]."' WHERE id_produk = '".$_GET['id']."'";
}
}
We changed the =
symbol in your update queries into .=
and then your quantity_ori
earlier doesn't have the $
symbol, since we're presuming it's a variable, don't forget the $
How to update stock table after making a sale
After inserting to sold
table, update the manf
table as well:
mysql_query("INSERT INTO sold_goods
(itemname, itemcode, itemtype, unitprice, quantity,
transactiontype, customercategory, Date)
VALUES
('$itemname', '$itemcode', '$itemtype', '$unitprice',
'$quantity', '$ttype', '$ccat', '$idate')");
mysql_query("UPDATE manuf SET qtyleft = qtyleft - $quantity where
itemcode = '$itemcode'" );
PHP Mysql Update Product Stock/Qty
Your UPDATE query should be this:
$sql_Qty = "UPDATE products SET `stock` = 'stock-".$_POST['txtQty'.$i]."' WHERE `idprod` = '".$_POST['txtidp'.$i]."'";
Related Topics
Php Substring Remove Everything in a String After Certain Character
How to Download an Mp3 File from Remote Url
Php Curl Required Only to Send and Not Wait for Response
Validating Base64 Encoded Images
Get Selected Option in PHP Without Pressing Submit
Laravel: Products,Categories and Subcategories! (Relation Ships)
Phpexcel Write Long Number in Cell
How to Count Occurrence of Duplicate Items in Array
Getting Data from a Textarea into a Database
How to Update Multiple Select Box Values in PHP MySQL
Php Session Lost After Redirect
How to Get Values of Multiple Selected (Dynamic) Checkbox in PHP
Secure Hash and Salt For PHP Passwords
How to Use Return Inside a Recursive Function in PHP