I am working with Drupal 7.54.
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 :
Related posts elsewhere :
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.