2

The Error:

 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 'key, value) VALUES ('1', '1', 'mydata', '35')' at line 1: INSERT INTO {mytable} (uid, tid, key, value) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3); Array ( [:db_insert_placeholder_0] => 1 [:db_insert_placeholder_1] => 1 [:db_insert_placeholder_2] => mydata [:db_insert_placeholder_3] => 35 ) in MyClass->save_myData() (line 145 of /path/to/my/stuff/my.class.php).

My Code:

db_merge( 'mytable' )
    ->key( array( 'uid' => $this->user->uid, 'tid' => $data['tid'] ) )
    ->insertfields(array(
           'uid' => $this->user->uid,
            'tid' => $data['tid'],
            'key'      => $key,
            'value'    => $value
    ) )
     ->updateFields( array( 'value' => $value ) )
     ->execute();

I've tried this with just the basic ->fields() but my latest iteration here involves specifiying the inserted fields and updated fields separately (just ran across this while googling and thought it might help. Same error though.

Originally I was trying to use

db_query( "REPLACE INTO goal_data(uid,tid,key,value) VALUES( :uid,:tid,:key,:value)", array(
                ':uid'      => $this->user->uid,
                ':tid'      => $data['tid'],
                ':key'      => $key,
                ':value'    => $value
            ) );

It too was throwing an error saying that there were in invalid number of placeholders or something similar. There wasn't. The error was basically:

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

Any thoughts on what I'm doing wrong? I don't get it, trying everything here and just keep getting errors.

Here's my table structure (very simple).

CREATE TABLE IF NOT EXISTS `mytable` (
  `uid` int(11) NOT NULL,
  `tid` int(11) NOT NULL,
  `key` varchar(255) NOT NULL,
  `value` longtext NOT NULL,
  PRIMARY KEY (`uid`,`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  • I believe you use ->fields() and updateFields(), never insertfields(). Scratch that, I see it's in there, but I think you have a typo: insertfields should be insertFields – Chris Rockwell Aug 9 '13 at 17:00
  • Fixed the spelling error, but it's still not working. I'll keep checking back here but about to give up. I think I'll remove this entity table and store all of this data as user profile fields (it's data about a user anyway) using user_save instead. – dpgtfc Aug 9 '13 at 18:50
  • If you take the ->execute() call off the query and cast to string you should be able to see the SQL string generated - could you post that in your question too? That would probably help narrow it down – Clive Aug 10 '13 at 0:16
0

The syntax error comes from key being a MySQL reserved keyword.
The best solution is to not use these keywords as identifiers.
Otherwise the workaround is to enclose them in backticks.

It may work like this:

  [...]
  ->insertFields(array(
           'uid' => $this->user->uid,
            'tid' => $data['tid'],
            '`key`'    => $key,
            'value'    => $value
    ) )
   [...]

However this fails at cross-database compatibility since the backtick is a MySQL-only syntax. Better rename the column if possible.

  • Thanks Daniel. I ended up going with storing this as profile data, but this helps for an upcoming data schema that looks like an entity would be best. I'll be sure to name the column though, can't believe I didn't see that! Thanks again. – dpgtfc Aug 20 '13 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.