By systemguy on

Hello,
I am trying to return a paged database table from a query to an external database. I can return a table fine without paging but my paging code gives me the following error:
"user warning: Table 'db30832_stats.SymbolHeaderTable' doesn't exist query: SELECT COUNT(*) FROM SymbolHeaderTable in .... html/includes/common.inc(1695) "

Any ideas on fixing the problem? I presume it has something to do with the pager's COUNT SQL trying to access an external database. My code is below (connection code omitted): 


// Create table header
$head = array(
  array('data' => t('Symbol'), 'field' => 'symbol', 'sort' => 'asc'),
  array('data' => t('SymbolDescription'), 'field' => 'symboldescription'),
  );

// Connect to database and query data
mysql_select_db($database_test_conn, $test_conn);
$query = "SELECT * FROM SymbolHeaderTable " . tablesort_sql($head);
$count_query = "Select Count(*) as Count1 From SymbolHeaderTable";
$limit = 10;
$res = pager_query($query, $limit, $element = 0, $count_query);

// Output data to table
while ($obj= mysql_fetch_object($res)) {
 $rows[] = array(
 array('data' => $obj->Symbol ),
 array('data' => $obj->SymbolName ),
 );
        }  
 
 //return theme_table($head, $rows);
$output = theme('table',$head,$data);
$output .= theme('pager', NULL, $limit, 0);
print theme('page', $output);

Any suggestions would be appreciated.
Thanks,
Mike

⋅ Categories: Drupal 6.x

Comments

systemguy’s picture

systemguy commented

Figured it out. Became obvious after I knew what to search the forums for. Needed to use db_set_active to switch between databases.

1) I changed my settings.php to include my 'default' as well as my additional db:
$db_url['default'] = 'mysql://drupal:drupal@localhost/drupal';
$db_url['mydb'] = 'mysql://user:pwd@localhost/anotherdb';

2) Added db_set_active code right before and after my query and everything else worked:

db_set_active('mydb');
$res = pager_query($query, $limit);
db_set_active('default');
wheck’s picture

wheck commented

Thanks!!! I sort of thought the solution had to be something like that but you just pointed it out perfectly :)