Robin Camille Davis
  1. Home /
  2. Blog /
  3. Building a database directory in Drupal 7

Building a database directory in Drupal 7

March 18, 2013
Tags: drupal, website

We use Drupal 7 as the CMS behind our library website. It's robust and flexible, but has a notoriously steep learning curve on the back end. One thing we struggled with at first was how best to direct our users to the databases they have access to. (At this time, CUNY doesn't have a discovery layer, so students must find articles by choosing a database first and searching within.)

Making a good directory for our 200+ databases required getting familiar with Content Types (specifying categories of content, like 'database' or 'blog post') and Views (how these fields are presented, like displaying a URL as a link). When you make a new content type, behind the scenes, Drupal adds more tables to its core database, where every field you pick is a column and every piece of content you add is a row.

The full page of our database directory looks like this:

database screenshot

Screenshot of database directory (click for full-size image or see for yourself)

Each database we subscribe to has its own row of information and links, like this:

Info about one database

Info about one database

That information is entered according to the fields we specified in our 'database' Content Type:

'Database' content type fields (click for larger)

'Database' content type fields (click for larger)

And here's a screenshot of the View that dictates how the directory displays:

View of 'A-Z databases' (click for larger)

View of 'A-Z databases' (click for larger). Note: the last 2 filter criteria are there because we list Subject Guides and federated searches as being 'databases' so they show up on single-subject pages (example: see asterisks at top), but we don't want them to show up on the main directory, so we exclude them by filtering out the URL.

As you can see, making a View requires specifying which fields you want displayed. Although the 'database' Content Type includes information about related subjects, we don't want to display that on this table; we only chose what was necessary for this directory.

The 'Table' format is set by default to give each field its own column, but we only wanted 3: Title, information (Body), and the Tech Fee column. Clicking "Format: Table / Settings" brings up these configurations:

View - table settings (click for larger)

View: table settings (click for larger)

The above screenshots don't tell you how exactly the fields are displayed. The yes-or-no "Purchased with Student Technology Fee" field, for instance, displays as an image if yes. The Mobile access URL is rewritten as...
<div><a href="[field_mobile_url]">Mobile site</a></div>
...so that the plain URL is a reworded link and shows up on its own line. (Usually, you can specify within the Content Type that a field is a URL and set static text from the get-go, but we've had trouble doing this with proxy URLs.)

When constructing a View, it helps to know how relational databases work. Still, while it may be a headache to make a bunch of different views, to me it's easier than writing out the SQL query...

SELECT node.nid AS nid, 'node' AS field_data_field_display_title_node_entity_type, 'node' AS field_data_field_plain_url_node_entity_type, 'node' AS field_data_body_node_entity_type, 'node' AS field_data_field_cuny_link_node_entity_type, 'node' AS field_data_field_mobile_url_node_entity_type, 'node' AS field_data_field_tech_fee_node_entity_type, 'node' AS field_data_field_access_node_entity_type
FROM
{node} node
LEFT JOIN {field_data_field_plain_url} field_data_field_plain_url ON node.nid = field_data_field_plain_url.entity_id AND (field_data_field_plain_url.entity_type = 'node' AND field_data_field_plain_url.deleted = '0')
LEFT JOIN {field_data_field_display_title} field_data_field_display_title ON node.nid = field_data_field_display_title.entity_id AND (field_data_field_display_title.entity_type = 'node' AND field_data_field_display_title.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN ('databases')) AND (field_data_field_plain_url.field_plain_url_value NOT LIKE 'http://guides.lib.jjay.cuny.edu/%' ESCAPE '\\') AND (field_data_field_plain_url.field_plain_url_value NOT LIKE 'http://ez.lib.jjay.cuny.edu/login?url=http://mh9fe2ft4z.cs.serialssolutions.com/%' ESCAPE '\\') ))
ORDER BY field_data_field_display_title.field_display_title_value ASC

Yikes.

Many thanks to our webmaster, Mandy, for configuring the majority of our database list. If there's interest, I'm also happy to share how we configured the single-subject View (example: International Affairs) or the single-letter view (example: begins with B).