Possible to have drupal read from a sql database?

Question

I have a site under drupal (using mysql) but need it to read from a external sql database and make quires/reports, maybe using views2?

Is is at all possible? I been looking for a solution to just read (not import, its thousands of entries...) from an external database for hours and not sure if its even possible.

Answer 1

It's possible to define several database connections in your settings.php and use db_set_active to select which database to use.

If you want to do this with views, you might need to do some extra work and embed the view after setting the active database to the external one.

Answer 2

Drupal can access external databases, assuming that they are one of the types Drupal supports.

I will refer you to this handbook page: How to connect to multiple databases within Drupal.

However, to actually access data in the external database, you will need to write custom code, probably in a custom module. Essentially, you will need to do something like:

// Set Database API to use the other database.
db_set_active('external_db'); 

// Query the database.
db_query("SELECT * FROM {your_table} WHERE condition = 'value'");

// Set the Database API back to the default db.
db_set_active('default');

Essentially, point the database to the external database, make your reads and writes, and switch back. If you forget to switch back, Drupal will crash as it's core functions will try to work with the non-Drupal database.

Answer 3

Without writing code, you should be able to create views of the secondary database using the Table Wizard module. However, given how Drupal 6 handles its database abstraction, this will require that the secondary database be the same type as the main Drupal database (eg, either both are MySQL or both are Postgresql).