-1

I'm trying to add products to a database from an XML file and when there's a duplicate article number I want to just update the stock level. I'm still learning PHP and MySQL and I've read numerous post on this forum but I just can't get it to work.

So what I did is this:

$xml = simplexml_load_file("a-link-to-downloaded_products.xml") or die("Error: Cannot create object");

  foreach ($xml->children() as $row) {
      $article_code = $row->artikelnummer;
      $brand = $row->merk;
      $name_nl = $row->naam;
      $ean = $row->ean; 
      $stock = $row->voorraad_aanwezig; 

      $sql = "INSERT INTO `products` (article_code,brand,name_nl,ean,stock) VALUES ('" . $article_code . "','" . $brand . "','" . $name_nl . "','" . $ean . "','" . $stock . "') ON DUPLICATE KEY UPDATE `stock` = VALUES(`$stock`)";

      $result = mysqli_query($db, $sql);
      ..... etc .....
  }

Above gives me an error saying

Unknown column '1' in 'field list'

or

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1)' at line 1

Because of that second error I assume that it has something to do with ON DUPLICATE KEY UPDATE stock = VALUES($stock)" However I tried a lot of different variations but I just can't get it to work! I used backticks, quotes etc. Almost anything I can think of.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Meules
  • 1,349
  • 4
  • 24
  • 71

1 Answers1

2

Just replace this:

ON DUPLICATE KEY UPDATE `stock` = VALUES(`$stock`)

With:

ON DUPLICATE KEY UPDATE `stock` = VALUES(`stock`)

Explanation: the VALUES() construct in the ON DUPLICATE UPDATE clause is used to reference a column value that is passed in the INSERT clause.

Important note: anyone on SO will tell you that you should really consider using prepared statement and parameterized queries, in order to make your queries safer and more efficient.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Ok to be sure... `VALUES('stock')` references to the column name `stock` in my db? About that security issue... I'll have a look into that, for now it's just something I'm trying to learn so this is my very first PHP project :) Anyway it works perfectly.... I understand now that "1" in the error since I tried to look for a column name "1" instead of stock. – Meules Oct 04 '19 at 18:52
  • @Meules: VALUES(`stock`) (with backticks, not single quotes) references the value that was originally given for INSERT in column stock. For more information you can have a look at [the documentation](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html). – GMB Oct 06 '19 at 07:41