PDO on duplicate update error Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of PDO on duplicate update error without wasting too much if your time.

The question is published on by Tutorial Guruji team.

I’ve checked multiple PDO posts, and they all say the syntax is incorrect, but even when checking I can’t seem to find it. Here is my code:

 $stmt = $pDatabase->prepare('INSERT INTO Agenda (index, date, shortdesc) VALUES :values ON DUPLICATE KEY UPDATE date=VALUES(date), shortdesc=VALUES(shortdesc)');

I’ve tried fixing it with a ; at the end, or inserting one at a time. It errors on the prepare, so whatever :values is shouldnt even matter.

This is the error produced:

Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[42000]: Syntax error or access violation: 1064 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 ‘index, date, shortdesc) VALUES(?, ?, ?)ON DUPLICATE KEY UPDATE date=VALUES(date)’ at line 1′ in /customers/f/b/e/**************/httpd.www/editagenda.php:14 Stack trace: #0 /customers/f/b/e/**************/httpd.www/editagenda.php(14): PDO->prepare(‘INSERT INTO Age…’) #1 {main} thrown in /customers/f/b/e/**************/httpd.www/editagenda.php on line 14

where 14 is the prepare line.

This line works fine in DBadmin.

My table looks like this:

index        date          shortdesc        longdesc           boolean
10           2015-12-12    Something        copyshort          1
11           2015-11-12    Somethi2ng       copyshort2         0

Answer

You can’t use one placeholder for multiple values like that.

From the manual:

Note: Parameter markers can represent a complete data literal only. Neither part of literal, nor keyword, nor identifier, nor whatever arbitrary query part can be bound using parameters. For example, you cannot bind multiple values to a single parameter in the IN() clause of an SQL statement.

Try this instead:

$stmt = $pDatabase->prepare('INSERT INTO Agenda (index, date, shortdesc) VALUES (:index, :date, :shortdesc) ON DUPLICATE KEY UPDATE date=VALUES(date), shortdesc=VALUES(shortdesc)');

(Note that you need the ( and ).) Then pass three values, one for each placeholder (:index, :date, and :shortdesc).

P.S. Note that index and date are reserved words in MySQL (and most RDBMSes). You need to wrap them in backpacks, as in:

$stmt = $pDatabase->prepare('INSERT INTO Agenda (`index`, `date`, shortdesc) VALUES (:index, :date, :shortdesc) ON DUPLICATE KEY UPDATE date=VALUES(`date`), shortdesc=VALUES(shortdesc)');
We are here to answer your question about PDO on duplicate update error - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji