0

Im working on invertory management system and I'm not sure if this is the best way to substract product quantity.

This query (function) will be executed when the orders will be exported to courier services.

First of all I get the product details. In this example the quantity and SKU code.

 //// GET PRODUCT DETAILS 
$stmtgetproductdata = $connpdo->prepare("SELECT products.sku, products.productqty FROM `orders` inner join orderitems on orderitems.orderid = orders.orderid inner join products on products.productid = orderitems.productid WHERE orders.customerid =:id");
        $stmtgetproductdata->bindValue(":id", 'ORDERID');
        $stmtgetproductdata->execute();
        $productswithdetails = array();

In while loop I put these informations from product to array.

 while ($rowproduct = $stmtgetproductdata->fetch()) {
      $productQTY = $rowproduct['productqty'];
      $productSKU = $rowproduct['sku'];
      $productswithdetails[] = array(
      "sku" => $productSKU,
      "qty" => $productQTY
      );
  }

And in end of the code (if everything was sucessfull) I run updatequery() function:

function updatequery($connpdo, $productarray){
         foreach ($productarray as $product){
    $stmtupdateQTY = $connpdo->prepare("UPDATE invertory SET qty = qty - :qty WHERE sku = :productsku;");
    $stmtupdateQTY->execute([":qty"=>$product["qty"],
                             ":productsku"=>$product["sku"]]);
 }
}

I need foreach is because there can be more then 1 product / order.

What do you think? Is this a safe way to update the inventory table? Thank you!

kviktor1230
  • 101
  • 7
  • 2
    Declaring your `prepare()` inside of the loop is not best practice. Unless you are logging each individual row change, making iterated trips to the database is not best practice. Why not just do all the updates in a single query (with no SELECT query at all)? I feel like I just explained this a few days ago: https://stackoverflow.com/a/71366494/2943403 Your question would benefit from offering a db-fiddle demo link. – mickmackusa Mar 09 '22 at 13:51
  • 3
    I expect you can probably do this all in one UPDATE...FROM statement – ADyson Mar 09 '22 at 13:52
  • No, Its not possible in my case unfortunately because I can update the quantity (or run the updatequery function in this example) when all the other functions in my file were successful (returned true). But I need to SELECT product datas at the beginning of the file. – kviktor1230 Mar 09 '22 at 14:02
  • When you say "But I need to SELECT product datas at the beginning of the file.", do you actually mean "I _think I_ need to SELECT product datas at the beginning of the file."? [XY Problem](https://meta.stackexchange.com/q/66377/352329)? – mickmackusa Mar 09 '22 at 14:05
  • Well no, I mean "I need to SELECT". Actually I'm selecting not only the quantity and the SKU but also the weigt and price for the courier services. And if the export was sucesful to courier services then I update the qty in invertory table. – kviktor1230 Mar 09 '22 at 14:08
  • 1
    Then you probably need to LOCK selected records AND a transaction. – Your Common Sense Mar 09 '22 at 14:17

0 Answers0