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

Question

I have a friend who wants to set up a site for an NPO that basically allows users to search and edit a data table with about 100 fields. My solution (being from the MS world) would be to set up a simple MVC.Net site which pretty much auto-generates pages and forms for searching and editing rows. The technology the NPO has set up however, is Drupal 7 running on Apache with a MySQL instance floating around in there.

I've searched Drupal modules and poked around with things like Data which seemed fit for constructing a UI that searches and modifies DB tables, but didn't see any way to make it work. This seems like it would be a pretty common problem people want to solve though. Can anyone propose some appropriate tools/modules for setting up such a site?

---

That's my question, but if you are looking for some additional information about what they're trying to accomplish, here's some relevant details:

• The data we want to kick off the site with is about 300 rows in an excel spreadsheet. Something supporting a bulk data import would be preferable, but isn't required. I've already converted the data into an SQL db, so if we can simply point a module to the database, that would be great too.

• Most of these are plain text fields, some are numeric, some are paragraph format (with line breaks). Rather than just text boxes for everything though, while editing and searching on certain fields, they want to present the user with a drop-down list of available values.

• On the same vein as having drop-down lists for certain fields, forms that supports some customization (such as custom data validation routines) would probably be handy.

• I have a grand total of about 10 hours of experience working with Drupal, but I'm comfortable with installing and configuring modules, and have ssh access to the server.

Answer 1

Start off with something like this:

• Build Content Types for the data you want to represent. Remember that relationships in Drupal are different than the DB world and are joined with things like http://drupal.org/project/relation or http://drupal.org/project/entity_reference
• Use a module like http://drupal.org/project/migrate or http://drupal.org/project/feeds to import your data and map it into your nodes. Feeds will handle what you're doing here pretty handily but if you need more advanced manipulation on the way in, Migrate is the way to go. This can basically run on some kind of cron/batch to continue a "sync" of your data.
• Use http://drupal.org/project/views to build reports of your data

If you need to manipulate the data tables directly, that's not exactly what Drupal is built for. It's more than a Front End. It's a full CMF.

Answer 2

Well, yes.

Technically you can probably do this. If the database is on the same DB platform (Mysql, MariaDB, Postgres, etc) there are a few things you can do.

You can connect Drupal to more than one database at once.

• Read here: https://www.drupal.org/node/18429
• And here: https://drupal.stackexchange.com/a/29575/6598

You can visualize data in an external database/tables using views

• Views Database Connector (Great blog post on it)

So, that solves the read part. However, writing to that DB would require some custom PHP work to create a custom module that will generate a Drupal form and write the data to the DB. This is very possible, and requires a bit of PHP knowledge.

• Intro to Form API
• Form API reference
• Building Custom Forms

You will probably also need to expose this to a menu item and made available at a URL on your Drupal site, so you'll need to also know about menu routing, so this is a really nice holistic guide to that.