UPDATE: Drupal 7 version of this blog post is available here.

I recently needed to create a table in Drupal (while working on my next Drupal open source module, Document). It’s just too easy outputting a table in PHP using a simple for loop over data fetched from the db. However, I wanted the table to look exactly like a native Drupal table, with sticky headers, ability to sort selected columns, paging etc.

A quick search threw up a couple of helpful pages on Drupal but I could not find the exact process of achieving everything I wanted to do with the table. So, I had to spend some time getting everything in place. Here’s the process (the code has been taken from the above referenced Document module itself. You can see it in action by downloading and installing the module to a Drupal installation, or at my Sandbox site for Drupal):

1. The first step is to define the headers for the table columns. It could be as simple as an array e.g.

   $headers = array('Type', 'Title', 'Author', 'Year of Publication', 'Keywords');

   However, for more advanced functionality (like ability to sort), you would need to provide a bit more detail to Drupal.

   $headers = array('',
       array('data' => t('Type'), 'field' => 'd.type'),
       array('data' => t('Title'), 'field' => 'title', 'sort' => 'asc'),
       array('data' => t('Author'), 'field' => 'author'),
       array('data' => t('Year of Publication'), 'field' => 'publish_year'),
       'Keywords',
       '');

   There are multiple interesting points to note above. One, you can provide a simple column title, or a nested array with more details. Two, For a nested array, data key should contain the column header to be shown, field key should contain the database column name (this allows Drupal to sort on that column when the column header is clicked). Note that the column name can contain the table name or a table alias prefix that you use in your sql query for fetching data, in case the query contains a join and the joined tables have columns with the same name.

   Only those  columns would be sortable (Drupal would emit a link for column header which when clicked would sort the data on that column), which have a field key specified in the column definition supplied to Drupal. I did not want a couple of columns above to be sortable (one of which was actually an image column), and therefore did not specify the field for them.

   Lastly, you can also specify the sort key for exactly one column, in case you want data to be sorted on a particular column on the initial page render. The value could be ‘asc’ or ‘desc’ with obvious meanings.

   Drupal style sticky headers are automatically enabled for the table when the data is passed through a theme(‘table’) method call (covered below).

2. Prepare your sql query. Here’s the query from the Document module’s admin section:
   {syntaxhighlighter brush: php;fontsize: 100; first-line: 1; }$sql = sprintf(‘SELECT * FROM {node} n INNER JOIN {document} d ON n.vid = d.vid WHERE n.status <> %d’, DOCUMENT_STATUS_PUBLISHED);{/syntaxhighlighter}
3. Request Drupal to add the ORDER BY clause to your query. You should not specify an ORDER BY clause in your query manually. That would lead to a Sql error and an entry in your watchdog each time this query executes on your installation. However, any WHERE conditions should already exist in the above query.

   //add the order by clause
   $sql .= tablesort_sql($headers);

   Notice that I am appending the result of the tablesort_sql method call to the existing query, and not overwriting it. This method either returns the ORDER BY clause for the default sort column from your $headers definition (if one was specified), or the appropriate column field and sort direction, if the current page was requested as a result of the user clicking a sortable column from the table rendered on previous request.

4. Execute your query, and create an array of row values from the query resultset. An important point to remember is to execute the query through Drupal’s pager_query method (and not db_query), if you want to add paging to your results (covered below).
   {syntaxhighlighter brush: as3;fontsize: 100; first-line: 1; }$results = pager_query($sql, 10);

   while ($doc = db_fetch_object($results)) {
   $rows[] = array(
   sprintf($imgPublish . ‘&nbsp;&nbsp;&nbsp;’ . $imgDelete, $doc->nid),
   $doc->type,
   l($doc->title, ‘node/’ . $doc->nid),
   $doc->author,
   $doc->publish_year,
   $doc->keywords,
   l(‘Download’, $doc->url, array(
   ‘attributes’ => array(
   ‘target’ => ‘_blank’))));
   }{/syntaxhighlighter}

5.  Pass your row results and headers through Drupal’s theme method for generating a themed html table for your data.

   $table = theme('table', $headers, $rows);

    

6. Finally, add a pager to your table, if you want to have paged results. If paging is not desired, then it is important to run the query through Drupal’s regular db_query method in Step 4) above.

   $table .= theme('pager', array(), 10, 0);

   Again note that the result of theme method call for a pager is appended to the table generated in the previous step.

   It is important to take care that you add paging to your table immediately after the pager_query before allowing any other part of Drupal to execute a pager_query method call. Otherwise you would need to manually manage a unique integer (that Drupal calls element to distinguish between multiple pagers on one page). I have not covered this aspect in the blog post, but tell me in the comments below if you need this, and I will expand the post to cover this aspect.

7. Well, that’s it. You have your table html in the $table variable. Most of the time, you would be returning this html either from some callback method (e.g. for a block etc.), or by assigning it to a form element whose type should be set to markup. Document module assigns it to a form element as follows:

     $form['document_moderate_table'] = array(
         '#type' => 'markup',
         '#value' => $table);

Once you get a hold of things, you would find the entire process a breeze. You can add additional effects to your table output. e.g. You can add attributes to your rows while iterating them in the loop of Step 5) above:

{syntaxhighlighter brush: php;fontsize: 100; first-line: 1; }while ($doc = db_fetch_object($results)) {
$rows[] = array(
…
array(‘data’ => $doc->type, ‘class’ => ‘class-for-doc-type-table-cell’),
…
}{/syntaxhighlighter}

Also, Drupal automatically adds “odd” and “even” to alternate rows of the table which you can use to style alternate rows differently by adding appropriate css classes.

Here’e the complete code (from the Document module itself):

{syntaxhighlighter brush: php;fontsize: 100; first-line: 1; }$headers = array(
”,
array(‘data’ => t(‘Type’), ‘field’ => ‘d.type’),
array(
‘data’ => t(‘Title’),
‘field’ => ‘title’,
‘sort’ => ‘asc’),
array(‘data’ => t(‘Author’), ‘field’ => ‘author’),
array(
‘data’ => t(‘Year of Publication’),
‘field’ => ‘publish_year’),
‘Keywords’,
”);

$sql = sprintf(‘SELECT * FROM {node} n INNER JOIN {document} d ON n.vid = d.vid WHERE n.status <> %d’, DOCUMENT_STATUS_PUBLISHED);
//add the order by clause
$sql .= tablesort_sql($headers);
$results = pager_query($sql, 10);

$imgPublish = theme_image(document_image_url(‘spacer.gif’), ‘Publish’, ‘Publish’, array(
‘onclick’ => ‘doc.changeDocStatus(this, %1$d, \’icon-publish\’, true);’,
‘class’ => ‘icon-publish’,
‘width’ => 16,
‘height’ => 16), FALSE);
$imgDelete = theme_image(document_image_url(‘spacer.gif’), ‘Delete’, ‘Delete’, array(
‘onclick’ => ‘doc.deleteDoc(this, %1$d, \’icon-delete\’);’,
‘class’ => ‘icon-delete’,
‘width’ => 16,
‘height’ => 16), FALSE);

$rows = array();
while ($doc = db_fetch_object($results)) {
$rows[] = array(
sprintf($imgPublish . ‘&nbsp;&nbsp;&nbsp;’ . $imgDelete, $doc->nid),
$doc->type,
l($doc->title, ‘node/’ . $doc->nid),
$doc->author,
$doc->publish_year,
$doc->keywords,
l(‘Download’, $doc->url, array(
‘attributes’ => array(
‘target’ => ‘_blank’))));
}
$table = theme(‘table’, $headers, $rows);
$table .= theme(‘pager’, array(), 10, 0);

$form = array();
$form[‘document_moderate_table’] = array(
‘#type’ => ‘markup’,
‘#value’ => $table);{/syntaxhighlighter}