A Drupal 7 table with sortable headers and pager

In my previous blog post I wrote about how to create an HTML table in a Drupal 7 module form. As promised in that tutorial, once I figured out how to make the table columns sortable, I'd share that secret recipe as well. Here then is how to make an HTML table in a Drupal 7 module/form:

• Sortable by clicking on column headers.
• Include a pager so you can scroll through the result set.
• Click a link on a field to take you to the form 'edit' page.

Using the source code below, my sortable Drupal 7 table looks like this:

As you can see from the Name column in that image, you can click the column headers to sort the table, and the table pager is displayed beneath the table.

Drupal 7 sortable table with pager - Source code

Without any further ado, here's the source code for my sortable Drupal 7 table that also includes a pager:

<?php

require_once 'common.inc';

/**
 * The "List Projects" table
 * ----------------------------------------------------------------------
 * 
 * got helpful drupal 7 table sort info from http://nanwich.com/node/335
 * 
 */
function projects_list() {

  # configure the table header columns
  $header = array(
    array('data' => 'Name',        'field' => 'name',               'sort' => 'ASC'),
    array('data' => 'Type',        'field' => 'project_count_type', 'sort' => 'ASC'),
    array('data' => 'Updated',     'field' => 'last_updated',       'sort' => 'ASC'),
    array('data' => 'Description', 'field' => 'description'),
  );

  # set the database table and initial SelectQuery options
  # $select is a SelectQuery object.
  # @see http://api.drupal.org/api/drupal/includes--database--select.inc/class/SelectQuery
  $select = db_select('projects', 'p')
              ->extend('PagerDefault')
              ->extend('TableSort');

  # get the desired fields
  # orderByHeader is a TableSort method (http://api.drupal.org/api/drupal/includes--tablesort.inc/function/TableSort%3A%3AorderByHeader/7)
  $select->condition('user_id', get_user_id())
         ->fields('p', array('id', 'name', 'project_count_type', 'last_updated', 'description'))
         ->limit(5)
         ->orderByHeader($header)
         ->orderBy('last_updated', 'DESC');  # TODO this call seems to be losing to orderByHeader on page load

  # execute the query
  $results = $select->execute();

  # configure the table rows, making the first column a link to our 'edit' page
  $rows = array();
  foreach ($results as $row) {
    $rows[] = array("<a href=\"/projects/edit/" . $row->id . "\">" . $row->name . '</a>',
                    $row->project_count_type,
                    $row->last_updated,
                    $row->description,
    );
  }

  $output = theme('table', array('header' => $header,
                                 'rows' => $rows ));

  # add the pager
  $output .= theme('pager');

  return $output;

}

I hope the comments in the source code are helpful in describing this solution. I'll try to add more details to this tutorial in time, but for now, the source code shown ends up rendering the HTML table shown in the image above, and as you can see from that image, you can sort the table by clicking the column headers, and also page through the result set using the pager that is displayed beneath the table.

One thing to note: This approach in Drupal 7 replaces the tablesort_sql function approach that was used in previous versions of Drupal. (If you're looking for tablesort_sql in Drupal 7, it isn't there.)