Include data from legacy database in Drupal 7

3 minutters læsning

I am porting a site to Drupal 7. I need to keep the legacy registration system, but wanted to include the list of course to register to in the new Drupal 7 solution. I wanted to keep the data in the old database, so I needed to use a different database for this than the default drupal-database. Turned out that was pretty easy just adding another database configuration to the settings.php file.

// besides the $databases['default']['default'] add the following
$databases['default']['vih'][] = array(
    'driver' => 'mysql',
    'database' => 'vih',
    'username' => 'root',
    'password' => 'xxx',
    'host' => 'localhost',

After figuring that out, I had a couple of options to create a view of the legacy data:

1) Creating a custom module

I started out creating a custom module. First I hooked into the menu to create a menu item. Getting clever asking a friend and reading hook_menu, I did the following.

 * Implementation of hook_menu().
function vih_course_short_menu() {
    $items = array(); 
    $items['kortekurser'] = array( 
        'title' => 'Korte kurser', 
        'page callback' => 'vih_course_short_view', 
        'access arguments' => array('access content'), 
        'type' => MENU_NORMAL_ITEM, 
        'menu_name' => 'main_menu' ); 
     return $items; 

Now you have a menu item which you can use. To get content on the page, we need to implement the vih_course_short_view-function. As is fairly simple, as I only need to have the list and not display the individual courses. See hook_menu if you need more info on how to pass variables into the callback.

Again, I was faced with at least two options on how to create the display for the data.

1) Use table_theme()

Asking in the Drupal forum, I was presented with this solution:

function vih_course_short_view() {
    drupal_set_title('Korte kurser');
    $courses = db_query("SELECT id, name 
                                   FROM {kortkursus} 
                                   WHERE dato_slut > :date", array(':date' => date('Y-m-d')), array('target' => 'vih'));
    $table = array();
    $table['header'] = array('Kursus', 'Uge', 'Pladser', '');
    $table['caption'] = 'Korte kurser';
    $table['attributes'] = array();
    $table['colgroups'] = array();
    $table['sticky'] = array();
    $table['empty'] = array();
    foreach ($courses as $course) {
        $table['rows'][] = array(l($course->name, 'kortekurser/' . $course->id));
    return theme_table($table);    

2) Use theme()

I learned about theme() reading: hook_theme and tpl.php files: registering them with drupal, and passing variables. When using theme() you need to use an extra function which tells where the template is and what variables are available for the template.

function vih_course_short_view() {
    drupal_set_title('Korte kurser');
    $courses = db_query("SELECT id, navn FROM {kortkursus} 
        WHERE dato_slut > :date", array(':date' => date('Y-m-d')), array('target' => 'vih'));
    return theme('vih_course_short_table', array('courses' => $courses));

 * Implementation of hook_theme().
function vih_course_short_theme() {
  return array(
    'vih_course_short_table' => array(
      'template' => 'templates/courses', // the hook will add .tpl.php by itself
      'arguments' => array(
        'courses' => null

In addition to this, you also need to create you courses.tpl.php where $courses are available.

Which to choose?

I kind of decided on table_theme(), which has less code and is really easy. The specific template solution could be the correct solution when the table is more complicated.

Exposing the table for views

After reading Scratching the Itch: MyWeather + Views 2, I got tried to expose the legacy data table for views using the Views API. I had to use another database, so I used this code:

function vih_course_short_views_data() {
    $data['kortekurser']['table']['group'] = t('Korte kurser');
    $data['kortekurser']['table']['base'] = array(
        'field' => 'date',
        'title' => t('Korte kurser'),
        'help' => t('Korte kurser'),
        'database' => 'vih');
    $data['kortekurser']['navn'] = array(
        'title' => t('Name'),
        'help' => t('Name of the course'),
        'field' => array(
            'handler' => 'views_handler_field'),
            'argument' => array('handler' => 'views_handler_argument_string'),
            'filter' => array('handler' => 'views_handler_filter_string'),
            'sort' => array('handler' => 'views_handler_sort_string')
    return $data;

However, I cannot check whether I like the solution with views better, as there is a pending bug for views when using a secondary database.

What solution do you prefer?