Creating Drupal style tables

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}

 

PHP: 

Comments

Really good write up.  I'm trying to determine how to style a row in a drupal table based on teh value of a field. 

rahul's picture

Hi Trump, you have a couple of options here. On server-side, you can add custom classes to the row based on values in the field in the loop, or in client-side, you can use jQuery and CSS selectors to add desired classes/styles to the rendered rows directly.

Very informative post. Will you explain explicitly how "to add custom classes to the row  based on values in the field in the loop"? Here's a working code sample:

$rows[] = array();
while ($obj = db_fetch_object($result_set)){
  $rows[] = array(l($obj->title, "node/" . $obj->nid), $obj->field1, $obj->field2, $obj->field3, format_date($obj->created, 'medium'), l(t('Delete'), "node/" . $obj->nid . "/delete"));
}

Using JavaScript would be easy but seems unnecessary.

Thanks!

rahul's picture

Hi Eric, here's how you would do it:

 

{syntaxhighlighter brush: php;fontsize: 100; first-line: 1; }$rows[] = array(); while ($obj = db_fetch_object($result_set)){ $rows[] = array('class'=> strlen($obj->title) > 5 ? 'len-gt-5' : 'len-lt-5', 'data' => array(l($obj->title, "node/" . $obj->nid), $obj->field1, $obj->field2, $obj->field3, format_date($obj->created, 'medium'), l(t('Delete'), "node/" . $obj->nid . "/delete"))); }{/syntaxhighlighter}

Here 'len-gt-5' and 'len-gt-5' classes have been applied based on data. Check this for further details.

Thanks. Works great! How easy, once you know how.

If I can ask another question, how do you do the same on $header? I am able to add a class to any <th> and to designate the default sorting column, but I want to add a class to the entire row -- the <thead>. My code sample:

$header = array(
  array('data' => t('Name'), 'field' => 'n.title'),
  array('data' => t('Weight'), 'field' => 'b.weight', 'class' => 'funky'),
  array('data' => t('Date created'), 'field' => 'b.created', 'sort'=> 'desc'),
);

Thanks again!

rahul's picture

Immediately off my head, I do not think Drupal allows that natively (adding custom classes to table header row). But a similar effect can be achieved very easily if you add a class to the table itself (let's say, 'my-table') and then use a CSS selector of the form:

{syntaxhighlighter brush: css;fontsize: 100; first-line: 1; }.my-table tr:first-child { /* Styles definitions */ }{/syntaxhighlighter}

That would add the styles to the header row of your table. If you are worried about IE 6 or other browsers that do not support pseudo CSS selectors, use jQuery:

{syntaxhighlighter brush: jscript;fontsize: 100; first-line: 1; }jQuery('.my-table tr:first-child').addClass('my-table-header-row'){/syntaxhighlighter}

and now define your css class 'my-table-header-row'.

Thanks, Rahul! To add CSS or jQuery to the table, do I use a tpl.php? I'm using hook_theme() and hook_view() to present the created node through a template. Will I return a 2nd array in hook_theme() and ? to pass the table data into the another tpl.php?

How do I name the table 'my-table'?
Thank you very much

rahul's picture

Hi Eric, sorry for the delay. I am not sure I understand you completely. Check the documentation of theme_table method to see how to add a css class to your table (its too easy, but notice its a css class and not the "name" of the table). For css and jQuery, ideally you would put them in css and js file respectively and include those files on the page using drupal_add_css and drupal_add_js in a custom module.

Thanks Rahul. I've been meaning to continue this discussion. I was thinking about the $header variable and decided that to add classes to <thead><tr>, it will be necessary to add jQuery and CSS.

So, adding classes to $rows and $header fields is easy, but adding classes to the header row is only possible with jQuery.

I plan to post the .js script here when it's ready and ask for your review.

Thanks again for your help.

 

rahul's picture

Hi Eric, I think the following should be sufficient without requiring any jQuery:

{syntaxhighlighter brush: jscript;fontsize: 100; first-line: 1; }$table = theme_table($headers, $rows, array('class'=>array('my-table')));{/syntaxhighlighter}

And then the following css:

{syntaxhighlighter brush: css;fontsize: 100; first-line: 1; }.my-table thead tr { /* Your styles */ }{/syntaxhighlighter}

We just assign a css class to the table and then use the fact that the header row would be in a thead tag to apply desired styles to the header row.

I tried and am getting this error:

Warning: htmlspecialchars() expects parameter 1 to be string, array given in check_plain() (line 1152 of .../includes/bootstrap.inc).

The header row is not printing, although table rows and the pager are printing.

Same error when I make this modification:

$table = theme('table', $headers, $rows, array('class'=>array('my-table')));

So I read the error message and try this:

$table = theme_table($headers, $rows, 'class'=>'my-table');

which produces WSOD -- PHP Parse error: syntax error, unexpected T_DOUBLE_ARROW

Here's my original:

  $table = theme('table', $header, $rows);
  $table .= theme('pager', NULL, 10, 0);
 
  return $table;

Your thoughts?

rahul's picture

Try this:

{syntaxhighlighter brush: php;fontsize: 100; first-line: 1; }$table = theme_table($headers, $rows, array('class'=>'my-table'));{/syntaxhighlighter}

It didn't like array() placed directly in theme_table(), but when I prepared $attributes beforehand, it worked!

  $attributes = array('class' => 'my-table');
  $table = theme('table', $header, $rows, $attributes);
  return $table;

border, cellspacing, cellpadding can also be added to the $attributes array like this:

$attributes = array(
  'border' => 1,
  'cellspacing' => 0,
  'cellpadding' => 5,
  'class' => 'my-table'
);

Although since 'my-table' is now in <table ...>, I should be able to complete the formatting of the table with CSS alone. This is very cool.

FWIW, I think theme_table(…) should be theme(‘table’, ...); because it makes theme function overrides much easier.

Cheers!

where do i add these table coding to make a table. i know this is a foolish thing to ask but please help me with this thing, i am a new bee to this thing

rahul's picture

Hi Kshitiz, I usually do not answer questions not related to a blog post. If you do not know how to add a table to a Drupal page, please check the basic Drupal tutorials to creating Drupal modules, using hook_menu etc. I might not answer any further question related to creating tables in Drupal unless they relate to blog post. You can download one such sample module for D7 from here:
http://www.rahulsingla.com/blog/2011/05/ajax-sorted-and-paged-tables-in-drupal-7 

Hi Rahul -

Fantastic write-up!!  I have a follow-up question:  In Drupal, how would I specify custom sort keys for my table?  I would like to be able to sort faculty titles (I'm in a University..) according to the ranking of the faculty titles, but the title is an text field (Professor, Clinical Professor, Associate Professor, Assistant Professor, Instructor,,, etc.)  So, How do I assign '1' to Professor (highest ranking title), '2' to Associate Professor (next highest ranking) and so on?

I'm a relative "new-bee" to Drupal, so I hope this isn't the absolute "dummest" question ever posed to you!! 

Thanks!
Susan

rahul's picture

Hi Susan, you have a couple of easy options available:

  1. Use an ORDER BY clause in your sql query to fetch rows already sorted as you want them.
  2. Otherwise, in Step 4, collect all rows first (in a loop), sort them (using PHP's one of many sorting methods), and then call theme_table, something like: 

    $all_rows = array();
    while($row = db_fetch_object()) {
      $all_rows[] = $row;
      //Perform any customization on each row you need...

    sort($all_rows);

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

Hi Rahul.

This is an excellent tutorial and works perfectly.  What would be good is if each row could have a delete button - so that the user could potentially delete rows.  I am using the table to display the user's private messages (Views does not work for this!) and it would be good if they could delete messages if they want to.

Thanks in advance.  Rupert

rahul's picture

Hi rupert, sorry for the delay in replying. Was hooked up too much with other things. Anyways, what you need (delete button for a Drupal table) is already available in my Document module for Drupal.

Please download the module from the official Drupal page, you would find that a Delete button is available in document search for authorized users. Its also available for the administration page of the module at: http://example.com/admin/content/document.

well this is why i love the CMS they have got plenty of features, useful plugins, seo friendly and easy to use, last but not the least it has been an informative article cum tutorial with the useful code images :)

hi.am beginner in drupal.kindly help mke in coloring and sizing the table using inline css function

rahul's picture

Hi srinivasan, you cannot add inline css in D6 I think. However in D7, you can do something like the following:

 

{syntaxhighlighter brush: php;fontsize: 100; first-line: 1; }drupal_add_css('table { border: 1px solid black; } /*More styles*/', array('type'=>'inline'));{/syntaxhighlighter}

Styling a table using css is outside the scope of this blog post, and hence I would not answer it here. You can find examples on web, or otherwise read this page and redirect your question.

 

hi rahul,
Thanks for your kind reply.am new to drupal.so please guide me.

rahul's picture

Hi Srinivasan, on my blog posts, you would not get replies to issues not related to the topic of the blog post itself. You can either use my forums to get general help, or otherwise use the spoken-tutorial.org site to get active help on Drupal (I manage the Drupal section there and there are other volunteers also who can help you).

Can we implement the sticky header of this theme_table into a Views table?

rahul's picture

Hi Akmal, yes that is possible, Views provide a configuration option for that.
Please set "Format" for your view to "Table", and then click the "Settings" link under "Format". There you will find an option that says:
"Enable Drupal style "sticky" table headers (Javascript)"

The information in this site seems to be  interesting.Thank You.

Hi..

   I am new in drupal development .So can you provide me drupal document means it will help in learning drupal...

Thanks

rahul's picture

Hi Atul, please reference Drupal api site and developer documentation.