Drupal 7 - Creating Drupal style tables with paging, sorting and sticky headers

My earlier blog post on creating Drupal 6 tables (I mean html tables and not database tables) complete with paging, sorting, sticky headers and other Drupal table features attracts a decent number of visitors (Analytics tell me). So, I thought of publishing another post with the same theme but this time for Drupal 7, as there are significant changes on how you would create Html tables with Drupal 7 with all the features.

Most of the changes revolve around using the new Drupal 7 DBTNG database abstraction layer. So, let's begin with a step-by-step process to create tables with D7 (i.e. Drupal 7). In this tutorial, we will be producing a listing of published nodes from the database showing them as a completely themed table with all the regular features of a Drupal table.

  1. The first step in creating the table is to define the table headers. Here's a sample on how you define the table headers:

    $header = array(
    	array('data' => 'Title', 'field' => 'title', 'sort' => 'asc'),
    	array('data' => 'Node ID', 'field' => 'nid'),
    	array('data' => 'Type', 'field' => 'type'),
    	array('data' => 'Created', 'field' => 'created'),
    	array('data' => 'Published'),
    	);
    Note that by default, we specify an ascending sort on the title field. This means that the rendered table will be sorted in ascending order on the title field by default. However, the user can subsequently sort on any column on the rendered table by clicking its header.

    If you do not want a particular column to be sortable, do not specify the 'field' parameter for its header. More information on headers is available in my previous blog post as well as Drupal's theme_table docs.

  2.  Next create your Sql query to be executed which returns the sorted and paged results from the database. Let's have a look at the query first and then discuss it:

    $query = db_select('node', 'n')
    			->condition('status', 1)	//Only published nodes, change condition as it suits you
    			->extend('PagerDefault') 	//Pager Extender
    				->limit(10)				//10 results per page
    			->extend('TableSort')		//Sorting Extender
    				->orderByHeader($header)//Field to sort on is picked from $header
    			->fields ('n', array (
    				'nid',
    				'title',
    				'type',
    				'created',
    				'status',
    			));
    As you can see, lots of things are going on in the query. Using db_select() and condition() calls, we create a regular DatabaseSelect statement which returns the desired result set.

    Next we use a couple of Drupal's DBTNG Extenders to page and sort the result set.

    The first extender called PagerDefault is used to specify the number of results to return in a page and optionally the starting page number. Using the limit(10) call, we specify that we need 10 rows per page.
    The PagerDefault Extender replaces the pager_query method in Drupal 6.

    Next we use the TableSort extender for sorting the results on one or multiple columns. On first request to your page, the TableSort extender would pick the sorting information from the $header array (in the call to orderByHeader method). However subsequently, if the user changes the sort order or sorts on a column by clicking the column header, the TableSort extender would then pick the sort information from order and sort parameters in the query string.
    The TableSort extender replaces the call to tablesort_sql method in Drupal 6.

    You might be interested in this and this link to know more about how Extenders work in Drupal 7.

  3.  The next step is executing the query and collecting the rows from the resultset. This is a regular loop and requires no explanation (I hope):

    {syntaxhighlighter brush: php;fontsize: 100; first-line: 1; }$results = $query ->execute(); $rows = array(); foreach ($results as $node) { $rows[] = array( 'data' => array( l($node->title, 'node/'. $node->nid .'/edit'), $node->nid, $node->type, format_date($node->created), $node->status ) ); }{/syntaxhighlighter} 
  4. Then, we create a table from the headers and the result rows with a simple call to theme (or theme_table methods):

    $html = theme('table', 
    			array(
    				'header' => $header,
    				'rows'=>$rows,
    				'caption' => 'Creating Drupal 7 style tables',	//Optional Caption for the table
    				'sticky' => TRUE,						//Optional to indicate whether the table headers should be sticky
    				'empty' => 'No nodes created...',		//Optional empty text for the table if resultset is empty
    			)
    		);
    Complete information on the options for theming a table in Drupal 7 is here.

  5.  The final step is to append a pager to the table.

    $html .= theme('pager',
    			array(
    				'tags' => array()
    			)
    		);
    This step is more or less similar to how we used to do it with Drupal 6 (apart from the fact that parameters are now passed as an associative array in Drupal 7). More info on options for theme_pager are available here.

    A very important fact to note about theme_pager is that it returns the html for the pager links. Therefore, you should ensure that you append this html to the html for the table returned by the call to theme (or theme_table) earlier and not overwrite it.

    Also, a call to theme_pager should immediately succeed your database call or you might have to manage the element parameter manually if there are multiple pagers on the same Drupal page. Check the Api docs for details on the element parameter of theme_pager, and if you are still unsure, please let me know and I will explain it in another blog post.

Well that is about it. You have the html needed html for your table available in the $html variable and you can now output it to the page.

The complete code for the above step-by-step process is below:

 

{syntaxhighlighter brush: php;fontsize: 100; first-line: 1; }//Create a list of headers for your Html table (see Drupal 7 docs for theme_table here $header = array( array('data' => 'Title', 'field' => 'title', 'sort' => 'asc'), array('data' => 'Node ID', 'field' => 'nid'), array('data' => 'Type', 'field' => 'type'), array('data' => 'Created', 'field' => 'created'), array('data' => 'Published'), ); //Create the Sql query. This uses various parts of Drupal 7's new DBTNG database abstraction layer. $query = db_select('node', 'n') ->condition('status', 1) //Only published nodes, change condition as it suits you ->extend('PagerDefault') //Pager Extender ->limit(10) //10 results per page ->extend('TableSort') //Sorting Extender ->orderByHeader($header)//Field to sort on is picked from $header ->fields ('n', array ( 'nid', 'title', 'type', 'created', 'status', )); $results = $query ->execute(); $rows = array(); foreach ($results as $node) { $rows[] = array( 'data' => array( l($node->title, 'node/'. $node->nid .'/edit'), $node->nid, $node->type, format_date($node->created), $node->status ) ); } //Theme the html table $html = theme('table', array( 'header' => $header, 'rows'=>$rows, 'caption' => 'Creating Drupal 7 style tables', //Optional Caption for the table 'sticky' => TRUE, //Optional to indicate whether the table headers should be sticky 'empty' => 'No nodes created...', //Optional empty text for the table if resultset is empty ) ); //Append pager $html .= theme('pager', array( 'tags' => array() ) ); return ($html);{/syntaxhighlighter}

 

You can also find this code attached below. To test this code, put the attached file in a custom module of your Drupal installation, add a menu entry in hook_menu like below, clear your Drupal caches and try navigating to http://example.com/test/pager (replace example.com with the url to your Drupal 7 installation):

 

$items['test/pager'] = array(
  'title' => 'Drupal 7 test pager',
  'type' => MENU_CALLBACK,
  'page callback' => 'mymodule_test_pager',
  'file' => '/test/pager.test.inc',
  'access arguments' => array('access content')
  ); 	 

 

 

UPDATE:

  • May 6, 2011 - On Paul's request below, I have created another version of this blog post that demonstrates Ajaxed paging and sorting of tables in Drupal 7. The new blog post is available here:
    Ajax Sorted and Paged tables in Drupal 7

 

PHP: 
AttachmentSize
Binary Data pager.test._inc2.16 KB

Comments

This is very clear explanation of the paging feature. Thanks!Smile

rahul's picture

Thanks for the feedback Paul...

I didn't find "Module authors should also consider using the PagerDefault Extender..." very helpful in the api docs. Your example was precise. Much appreciated.

One thing you didn't say explicitly was that extend('PagerDefault') MUST be declared before ->extend('TableSort').

EDIT by admin: The order of these 2 extenders does not matter as discussed below. This comment was edited by admin to prevent other readers from carrying this mis-conception if they skipped reading the other comments below.

rahul's picture

Hi Jon, I did not realize that was a constraint in DBTNG. In fact, I had been using PagerDefault before TableSort all of the times. But I would be really surprised if this constraint actually exists. I will run some tests, but you see that ultimately Extenders add portions to the Sql Query generated by DBTNG. And because PagerDefault and TableSort work on 2 very different parts of the SELECT statement, their declaration order should be non-significant. Still I will check it once.

rahul's picture

Hi John, like I said, the order of the 2 extenders does not matter. I tested with the reverse ordering also and it runs just fine.

Me too facing wtih the ordering problem. I have to extend to PagerDefault before TableSort for pagination to work.

May be something wrong in my installation.
Anyway thanks for the post, it is very useful.

rahul's picture

Well that's strange, it works for me irrespective of the order of Extenders I use. Anyways, atleast it works in a specific order for sure (as a couple of readers have confirmed here) :)

I would like to have an Ajax-enabled paging facility. I think that this is not currently supported via the Table and Paging system and would therefore require some development?

rahul's picture

Hi Paul, yes Drupal does not natively supports Ajaxed Paging but it does not require much code to do it manually. I have created a test module demonstrating the same and a new blog post describing the code for the module. You can find the module and its description in the new blog post here:

http://www.rahulsingla.com/blog/2011/05/ajax-sorted-and-paged-tables-in-drupal-7

Nice post! Helped me alot ... in D6 everything was so easy and now .. : )

Thanks,
Matthias

After building all these data constructs, why are you rendering them to html so early? Almost everything in D7 will allow you to return render arrays and then subsequently be able to run _alter functions on them from different modules.

eg Instead of calling theme('table') above, return the render array


$content = array(
'uniqueid' => array(
'#theme' => 'table',
'#header' => $header,
'#rows' => $rows,
'#caption' => TRUE,
'#empty' => 'There is no spoon ..',
),
'uniquetoo' => array(
'#theme' => 'pager',
'#tags' => array(),
),
);

I have no idea what tags I should be adding around code on your site, but obviously <code> is not it.

rahul's picture

Hi Ted, sorry for the delay in response, I was on a vacation. Thanks for your code sample. And yes, render arrays in D7 are what should normally be used instead of manually rendering a table to html. However, I decided to do so (render to html) because:

  1. I wanted to give a complete code example on how to obtain html for a Drupal style table in D7.
  2. At some points, you need to manually render table to a html (e.g. in ajax operations).

Thanks again for the code sample, I am sure other visitors would find it useful.

Excellent article, thank you very much. It saved me a lot of time. I do agree with Ted who says it should be done using render arrays, as this is a more flexible approach and allows other modules or themes to change the output as necessary.

rahul's picture

Hi Chris, thanks for your feedback. Please see my reply to Ted's comment.

This piece of code and the excellent explanation helped me to save hours and days of work. Thank you very much.

I have a question regarding the table headers: Is there any possibilty to integrate images in the headers? I have tried a lot, but nothing didn't work out.

Just as hint for others: I tried to link the extender with the GroupBy-method, which Unfortunately does not work. I created an View in my MySQL database ... this works perfect for me and just filtered by the Condition-method.

rahul's picture

Hi Matthias, having images in table headers should have been easy. Please consider the following code for table headers:

{syntaxhighlighter brush: php;fontsize: 100; first-line: 1; }$header = array( array('data' => 'Title', 'field' => 'title', 'sort' => 'asc'), array('data' => 'Node ID', 'field' => 'nid'), array('data' => '<img src="/images/r.jpg" />', 'field' => 'type'), array('data' => 'Created', 'field' => 'created'), array('data' => 'Published'), );{/syntaxhighlighter}

I have tested it against a D7 installation, and I can see the image in "type" field.

How about having images in table rows ? Is very useful for having actions for each row id...

Thank you,

rahul's picture

You should be able to use html for a table cell. Check my document module for Drupal for live examples:
http://drupal.org/project/document 

I changed your code according to my requirements, but I could not see the pager diaplayed.The code is displayed below:
But your code works perfect. Can you tell me the reason ?

rahul's picture

Hi zub, sorry for the delayed reply. The first thing I would check is whether there are more than 10 users in your table to have the pager show. Please note that the pager is not displayed if there is only one page in your resultset.

Hi Rahul, Infact there are more than 10 records in my table. 

Thanks

rahul's picture

Sorry, I should have been more explicit. Its not only user table, rather your resultset after the joins and conditions should have more than 10 records. You can get the query using toString() and execute it directly against the db to check the number of records in the resultset.

I had a quick look at your code and it seems fine to me. If you are sure there are more than 10 records in the resultset and everything is fine on your end, then I think I will need to have a look at the installation.

I tried running the sql using the query got using toString(), and found that the resultset has 99 records, which is more than 10 anyway. I think you can run my code in your instance of drupal ,since it involves only three core drupal tables only,i.e., users, users_roles and role. By the way using  toString() was a new piece of information for me. Thanks for that.

Hi

Did you solve your problem? If yes, please share your experience about that. I am having the same problem.

BR
Alexey

No, it is not solved till now, waiting for Rahul's  response.....

rahul's picture

Hi Alexey, please see my comment below.

rahul's picture

Sorry guys for the delayed response. You can gauge the I was occupied by the fact that I am writing this comment at 3:22 am my time.

Seems like you missed a very important point regarding the extenders:
Note that $query is not altered in place. The new object is returned from extend(), and if it is not saved to a variable it will be lost.

Anytime you use extend() on a query, a new object is returned and you need to perform subsequent operations on the new object. In your sample code, the return value of extend() goes begging, you end the statement with a semi-colon and then use $query->fields and $query->execute. Here $query is not "extended" as the return value of extend was not stored. Changing your query to the following made it work:

{syntaxhighlighter brush: php;fontsize: 100; first-line: 1; }$query=$query->extend('PagerDefault') //Pager Extender ->limit(2) //10 results per page ->extend('TableSort') //Sorting Extender ->orderByHeader($header);//Field to sort on is picked from $header $query->fields ('u', array ('uid','name') ) ; $query->fields ('r',array('name') );{/syntaxhighlighter}

Note that I am saving the return value of extend. Instead I could have removed semi-colons after orderByheader and fields methods and continued invocation of sequenced method calls as in the blog post.

Thanks for pointing out that. It worked fine.

Thx for this great article. 

Thanks for this post. I like how clean and easy it is to follow. I could not get my table sorting to work untill I made the changes to adding a variable to the extender.