Updating multiple database tables when products are shipped back by customer

Question

So, in this example I have to make these changes in my tables:

1. Select Product Data => SKU, QTY

2. Insert into shipping_back table

3. Update in shipping_back to current date

4. Update in customers to another status

5. Delete from barcode_shipping_back table

6. Delete from shipping table.

Here is my code:

$datetime = date('Y-m-d H:i:s');
//UPDATE INVENTORY FUNCTION
function updatequery($connpdo, $productsarray){
 foreach ($productsarray as $productarray){
 $stmtupdateQTY = $connpdo->prepare("UPDATE invertory SET qty = qty + :qty WHERE sku = :productsku;");
 $stmtupdateQTY->execute([":qty"=>$productarray["qty"],
 ":productsku"=>$productarray["sku"]]);
 }
 }
 
$stmtGetStatus = $connpdo->query("SELECT barcode FROM barcode_shipping_back WHERE status = '1'");
if($stmtGetStatus->rowCount() > 0){
while ($row = $stmtGetStatus->fetch(PDO::FETCH_ASSOC))
{
 $productswithdetails = array();
 //// GET THE PRODUCTS SKU/QTY FROM THE ORDER
 
 $stmtgetproductdata = $connpdo->prepare("SELECT products.productqty, products.sku FROM `orders` inner join orderitems on orderitems.orderid = orders.orderid inner join shipping on shipping.orderid = orders.orderid inner join products on products.productid = orderitems.productid WHERE shipping.tracking_id =:tracking_id");
 $stmtgetproductdata->bindValue(":tracking_id", $row["barcode"]);
 $stmtgetproductdata->execute();
 
 while ($rowproduct = $stmtgetproductdata->fetch()) {
 $productqty = $rowproduct['productqty'];
 $productSKU = $rowproduct['sku'];
 $productswithdetails[] = array("sku" => $productSKU,
 "qty" => $productqty);
 }
 
 
 //// Insert TO SHIPPING BACK
 
 $stmtgetproductdata = $connpdo->prepare("INSERT INTO shipping_back(orderid, shipping, shipping_id, shipping_price, tracking_id, barcodetext, delivered, import_date) SELECT shipping.orderid, shipping.shipping, shipping.shipping_id, shipping.shipping_price, shipping.tracking_id, shipping.barcodetext, shipping.delivered, shipping.import_date FROM shipping WHERE tracking_id = :barcode");
 $stmtgetproductdata = ->execute([":barcode"=>$row["barcode"]]);
 
 //// UPDATE SHIPPING BACK WITH DATE
 
 $stmtupdateshippingback = $connpdo->prepare("UPDATE shipping_back SET upload_date = :datetime WHERE tracking_id = :barcode");
 $stmtupdateshippingback = ->execute([":datetime"=>$datetime,
 ":barcode"=>$row["barcode"]]);
 
 /// UPDATE CUSTOMERS
 
 $stmtupdatecustomers = $connpdo->prepare("UPDATE customers cus SET cus.o_status = '88' WHERE EXISTS( SELECT * FROM shipping s INNER JOIN orders o on o.orderid = s.orderid WHERE s.tracking_id = :barcode AND cus.id = o.customerid)");
 $stmtupdatecustomers = ->execute([":barcode"=>$row["barcode"]]);
 
 /// IF ALL THE SQL QUERY WAS SUCCESSFUL THEN DELETE AND RUN THE INVERTORY FUNCTION
 if ($stmtgetproductdata && $stmtinsertproductdata_back && $stmtupdateshippingback && $stmtupdatecustomers) {
 
 updatequery($connpdo, $productswithdetails);
 
 $stmtdeletefrombarcodeshipping = $connpdo->prepare("DELETE FROM barcode_shipping_back WHERE barcode = :barcode");
 $stmtdeletefrombarcodeshipping = ->execute([":barcode"=>$row["barcode"]]);
 
 $stmtdeletefromshipping = $connpdo->prepare("DELETE FROM shipping WHERE tracking_id = :barcode");
 $stmtdeletefromshipping = ->execute([":barcode"=>$row["barcode"]]);
 }
 
 else {
 echo "ERROR";
 }
}
$message["success"] = "1";
$message["text"] = "Success";
echo json_encode($message);
}
else {
 $message["error"] = "1";
 $message["text"] = "There are no SQL ROWS";
 echo json_encode($message);
}

Or should I use in some way PDO transactions?

• BCdotWEB: The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please see How do I ask a good question?.

Answer 1

1. First and most importantly, yes, you should definitely be wrapping these actions in transactions because if a single "write" fails, then all related processes should be rolled back.
2. It shouldn't ever be necessary to declare date('Y-m-d H:i:s') and feed that PHP layer value into a query. All SQL dialects are perfectly capable of offering a datetime stamp -- in MySQL, you'd use CURRENT_TIMESTAMP. This removed a little bit of needless noise in your script since it is one less thing to bind. In certain circumstances, you may prefer to use a trigger or similar approach to automatically update a [last_updated] column. Stack Overflow has quite a few (redundant) pages that demonstrate this technique.
3. Prepared statements should never be declared inside of a loop if they do not change. Write them before the loop and use them over and over -- that's how they are designed to work.
4. I don't use bind() with PDO since the execute() call is perfectly capable of receiving the values.
5. Why is updatequery() the only subprocess separated from the global scope? I like it -- it is readable, doesn't need commenting, and encapsulates logic. I'd probably suggest that you tidy up your entire script in the same fashion.
6. if($stmtGetStatus->rowCount() > 0){ seems unnecessary. If there are no rows, then the subsequent loop will simply not be entered. I'd remove this part.
7. An extremely long line of SQL is very annoying to read because of the tedious horizontal scrolling. Please endeavor to obey line width limits described in PSR-12 coding standards. Line breaking between each clause in your SQL will make things much easier to maintain.
8. Generally speaking making iterated queries (trips to the database) is not ideal. I didn't bother trying to determine if this is avoidable for your task -- maybe it isn't. That said, if a PROCEDURE would help to perform this task, it might be worth investing time into.
9. I see that if ($stmtgetproductdata && $stmtinsertproductdata_back && $stmtupdateshippingback && $stmtupdatecustomers) { is checking for truthy outcomes, but not specifically checking for affected rows. Assuming each action MUST affect at least one row, I think it would tighten up your script to make these checks explicitly.
10. When printing the conditional outcomes as JSON, please be careful to standardize the payload. If this script is being called by, say AJAX, your receiving script will need to parse what is returned. For this reason, do not mix iterable payloads with scalar payloads. After json decoding, Error is not iterable, but your other outcomes are iterable. Also, of the two iterable payloads, some keys/properties are not declared. I recommend having success as 0 or 1 and text being whatever informative string you like.
11. All printed data should be json encoded. For this reason, every branch should create a payload ($message) and then json_encode($message) should be called unconditionally at the end of the script.

• kviktor1230: Thanks for your help! What do you think about this code? I edited by your answer. pastebin.com/R8HJ96jM Thank you! Btw. I couldn't figure it out how to insert the "upload date" with only SQL query. Im using mariadb
• mickmackusa: I think pastebin looks horrible on mobile. I think you should re-read my #3, #9, and #11. Also, please write a space on both sides of the => between a key and a value. Write all MySQL keywords and functions in ALL_CAPS for readability. And when declaring a placeholder don't write a space between : and the name. mariadb.com/kb/en/current_timestamp
• kviktor1230: New edit: pastebin.com/SNypJLuZ But I still I dont get how to declare PDO before the while loop. In while loop I get all the tracking_id and barcode which I need to work with. Thank you again!