How do I edit an external database from the UI?

Question

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.

Answer 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.