PHP/MySQL Error " Syntax error or access violation:"

PHP
1

Hi all,

I have been throwing together something and I have come back to using PDO for the first time in a while (dont ask) and when I am running a prepared statement I am being thrown an error I have never seen before, a quick bit of Google-fu has come up with nothing useful for me so I was hoping that you wonderful bunch can help, below is the statement and full error:

$stmt = $db->prepare('INSERT INTO obs VALUES (:store, :tgt, :ob, now(), :q1, :q2, :q3, :q4, :q5, :q6, :q7, :q8, :q9, :q10,
                       :q11, :q12, :q13, :q14, :q15, :q16, :q17, :q18, :q19, :q20, :q21, :q22, :q23, :q24, :q25, :q26, :score)');

     $stmt->execute(array(
         ':ob' => $sub['observer'],
         ':tgt' => $sub['observed'],
         ':score' => $count,
         ':store' => $sub['store'],
         ':q1' => (isset($sub['welcome1']) ? 1 : 0),
         ':q2' => (isset($sub['welcome2']) ? 1 : 0),
         ':q3' => (isset($sub['welcome3']) ? 1 : 0),
         ':q4' => (isset($sub['welcome4']) ? 1 : 0),
         ':q5' => (isset($sub['welcome5']) ? 1 : 0),
         ':q6' => (isset($sub['hand1']) ? $sub['hand1'] : 0),
         ':q7' => (isset($sub['under1']) ? 1 : 0),
         ':q8' => (isset($sub['under2']) ? $sub['under2'] : 0),
         ':q9' => (isset($sub['under3']) ? 1 : 0),
         ':q10' => (isset($sub['under4']) ? 1 : 0),
         ':q11' => (isset($sub['under5']) ? 1 : 0),
         ':q12' => (isset($sub['under6']) ? 1 : 0),
         ':q13' => (isset($sub['under7']) ? 1 : 0),
         ':q14' => (isset($sub['guide1']) ? 1 : 0),
         ':q15' => (isset($sub['guide2']) ? 1 : 0),
         ':q16' => (isset($sub['guide3']) ? 1 : 0),
         ':q17' => (isset($sub['guide4']) ? 1 : 0),
         ':q18' => (isset($sub['guide5']) ? 1 : 0),
         ':q19' => (isset($sub['guide6']) ? 1 : 0),
         ':q20' => (isset($sub['guide7']) ? 1 : 0),
         ':q21' => (isset($sub['guide8']) ? 1 : 0),
         ':q22' => (isset($sub['easy1']) ? $sub['easy1'] : 0),
         ':q23' => (isset($sub['easy2']) ? 1 : 0),
         ':q24' => (isset($sub['fare1']) ? 1 : 0),
         ':q25' => (isset($sub['fare2']) ? 1 : 0),
         ':q26' => (isset($sub['fare3']) ? 1 : 0)
     ));

The error I receive is:

PDOException: 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 ‘)’ at line 2 in C:\wamp\www\obs\index.php on line 79

Line 79 is the closing parenthesis at the end of the snippet. The reason for all the ternary checks is that I am working with checkboxes for input and obviously if they have not been ticked then they do not appear so I need to make sure that there is at least a 0 value in there.

2

Why have you got a comma after the :score placeholder and before the close-bracket? Would it throw a syntax error for that?

ETA - probably not that, now I’ve read and re-read which line it’s throwing the error on.

3

That was a typo right now :stuck_out_tongue: it’s not in the actual code

4

My guess is it’s the parentheses in now() causing a problem.

Maybe try something like

$current_time = now(); 
......

:ob, :current_time, :q1, 
......
         ':current_time' => $current_time,
5

Ah, I see. The only trouble with re-typing the code for the forum, rather than copy/paste from your actual code, is that if the issue is a typo in your actual code, it might not appear on the forum post.

So would that cause it to throw the syntax error at the end of the execute(), rather than during the prepare()? I was trying to find out whether prepare() does a syntax check on the query or whether it leaves it until the execute() stage.

6

I don’t see any error handling it the example code.
I don’t know if I’m understanding this correctly
http://php.net/manual/en/pdo.prepare.php

Return Values

If the database server successfully prepares the statement, PDO::prepare() returns a PDOStatement object. If the database server cannot successfully prepare the statement, PDO::prepare() returns FALSE or emits PDOException (depending on error handling).

Note:
Emulated prepared statements does not communicate with the database server so PDO::prepare() does not check the statement.

It looks like the execute() is catching the exception.

7

It does.
But only when asked.

The behavior is quote logical:

  • when emulated prepares are used, then whole query is sent to DB with execute() call
  • when emulation is turned off, then the real prepare is used and in this case it will be happy to throw a syntax error.
2 Likes
8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.