0

I need to update my table with multiple request. With a form, i get some values that i will take for update my table.

  $products =  [
    'prodotto1' => [
      'referenza' => $_POST['referenza1'],
      'prezzo' => $_POST['prezzo1'],
    ],
    'prodotto2' => [
      'referenza' => $_POST['referenza2'],
      'prezzo' => $_POST['prezzo2'],
    ],
    'prodotto3' => [
      'referenza' => $_POST['referenza3'],
      'prezzo' => $_POST['prezzo3'],
    ]
  ];

I thought to do this with a foreach :

function updateProducts(array $params){
    $pdo = $GLOBALS["db"];
    $sql = "UPDATE product SET REFERENCE=:reference, PRICE=:price WHERE REFERENCE=:reference";
    $stmt= $pdo->prepare($sql);

    foreach ($params as $prodotto ){
        if ($prodotto['referenza'] && $prodotto['prezzo'] ){
            $stmt->bindParam(':reference', $prodotto['referenza'], PDO::PARAM_STR);
            $stmt->bindParam(':price', $prodotto['prezzo']);
            $stmt->execute();

        }
    }
}

But i'm not sure that is the best way to do this.

Can u suggest me some goods practice ?

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
s.elettro
  • 55
  • 1
  • 8
  • 2
    Looks fine the way you have it. What do you believe is wrong with what you did. I might be tempted to wrap a Transactions around the multiple updates just to be sure you get them all updated or none of them. – RiggsFolly Feb 21 '22 at 11:02
  • 1
    Questions like this are better asked on [Code Review Site](https://codereview.stackexchange.com/) – RiggsFolly Feb 21 '22 at 11:04
  • You may want to add a transaction, but other than that the approach is OK. Seе https://stackoverflow.com/questions/9819271/why-is-mysql-innodb-insert-so-slow – Your Common Sense Feb 21 '22 at 11:09
  • 1
    Using $GLOBALS is a bad thing tho – Your Common Sense Feb 21 '22 at 11:10
  • I think the [INSERT ... ON DUPLICATE KEY UPDATE](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html) is a good way to update multiple rows in one query. It has the "added feature" that when a row doesn't yet exist it inserts it. You'll have to test the performance, because just having one query doesn't mean it is more efficient. – KIKO Software Feb 21 '22 at 11:17
  • Thank you guys ! @YourCommonSense why is bad way to use GLOBALS ? – s.elettro Feb 21 '22 at 11:21
  • 1
    First of all, functions are used to isolate their content from the global scope. By using GLOBALS you break that feature. Secondly, it will be harder to debug your code, because you cannot test this function independent from it's global environment. In short, supply the database as an argument to this function. (did I [miss anything](https://stackoverflow.com/questions/1557787/are-global-variables-in-php-considered-bad-practice-if-so-why)? Yes, [I did](https://www.quora.com/Why-are-PHP-globals-bad)) – KIKO Software Feb 21 '22 at 11:24
  • @KIKOSoftware when you are using INSERT query to UPDATE a row, you are BOUND to insert some UNWANTED rows. Each query type should be used exactly for its purpose, not as a dirty hack solely to appease some vague feeling – Your Common Sense Feb 21 '22 at 11:44
  • @YourCommonSense: That's a good point. I always say to others to only use things for their intended purpose, and here I seem to go against that. Nevertheless this method can be useful it you actually need the "added feature". I was hoping the quotes would imply the necessary amount of irony. – KIKO Software Feb 21 '22 at 11:50

0 Answers0