5

I have a relatively simple query that is executed via db_query()->execute; For some reason this causes a PDO Error, which I assume has to do with the way drupal handles the input. Any ideas on what causes this error and how to avoid it?

db_query('
    INSERT INTO {account_floodprotect} (ipaddress, soapcount, mailcount)
    VALUES (:ipaddress, :soapcount, :mailcount)
    ON DUPLICATE KEY UPDATE
    soapcount = soapcount + :soapcount,
    mailcount = mailcount + :mailcount',
  array(
    ':ipaddress' => $ip,
    ':soapcount' => $soapcount,
    ':mailcount' => $mailcount,
  ))->execute();

/*

ERROR CAUSED:

PDOException: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound

The table involved:

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| ipaddress  | varchar(255)     | NO   | UNI |         |                |
| soapcount  | int(10) unsigned | YES  |     | 0       |                |
| mailcount  | int(10) unsigned | YES  |     | 0       |                |
| updatetime | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
11

Don't need execute()

db_query('
    INSERT INTO {account_floodprotect} (ipaddress, soapcount, mailcount)
    VALUES (:ipaddress, :soapcount, :mailcount)
    ON DUPLICATE KEY UPDATE
    soapcount = soapcount + :soapcount,
    mailcount = mailcount + :mailcount',
  array(
    ':ipaddress' => $ip,
    ':soapcount' => $soapcount,
    ':mailcount' => $mailcount,
  ));
3

I think it'll be the parameters after ON DUPLICATE KEY UPDATE that are causing the problem; db_query() is fairly picky about which parts of the query string it will or won't do parameter replacements for.

The simplest way around it is to use the db_merge() function. Your query would look something like this:

db_merge('account_floodprotect')
  ->key('id')
  ->fields(array(
    'ipaddress' => $ipaddress,
    'soapcount' => $soapcount,
    'mailcount' => $mailcount
  ))
  ->expression('soapcount', 'soapcount + :soapcount', array(':soapcount' => $soapcount))
  ->expression('mailcount', 'mailcount + :mailcount', array(':mailcount' => $mailcount))
  ->execute();
  • 1
    The error is probably of the execute() there, as suggested in the other answer but db_merge() is exactly what should be used here, yes. – Berdir Aug 15 '12 at 6:10

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.