How to retrieve data directly from a Drupal database


Assume you have some table in your Drupal database, and you want to fetch some rows from it (directly from the database) to show them on a page.

How can this be done?



1 answer


This is a perfect usecase for the Forena module, which comes with quite some community documentation.

Forena is built of the idea of using SQL to get data out of a database (which can be the Drupal database or an external one such as My SQL, Oracle, MS SQL, ...) and use XHTML and CSS to format it into web reports. There are quite some reasons for considering Forena (even if it was only because of how "Forena complements the Views module"). Visit the Forena HowTos page for some links (near the bottom) to some live demo / showcase links.

You can use the typical report writing features of Forena to build a report, and then use the most appropriate site building feature(s) to actually visualize these reports. The Forena documentation that gets installed with the module also includes a chapter about these site building features, as shown also in the demo site (via the various links near the bottom of that link).

Here is an example to get you started:

  • create a database connection (admin task, one time only for each database to connect to, with permissions, etc). In Forena this is called a "Data Source". DBMSs that are supported are the Drupal DB itself (of course), but also MySQL, Oracle, MS SQL, Postgress, Sqite, anything PDO compliant.

  • a custom query, to be created by somebody with permission to create data blocks (typically only a subset of site users get that auth). In Forena this is called a "Data Block". It should look similar to this:

        SELECT id, slug, title, tags, body, image
        FROM body_field_revisions
        WHERE slug = ?
  • the report specifications (what in Forena is known as an FRX file, which is an XHTML file), would probably look similar to this:
       <title>My custom report title</title>
       <frx:category>Articles related Reports</frx:category>
       <frx:options skin="My_Skin_File_With_CSS_And_JS"/>
       <div frx:block="MyDatabaseConnection/MySqlQuery">
         <p frx:foreach="*">{title}<Br/>{tags}<Br/>{body}<Br/></p>

Creating such FRX file can be done either via its WYSIWYG reporting interface (e.g. for newbees). More experienced users prefer to have the full FRX power available. The more they learn about the FRX features, the more they edit the FRX files (which are just text files). The great thing about these FRX files is also, I think, that you can delegate the report development to regular users also (who do not have to know PHP, only HTML-kind of knowledge, or just use the WYSIWYG approach to even hide that for them). Think about managers being able to write their own reports ... instead of bothering you about it.

So ... No PHP involved, just SQL and XHTML, and some extra options like CSS and/or JSS if you want (that's what all the Report Skins is all about). And with optional support for dataTables also. Last but not least: it supports creating charts from it also, using an open source charting engine in PHP (= saving to PDFs works great + no internet connection required or possible firewall issues).