CODEWITHSUNDEEP
phpmysqlsqlpdo
Tallboy
Tallboy

Reputation: 13427

Syntax error or access violation: 1064

I have a problem in my SQL it looks like. I have tried looking around through docs and can't seem to find my error.

Heres the error:

[11-Jun-2014 17:56:46] WARNING: [pool www] child 12509 said into stderr: "NOTICE: PHP message: PHP 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 'END WHERE id IN ()' at line 1' in /var/www/html/output.php:53"

I should add that the code works AS IS... it's only when I try to add the following $options that the errors appear:

  $options = array(
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
  );

and the code:

  # Also prepare SQL for update below
  $sql = "UPDATE `headlines` SET views = CASE id ";
  foreach ($headlines as $headline) {
    $id = $headline['id'];
    $sql .= sprintf("WHEN %d THEN views + 1 ", $id);
  } 
  # Update views
  $placeholders = rtrim(str_repeat('?, ', count($ids)), ', ');
  $sql .= "END WHERE id IN ($placeholders)";
  $q = $db->prepare($sql);
  $q->execute($ids);

Generated query:

UPDATE `headlines` SET views = CASE id WHEN 1364 THEN views + 1 WHEN 1365 THEN views + 1 WHEN 1366 THEN views + 1 END WHERE id IN (?, ?, ?)

Its also possible it could be empty, like such:

UPDATE `headlines` SET views = CASE id END WHERE id IN ()

Upvotes: 0

Views: 1409

Answers (1)

Mike Brant
Mike Brant

Reputation: 71384

You are asking PDO to send an exception when an error is encountered and you are doing nothing to catch that exception. That is the reason for the fatal error, and why you seem to see different behavior when changing PDO configuration (you just might not have been noticing the error before if you weren't logging it or looking in your logs).

Now the reason for the SQL error is given in the exception message. Your problem starts with the usage of END in your query. CASE id END is not valid syntax.

It is unclear to me in the generated SQL why you are using a case statement at all. As suggested above in comments, the WHERE clause you have specified would only execute the update on the specific id's provided, so the CASE statement is superfluous, not too mention it goes against the approach of parametrizing your input.

This should be all you need:

UPDATE headlines SET views = views + 1 WHERE id IN (?,?,?)

Upvotes: 2

Related Questions