2

I am working with Drupal 7.54.

Using the great module Views Database Connector, I managed to display data from an external database, previously configured in settings.php as mentioned by Drupal.org.

Now, using Views 3.15, I can create pages, blocks, etc to display data from this extra database and it's nice.

My client will be able to edit this external database from Access, Sequel Pro, Navicat, etc and the modifications will be automatically displayed on the website.

But now, I have a problem with the other way : modify data from Drupal UI and insert these new values in the external database.

I have tried to find modules or tutorials and here are the options I may have :

  • Using Feeds SQL: with this module, I can link the external database through import process. I should first create a content type to map fields with columns and then import data from the external database in Drupal ecosystem. Later, when I modify contents, I may be able to create an inverse process to export Drupal fields values into the external database columns (not quite sure it is possible). BUT if I modify something through Drupal UI and set an automatic import through cron, it will overwrite the external database, possibly modified before through another interface (Sequel Pro, etc) so I need a non-automatically way to edit the external database through Drupal UI. Maybe add an "Update database" button on the Drupal edit form? Hope I am clear here...

  • Creating a custom module : I am not a senior Drupal developer, more a site builder. I always try to use modules and configuration between them to reach my purpose which seems natural if you use a CMS (or CMF), imho. Anyway, if someone has an obvious solution with a handmade module, any advice is welcome, of course.

  • Other modules : Data, Schema, Forena... they seem to be very useful and powerful but they also seem to work only for display, am I right? You may adopt tables, create reports or custom SQL queries but in fine, you may not be able to edit external database with these tools?

Well, I am still lost in thoughts and I hope that this forum (which helps me each time) could give me a way to achieve this goal.

A last option would be to work without Drupal but I am not ready for that!

Thanks in advance.

Related questions on this website :

Insert into table from Drupal 7

Inserting data to external DataBase

Manipulate Database from Drupal

Drupal as an interface for users to Add/Edit/Search a database

Related posts elsewhere :

Module to sync with external database

Save data to external (non drupal) database

Working with an external database

EDIT 1 : After different tests, maybe I will not need Views Database Connector at all since Feeds SQL does a tremendous job with SQL data from an external database. With an automatic cron job and a nice mapping, you can convert your SQL data into Drupal nodes and thus using Views, Panels, Drupal Commerce, Taxonomy, etc. If I update the external database (with any software you like, from PHPMyadmin to Access), next time the cron runs, Feeds SQL will update the related nodes on the website and no one needs to edit the fields values through Drupal interface. Awesome. This part is OK.

But I still need the reverse way : synchronize the Drupal fields values with the external database lines... A sort of export... Views Data Export? but the resulting file (csv, xml...) will always need manual process to overwrite the external database... To be continued...

EDIT 2 : I have tried to work with Data and Schema. First, I exported a table from the external database and imported it in the Drupal database among all the usual tables. Then I adopted the orphan table and create an entity with this table (thanks to Schema module). Thus I managed to create a View listing the table contents. Finally, I installed Views Bulk Operations and added an action button "Modify entity values". Now, I have a view displaying values from an extra-Drupal table with a possibility to edit the values. But I must admit that it is not user-friendly compared to a Drupal usual content form (custom with panels or display suite). So, any new idea Drupal friends?

EDIT 3 : I guess a custom module should be the answer. So I started something based on this question. It works if I need to create a new line in the external database. Now I would need some advice to write this module so I can edit an existing row? To clarify that part, I posted another question: How to send values to an external database with a custom module?. Thanks in advance.

  • Do you mean you want mysqladmin alike interface on Drupal? – Jimmy Ko Jul 5 '16 at 11:22
  • Yes, for example, and according to my last readings, I guess Data and Schema could help me if I "inject" the external database tables in the Drupal database and adopt them. But maybe you have another idea like your question suggests? Ideally, it would be great to enter new values in the database tables through Drupal forms or maybe set an active database related to a specific content type, something like this. Or even link a Drupal field with a table column! Anyway thanks for any tip you have. – Toki Jul 5 '16 at 14:39
  • I ask this because you mentioned your client is able to edit the external database by Access, Sequel Pro ... Before answering your question, it is better to know what kind of UI your client can use or request for. – Jimmy Ko Jul 5 '16 at 15:44
  • Here is the problematic : my client requests 2 different ways to edit the database content. One with standard software like Access (he is working on that database since many years) and another one through simple web interface for other users less comfortable with database softwares (only forms and tables). Finally, data from this external database will be displayed on a second Drupal website (I will use Feeds SQL to "convert" these raw data into nodes) and no edition will be needed on this one (with search engines, maps, e-commerce, etc.). I hope I am clear enough. – Toki Jul 5 '16 at 16:57
1

Finally, I manage to edit an external database from Drupal interface following these steps (just a simple example) :

  • Modify the settings.php file to declare my external_database with one table datatest(primary key : id)
  • Create a Drupal content type External Data with fields (field_integer, field_varchar, field_long_text, field_date) related to the datatest columns (column_int, column_varchar, column_longtext, column_date)
  • Create a Feeds importer using Feeds and Feeds SQL to map Drupal fields with datatest columns
  • Import datatest values in Drupal nodes with my Feeds importer
  • Using Feeds Job Scheduler, now any modification in datatest table will be transfered to my Drupal nodes, each cron.
  • Writing a rule (Rules module) to update datatest values each time a Drupal node is edited

    • Event : After updating existing content of type External Data
    • Condition : none
    • Actions :
      • Execute custom PHP code

    db_set_active('external_database'); db_query("UPDATE datatest SET column_int='[node:field-integer]', column_varchar='[node:field-varchar]', column_longtext='[node:field-long-text]', column_date='[node:field-date]' WHERE id=[node:title]"); db_set_active();

    • Show a message on the site (to confirm the rule has fired)
  • Using this last item, now each time I updated a node through External Data Drupal form, values are also modified in datatest.

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.