Why do I get this exception when running a query?

Question

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 'default) VALUES ('1', '0', 'LSm', '054', '0')' at line 1: INSERT INTO {prod_conf_procuct_parameter} (prod_id, price, key1, key2, default) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4); Array ( [:db_insert_placeholder_0] => 1 [:db_insert_placeholder_1] => 0 [:db_insert_placeholder_2] => LSm [:db_insert_placeholder_3] => 054 [:db_insert_placeholder_4] => 0 )

function _prod_conf_db_query_insert_results_pcpp($arg,$key1,$key2,$price,$default){
watchdog('alert', "Arg: $arg, Key1: $key1, Key2: $key2, Price: $price, Default: $default");
db_insert('prod_conf_procuct_parameter')
  ->fields(array(
    'prod_id'=> $arg,
    'price'=> $price,
    'key1' => $key1,
    'key2' => $key2,
    'default' => $default
    )
  )
  ->execute();
}

watchdog() gives Arg: 1, Key1: LGm, Key2: 054, Price: 0, Default: 0.

The problem is definitively the column "default" because the insert without default|$default works.

The column is defined in hook_schema() as follows.

'default' => array(
          'description' => 'boolean indicates if the new applicated select list has default',
          'type' => 'int',
          'size'=> 'tiny',
          'default' => 0
        ),

If I run the following query from PHPMyAdmin, it works.

INSERT INTO `prod_conf_procuct_parameter`(`prod_id`, `key1`, `key2`, `price`, `default`) VALUES (1,"LSm","054",0,1)

Answer 1

DEFAULT is a reserved MySQL keyword. Try:

db_insert('prod_conf_procuct_parameter')
  ->fields(array(
    'prod_id'=> $arg,
    'price'=> $price,
    'key1' => $key1,
    'key2' => $key2,
    '`default`' => $default
    )
  )
  ->execute();

phpmyadmin does a lot of hand-holding and error squashing for you - it's not advisable to use it as a canonical source of truth for a particular query string's validity.