0

I'm trying to do MySQL query with update on duplicate key but I can't see whats wrong with syntax because getting "syntax error or access violation" error.

code:

$sql = "INSERT INTO site_configuration 
          (configuration_key, configuration_value, additional_data) 
        VALUES 
          (:config_key, :site_new_background, :site_background_meta)
        ON DUPLICATE KEY UPDATE
          'configuration_value' = VALUES(:site_new_background), 'additional_data' = VALUES(:site_background_meta)";
$smth = $database->prepare($sql);
$smth->bindParam(':config_key', $config_key, PDO::PARAM_STR);
$smth->bindParam(':site_new_background', $site_new_background, PDO::PARAM_STR);
$smth->bindParam(':site_background_meta', $site_background_meta, PDO::PARAM_STR);

Error message:

'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 ''configuration_value' = VALUES(?), 'additional_data' = VALUES(?)'

Diamonte
  • 393
  • 7
  • 22

1 Answers1

1

remove the single quote from the statement 

$sql = "INSERT INTO site_configuration 
              (configuration_key, configuration_value, additional_data) 
            VALUES 
              (:config_key, :site_new_background, :site_background_meta)
            ON DUPLICATE KEY UPDATE
              configuration_value = VALUES(:site_new_background), additional_data = VALUES(:site_background_meta)";
Nishant Nair
  • 1,999
  • 1
  • 13
  • 18
  • This works. I had to remove single quotes AND can't reuse placeholders as Jon Stirling commented to my question, so I fixed it with using question marks and putting array to execute. Also I noticed I had placeholders ON DUPLICATE KEY UPDATE where they should be column names like: `VALUES(configuration_value), addiotional_data = VALUES(additional_data)` – Diamonte Apr 25 '17 at 10:09