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');
}

But when I try fetching from an external SQL-table called pref_users (my site embeds a card game and some users come from outside - through Facebook and other social networks, so I have to maintain some non-Drupal SQL-tables, in the same database where Drupal data is stored) with this 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');
}

then I unfortunately get the 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 simply from pref_users and not from {pref_users} at it seems to assume above?

  • 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 '11 at 13:58
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

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.