1

I have a pretty detailed relational database that I would like Drupal to communicate with (Read Only). Now, I know that I can create custom modules to output the data but I am not sure I will be leveraging Drupal's benefits using in that fashion (it seems like a hack). I do not plan on moving the data to Drupal either (too large in size and also more than 80 tables).

Is there a way that I could use views to get the data out from my external database without interfering with the Drupal core? I am sure that Drupal developers run into this issue many times, I would like to have a pointer as far as where to look. Is that possible? has anyone ever done this?

I have already used Google in an attempt to find a solution. I even tried the entity module, entity api, data module a few other API and modules without success.

Just need a workflow/pointer or simply a successful website that is using Drupal in that fashion.

I am using Drupal 7 and I will be willing to go to Drupal 8.

1

Yes there is a way to use views to get data out of an external database. You will need the hooks_view_data()

There is however the limitation the view can not cross databases I believe.

To solve this, you can write a custom module

<?php

function yourmoduleame_views_views_api() {
   return array(
      'api' => 3,
   );
}


function  yourmodulename_views_views_data() {
   $data['arTable']['table']['group'] = t('yourtable Data');
   $data['arTable']['table']['base'] = array(
      'field' => 'id',
      'title' => t('your title'),
      'help' => t('help text'),
      'database' => 'databasename',
      'weight' => -10,
   );

Where databasename is one of your db_names defined in settings.php.

For more information, look at this post

  • What isn't outlined above, is building up all of the field entries in $data. This is pretty tedious. It helps if you make up a few helper functions for the various column types. I have also done this where I do a hook_views_data over MySQL views. Have a DBA who is familiar with the external database make up a few views that you need, and then wire them into Drupal. – mpdonadio Jan 10 '14 at 16:54
  • If the view needs to display data frome more than one mysql database, this is a workaround: I created a mysql view in the first database (the one I defined in settings.php), that includes the data I need from the second database. I then defined the view as the base table, and voila! More details at drupal.org/node/1417982 – Ursula Jan 10 '14 at 16:55
  • My database is a mysql database so there is no problem there. But the sheer amount of fields and table realationships worries me. It will take me a couple days non stop to add all theses fields in. @MPD that is a great idea, since i am technically the DBA as well, i will try to investigate that route. – winteck Jan 10 '14 at 17:12
  • FYI: link to "post" is broken ... Not sure how to repair ... Remove? – Pierre.Vriens Nov 3 '15 at 20:31
1

Maybe the Forena module is what you're looking for? It is built of the idea of using SQL to get data out of a database (which can be the Drupal database or an external one such as My SQL, Oracle, MS SQL, ...) and use XHTML and CSS to format it into web reports. There are quite some reasons for considering Forena. Visit the Forena HowTos page for some links (near the bottom) to some live demo / showcase links. Refer to its community documentation for a lot more details.

To answer this specific answer, I'd use the views integration of it. Here are some details about that integration, included in the Forena documentation that gets installed with the module also (and shown in the demo site):

Using the Forena integration with the Views module, it is possible to use Forena reports as a views row style, so that in a view you can use the Forena templating engine to do the formatting. Although this can be done through the views templating engine Forena users may find they have more control with this engine.

Disclosure: I'm a co-maintainer of this module,
I hope this does not violate the site's policy on self-promotion.

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.