Fighting Crane Solutions

Sortable tables in Drupal

Submitted by Emily on Thu, 01/07/2010 - 02:35

Hey! Another drupal post! Alright!

A sortable table is one of the more useful ways of displaying information online, and like a lot of things, drupal makes it pretty easy. Unfortunately, this little feature is not exactly highly documented. I'm in the midst of creating one for a project myself, and although by now I've done it many times, the first time I did it was a bit of a struggle. So, I'm taking a break from work to document it here. Hopefully, it'll save someone else from having the same problem I did. Please note this information applies to drupal 6 only!

theme_table() is your friend

Normally, when you create a table in drupal, you turn to theme_table() - the default theme function for tables. We use the exact same theming function for a sortable table. Theme_table() takes up to four arguments, $header, $rows, $attributes (optional) and $caption (optional). Briefly, $header is an array representing the table header, $rows is an array representing the table rows, $attributes is an array of attributes to apply to the table, and $caption is a caption to apply to the table.

Let's make a sortable table!

Actually coding the sortable table involves three main steps:

Creating the header.
Writing the database query.
Creating the rows.

Let's just walk through an example. We'll create a simple table listing node titles, type, date created, and author.

Create the header

$header = array(
  array(
    'data' => t('Title'),
    'field' => 'n.title',
    'sort' => 'asc',
  ),
  array(
    'data' => t('Type'),
  ),
  array(
    'data'  => t('Author'),
    'field' => 'u.name',
  ),
  array(
    'data' => t('Date Created'),
    'field' => 'n.created',
  ),
);

The header is a multidimensional array. Each cell in the table header (i.e. each column in the table) is represented by an associative array containing one or more of the following keys:

data: Data is required. This is the text that will be displayed. Make sure you run it through the t() function so it can be translated. If a column is non-sortable, this is all you need.
field: This is required for a sortable column. This is the database field that the information in the column is coming from. The field should be referred to exactly as you would refer to it in the database query (next step!). In this case I've added a table alias since the query will be on multiple tables.
sort: This is the default sort order for this column. It's completely optional. It can either be 'asc' - ascending or 'desc' - descending. Something you won't find in the documentation is that the last defined column to have a default sort order will be the default sorted column. So, in the example, I want title to be sorted by default, so that is the only column with a default sort order since it is first.

Query that database!

$query = "SELECT n.title, n.created, n.type, u.name
            FROM {node} AS n
            LEFT JOIN {users} AS u
            ON n.uid = u.uid"
            . tablesort_sql($header);

Here we have the database query to gather the information we want. One thing to note is the table aliases which we also used in the header. Also, very important, we are adding a function call to tablesort_sql() at the end of our query and passing in the header array we defined previously. This is what actually causes the sorting to happen, so don't forget this part, or you'll have a very nice table that doesn't sort.

Get them rows!

Now all that's left is to populate our rows. This is done the same way whether or not the table is being sorted, but for the sake of completeness, I'll finish up our example.

  /**
   * Remember, $header and $query were defined above!
   */

  $result = db_query($query);

  $rows = array();
  while ($row_object = db_fetch_object($result)) {
    $rows[] = array(
      'data' => array(
        //Title
        array(
          'data' => $row_object->title,
        ),
        //Type
        array(
          'data' => $row_object->type,
        ),
        //Author
        array(
          'data' => $row_object->name,
        ),
        //Date created
        array(
          'data' => date('M d, Y', $row_object->created),
        ),
      ),
    );
  }

  print theme('table', $header, $rows);

First, we run our query the standard drupal way. Then for each result row, we create a table row. Each table row is represented by an array containing an array of cells ('data'), and optionally any attributes to be applied to the row (for example, 'class' => 'even').

Each cell is similarly represented by an associative array containing the data to go in the cell ('data' :P) and optionally any attributes to be applied to the cell.

Then, we simply call theme_table passing in our header and rows, and bob's your uncle (I always wanted to say that ;)) - you've got a sortable table.

Coda

There you go - one more great, (almost) undocumented drupal feature! And you can expand on this - you can incorporate form elements, you can also page these sortable tables if you expect a lot of results. But we'll save that for later posts. (Hey, I've gotta keep you coming back somehow!)

Update: Go read part 2! It's all about adding paging to your sortable tables. Good stuff.

Thanks Emily, that was a

Submitted by Anonymous on Tue, 02/16/2010 - 23:37.

Thanks Emily,

that was a great article and quite useful!

One thing though...

I could not get my table to render until I used:

$rows[] = array( $data->x, $data->y..);

instead of as you have it:
$rows[] = array( 'data'=>array(array('data'=>$data->x), array('data=>$data->y),...));

Cheers,

David

Re: Thanks Emily, that was a

Submitted by Emily on Sat, 08/28/2010 - 20:18.

I'm glad the article was useful!

You're right, you can define rows using a simple indexed array, but you have to use an associative array if you want to add any html attributes to the row or the cell, like a class, etc. I'm not sure why you weren't able to get the table to render using associative arrays to define the rows. I definitely tested the code before I posted, and it worked for me. I'd recommend you look over the theme_table api documentation - it may shed some light on the problem.

All the best,
Emily