5

I try to build new module in drupal 7 I try to insert some data in some where inside the code it was working in many functions but suddenly when I try to do the following

  db_insert('payment_request')
    ->fields(array(
      'payment_item' => $data['payment_item'],
      'change' => $data['change'],
      'via' => $data['via'],
      'userlist' => serialize($userlisthidden),
      'startdate' => strtotime($data['startdate']),
      'enddate' => strtotime($data['enddate']),  
      'hash' => $hash   
    ))->execute();

it gives me an error

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 'change, via, userlist, startdate, enddate, hash) VALUES ('2', '1', '1', 'a:1:{i:' at line 1

I checked the table and the fields and the values every thing are correct

so I decide to insert static data like the following :

  db_insert('payment_request')
    ->fields(array(
      'payment_item' => 'qq',
      'change' => 'qq',
      'via' => 'qq',
      'userlist' => 'qq',
      'startdate' => 'qq',
      'enddate' => 'qq',  
      'hash' => 'qq'   
    ))->execute();

But it gives me the same error so, what i miss here, please help me

9

CHANGE is a reserved word in MySQL. PDO doesn't have a built-in function for quoting identifiers (just string literals and dates), so presumably Drupal simply hasn't tried to implement this on PDO's behalf.

Using a different column name would be the simplest solution.

3

Reserved Word are permitted as identifiers if you quote them.

For example:

instead of using keyword interval like this:

CREATE TABLE interval (begin INT, end INT);

You could try

CREATE TABLE `interval` (begin INT, end INT);
  • 2
    That's certainly true, but the docs do explicitly state only to use db_insert() for insert queries (not db_query()). So yes, it can be done, but you might find it breaks portability to another DBMS. Obviously this may or may not be a problem depending on requirements, but it might not be suitable for, e.g., a contrib module – Clive Dec 29 '14 at 13:08

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.