CODEWITHSUNDEEP
phpmysql
Will Case
Will Case

Reputation: 105

Invalid PDO MySQL Statement - Error in Syntax

So, I have the following PDO statement, which is one of several similarly constructed statements on the page. The rest are working fine, so I am confused as to why this one is stating invalid syntax.

$test_id = '1';
$option = 'a';
$ab_email = '[email protected]';

$stmt = $pdo_db->prepare("INSERT INTO `ab_tests_visitors` (test_id,option,visits,email) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE email=?");
$stmt->execute(array($test_id,$option,1,$ab_email,$ab_email));

The schema for the database has 5 columns, 2 of which are indexes.

The error being given is:

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 MySQL server version for the right syntax to use near 'option,visits,email) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE email=?' at line 1'

Upvotes: 0

Views: 127

Answers (2)

Carbos
Carbos

Reputation: 117

For this problem, in name of your column, you must use backticks ` around the column name (alt + 96)

Upvotes: 1

Sirko
Sirko

Reputation: 74106

option is a MySQL keyword. If you want to use it as an identifier, make sure to always surround it with backticks (as you should do with identifiers anyway):

$stmt = $pdo_db->prepare("INSERT INTO `ab_tests_visitors` (`test_id`,`option`,`visits`,`email`) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE `email`=?");

Upvotes: 1

Related Questions