How to Update Stock Quantity Using MySQL in PHP

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



Leave a reply



Submit