ON DUPLICATE KEY UPDATE in prepared statement with array

Question

My code so far:

$query = 'INSERT INTO articles(id, name, picture, url, origAmount, currAmount, added) VALUES ';

$insertQuery = array();
$insertData = array();

$date = getDate();

foreach ($articles as $article) {
    $insertQuery[] = '(?,?,?,?,?,?,?)';
    $insertData[] = $article->id;
    $insertData[] = $article->name;
    $insertData[] = $article->picture;
    $insertData[] = $article->url;
    $insertData[] = $article->amount;
    $insertData[] = $article->amount;
    $insertData[] = $date['year'] . '-' . $date['mon'] . '-' . $date['mday'];
}

if(!empty($insertQuery)) {          
    $query .= implode(', ', $insertQuery);
    $handle = $this->link->prepare($query);
    $handle->execute($insertData);
}

If the id already exists I want to update only currAmount. I have tried

$insertQuery[] = '(?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE currAmount = (currAmount)';

which gives

'SQLSTATE[42000]: Syntax error or access violation: 1064 [...] near '('0192801635','The Renaissance A Ve' at line 1'

So the literal content of the row, it inserted fine on the empty table though, so it's not a problem with the content per se.

How can I do this?

EDIT: SOLVED IT

After the implode, added

$query .= ' ON DUPLICATE KEY UPDATE currAmount = VALUES(currAmount)';

Thanks everyone.

@jeroen it seems to work for [this guy](https://www.tutorials.de/threads/mysql-insert-mit-duplicate-key-update.394298/), but he put the values in by hand, which I can't do. — Lennart Bergmann, Oct 03 '17 at 12:02
since your insertquery is an array you have to use foreach loop and execute single query at a time — Anil Shrestha, Oct 03 '17 at 12:06
Possible duplicate of [On Duplicate Key Update same as insert](https://stackoverflow.com/questions/14383503/on-duplicate-key-update-same-as-insert) — alaminio, Oct 03 '17 at 12:07
You can use REPLACE. https://dev.mysql.com/doc/refman/5.7/en/replace.html — Maxi Schvindt, Oct 03 '17 at 12:15

Alex Kapustin · Answer 1 · 2017-10-05T03:24:31.220

0

... ON DUPLICATE KEY UPDATE currAmount=values(currAmount)

You forgot to add values keyword

UPDATE Full example:

$insertQuery[] = '(?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE currAmount=values(currAmount)';

edited Oct 05 '17 at 03:24

answered Oct 03 '17 at 11:54

Alex Kapustin

1,869
12
15

"Syntax error or access violation: 1064 You have an error in your SQL syntax; [...] near 'ON DUPLICATE KEY UPDATE VALUE(currAmount)" :( – Lennart Bergmann Oct 03 '17 at 12:05
I did not want to write whole query, so I replaced unchanged SQL by ... – Alex Kapustin Oct 05 '17 at 03:23

score -1 · Answer 2 · answered Oct 03 '17 at 12:09

DO something like this

foreach ($articles as $article) {
  $insertQuery = '(?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE currAmount = (currAmount)';
  $insertData = $article->id;
  $insertData = $article->name;
  $insertData = $article->picture;
  $insertData = $article->url;
  $insertData = $article->amount;
  $insertData = $article->amount;
  $insertData = $date['year'] . '-' . $date['mon'] . '-' . $date['mday'];

  if(!empty($insertQuery)) {          
    $query .= implode(', ', $insertQuery);
    $handle = $this->link->prepare($query);
    $handle->execute($insertData);
  }
}

ON DUPLICATE KEY UPDATE in prepared statement with array

2 Answers2