How to publish data residing in an external databa

Question

I'm very new to Drupal and was getting a bit of mixed information about views so I wanted to see if anyone can set me on the right path.

Here's my situation. I have a separate database (on the same Drupal database server), that has a lot of data that's maintained by another team. We want to publish that data to the web and after getting some suggestions it looks like Drupal views is the way to go.

The problem is, I'm not sure where to go from there. I read the documentation and just watched the views tutorial on Lullabot, but my data isn't in the fields columns at all.

I read one document that said I had to "describe" my external database to Drupal but wasn't sure how to do it. I found a few tools on drupal.org (services, etc..) that said they can analyize my database and report the structure back to Drupal but I can't figure it out.

Basically the end result I want is two things. First is to simply display the data as is from my external database onto a Drupal node (the only catch is, each page is specific to a database entity so although the data format/display is standard, the data itself will change with the links, for exmaple, www.example.com/ibm should only provide our data on ibm and as the company name changes so does the data that's displayed) and later I might want to run some calculations on the data before it shows up.

Anyone have any suggestions of how to do this or any tutorials?

If it helps, I'm using Drupal 7.

On a side note, if it's not possible to do this in Drupal or even if it is, would it be better to create this in PHP and then somehow wrap it in Drupal? I'm debating this because I think I know how to do this in PHP and it might give me more control on the result (but i'm being a bit lazy because I'm thinking maybe Drupal can do it for me :-)

Update: Okay, now I'm even more confused. I have looked into views and still can't get it to integrate with my database. I have looked at schemas, and feeds as a way of either describing the database or getting it to replicate with Drupal. I'm not sure if I'm going the right direction (or what the right direction is) because so far everything I am trying seems to be a dead end. Can someone suggest a tutorial? Or relate their experiences getting this done? I simply want to display content from another database onto a page (when users select a specific stock ticker), I may want to allow users to customize it later(which is why I think views are needed).

Answer 1

Module Forena can be used to query (not update) all sorts of databases, such as a MySQL database, and to establish database connections to the external databases. And it comes with various hooks to use Forena from within another module.

For more details about Forena, 2 types of documentation are available:

• Community documentation.

• Documentation that comes with Forena, which you can access right after install and enable of the module. Checkout the demo site for an online example of the current:

  • Forena documentation - use the link 'Reporting documentation' or visit relative link /reports/help.
  • Forena samples - use the link 'Reporting samples' or visit relative link /reports/samples (these samples are fully functional, so make sure to experiment a bit with it, such as the drill downs available on the SVG Graph sample).

The newest 7.x-4.x version also includes an amazing (I think) UI for either creating your reports (the WYSIWYG report editor) and/or for creating your SQL queries (the Query Builder).

Here are some variations of the mysql database part:

1. Using a SQLite instead of MySQL database: the Forena samples actually get shipped including a (Tiny) SQLite database. Go check it out in the demo site: the data shown there are data contained in the sampledb, which is in SQLite format.
2. Forena comes with a full suite of Supported database connections ... such as MS SQL, Oracle, Postgress or any PDO compliant variation.

Enough reasons for considering giving Forena a try? While doing so, use it's issue queue for any type of support/docu requests you may have.

Be aware: I'm a co-maintainer of Forena.

Answer 2

As far as connecting to the database, in settings.php you can add additional database connections and use the db_set_active('connection_name') function in code to switch your active db connection to that source. Building plugins for views - that's a big subject far beyond the scope of a single answer. All I can say is Google it and good luck, and come back with specifics :)