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