0

The following code works well for displaying a paginated list of Drupal users.

function generate_list() {
  $query = db_select('users', 'u')
    ->extend('PagerDefault')
    ->fields('u', array('name', 'uid'))
    ->orderBy('created', 'DESC')
    ->limit(20);

  $result = $query->execute();

  $list = array();
  foreach ($result as $row) {
    $list[] = sprintf('<a href="/user.php?id=DE%u">%s</a>', $row->uid, $row->name);
  }

  $theme_args = array('items' => $list, 'type' => 'ul');
  return theme('item_list', $theme_args) . theme('pager');
}

I tried fetching from an external SQL-table using the following code.

function generate_list() {
  $query = db_select('pref_users', 'u')
    ->extend('PagerDefault')
    ->fields('u', array('first_name', 'id'))
    ->orderBy('created', 'DESC')
    ->limit(20);

  $result = $query->execute();

  $list = array();
  foreach ($result as $row) {
    $list[] = sprintf('<a href="/user.php?id=%s">%s</a>', $row->id, $row->first_name);
  }

  $theme_args = array('items' => $list, 'type' => 'ul');
  return theme('item_list', $theme_args) . theme('pager');
}

I get this error.

PDOException: SQLSTATE[42P01]: Undefined table: 7
ERROR: relation "drupal_pref_users" does not exist at character 67: SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {pref_users} u) subquery; Array() in method PagerDefault->execute() (line 75 in file includes/pager.inc).

Does anybody please know a way to tell Drupal to fetch data from pref_users and not from {pref_users} at it seems to do?

1
  • 1
    I suggest you simplify your question to basically something like "Can I use external tables with db_select()?". Right now, 90% of your question looks like a duplicate of your others, existing questions and it's not relevant for the problem at hand.
    – Berdir
    Mar 25, 2011 at 13:58

1 Answer 1

1

the squiggly brackets are used for escaping table names in drupal. For cases where you have multiple sites in one database.

I'm guessing that you have a db prefix set up for all of your Drupal tables to be drupal_

You can set this on a db connection before $result = $query->execute(); using the setprefix method

Something like

 $query->setprefix(array(
     'default'   => 'drupal_',
     'perf_users'     => ''));

untested code

may do it. However you are more likely to want to change your settings.php file and set table prefixs in there

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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