db_update without table prefix on custom table

Question

I installed Drupal with table prefixes in the database. I have my own tables in the same database which I am trying to update. I am getting an error because Drupal is automatically adding the prefix "drupal_" to the table I specified.

$key_cached_update = db_update('my_table')
  ->fields(array('key_cached' => $apikeyinfo->result->key['expires'],))
  ->condition('keyid', $row->keyid, '>=')
  ->execute();

Base table or view not found: 1146 Table 'drupal_my_table' doesn't exist

I don't have problems with db_query to read the database.

Answer 1

In your settings.php file where you have prefix set in the $databases array. Change this

'prefix' => 'drupal_',

to this

'prefix' => array(
  'default' => 'drupal_',
  'my_table' => '',
),

If you want a more wildcard like way to do this you can set the target. So lets say this is what your $databases array looks like inside settings.php

$databases = array (
  'default' => array (
    'default' => array (
      'database' => 'd7',
      'username' => 'root',
      'password' => '',
      'host' => NULL,
      'port' => '',
      'driver' => 'mysql',
      'prefix' => 'drupal_',
    ),
  ),
);

Change it to this, where everything is the same except the prefix is empty.

$databases = array (
  'default' => array (
    'default' => array (
      'database' => 'd7',
      'username' => 'root',
      'password' => '',
      'host' => NULL,
      'port' => '',
      'driver' => 'mysql',
      'prefix' => 'drupal_',
    ),
    'noprefix' => array (
      'database' => 'd7',
      'username' => 'root',
      'password' => '',
      'host' => NULL,
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

Then in your function call do this

$key_cached_update = db_update('my_table', array('target' => 'noprefix'))
  ->fields(array('key_cached' => $apikeyinfo->result->key['expires'],))
  ->condition('keyid', $row->keyid, '>=')
  ->execute();