Clash Royale CLAN TAG#URR8PPP

PHP Deleting multiple rows with returning values to another table

I have a code here that deletes a single row of data from sales_order and then returns the value of the qty to the products table.

sales_order

qty

<?php
include('connect.php');
//get values from href
$id=$_GET['id'];
$invoice=$_GET['invoice'];
$qty=$_GET['qty'];
$product=$_GET['product'];

//returns quantity to stock
$sql = "UPDATE products
SET stock=stock+?
WHERE productid=?";
$q = $db->prepare($sql);
$q->execute(array($qty,$product));

//execute query
$result = $db->prepare("DELETE FROM sales_order WHERE transaction_id= :id");
$result->bindParam(':id', $id);
$result->execute();
header("location:pos.php?invoice=$invoice");
?>


Now what I'm aiming to do here is to have a reset button that will delete all rows with the same invoice number from sales_order and then each row returns the quantity value to the products table. I can't seem to find a way to do it. Can anyone help me?

2 Answers
2

You can do an UPDATE with a JOIN:

UPDATE

JOIN

UPDATE products AS p
JOIN sales_order AS s on s.productid = p.productid
SET p.stock = p.stock + s.quantity
WHERE s.invoice_number = :invoice


Then you can delete all the sales_order rows with that invoice number. You should use a transaction to ensure that this is all done atomically.

sales_order

I'm making some assumptions above about the schemas of the tables, you'll need to adjust to your actual column names.

I think you can go with an array concept. I think its simple to retrieve all the required values using select query before deletion. Then you can save it to the required tables. The you can delete the values

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.