Incorporating data from an external database into Craft pages

Question

I'm looking at how best to incorporate various sets of data from another MySQL database into a Craft site. Using the Feed Me plugin would be one way, if the data source had feeds, but I'm thinking about alternatives. Does this make sense:

1. There is an Entry whose page should display data from the external DB.
2. The admin user can, using standard Fields, select the criteria for which data it is (eg, category, number of items, timespan, etc).
3. In the Entry's template, a template hook calls a Craft plugin.
4. The plugin makes a request to the external DB, tidies the returned data, and renders it into the page with a twig template.

Does that make sense? Anything that sounds impossible or stupid? Can you think of a better solution?

It looks like I could cache that part of the template so that it didn't make requests to the external DB with every page request. Is that right?

(For context: I haven't used hooks yet, and have only written the most basic of Craft plugins. I have years of general PHP experience, but little with Craft or Yii.)

Answer 1

That solution makes sense! I have used a similar strategy. You should absolutely cache around the template hook, or cache the database calls in the plugin itself.

Take a look at the Yii Query Builder to see how Yii/Craft handles MySQL queries. You can also look at examples Craft source code.