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.
- 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.
- 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.
- 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} - 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.
- 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
This is very clear explanation of the paging feature. Thanks!
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.
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?
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.
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.
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.
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 ?
I’m getting much the same thing. I’m not using the TableSort extender, just the PagerDefault, but it’s just not working. It’s not restricting results (despite a limit of ten) and the call to theme(‘pager’) returns nothing. I’ve tried putting the call to theme(‘pager’) right after the query, I’ve even tried sending it to the screen with drupal_set_messge to try and intercept it – nothing. I must be missing something really simple, but the paging just isn’t taking effect…
Hello I found your tutorial. It is the best for what I was looking for.
I am using it in my Drupal 7 website. However, the table headers are not sticky. I just downloaded your module and installed it in a local server. It also do not have the sticky header. But as the theme_table() says, sticky=> TRUE is what we need. I also printed the value in the includes/theme.inc @ line 1633. It also showd 1 as I set the array paameter as TRUE. Do you have any idea what is going on.?
very useful. thanks
Hi Raoul,
I’m encountering something really strange with my table. I don’t know whether it’s the right place here, but you are very smart and helpful guy. Thank you for that 🙂
My table gets the results from a MySQL view filtering by certain criteria. Everything works out fine, but sometimes the integers from the table are just others than the one in the view. When I do the same query in phpmyadmin, there are always the right results, but in drupal it happens to go sometimes wrong.
Here is an example: http://www.masterwiki.de/application/results/master/163/401/1241 -> the first entry in the table: There should be a “1”. When you click on the details symbol you can see that there is actually only one dataset behind it and phpmyadmin also comes up only with a “1”. However, it shows me “4”.
This is kind of strange to me. Maybe you have an idea? I would realy appreciate that 🙂
Thank you, Matthias
How you you do this this a custom table. I have tables that dont use the node table nor nid. how would this section look? or would it look the same?
thanks
Rahul
I dont think your answering the badly asked question. I get that the numbers, formatting and fields will change.
I also get that you’re looping through the results and putting the array into the a rows array. anyway lets say my table has 3 fields; firstname, lastname and email
I assume that would be valid?
As following your quick advice, I am able to add as many features to my site…………….
Keep helping us more on these eye catching topics.
I cannot download file attachment the pager.test file. It states that I do not have permission.
DL
Hi,
If you use a form to generate results from, how to keep values from the form when changing page using the pager ?
hi rahul your post is very helpful.
i stuck on one thing that how can i add seprate pager thou i have one formcontaining 4 tables and pager renders all four tables result so do u have any idea about that.
thanks
sanjay jewani
Hi! Wonderful examples!
I think you should add
->addTag(‘node_access’)
to your queries to let drupal check permissions for node access.
gretings! Marco
Just a note– on my install, the results are always returned in lower case, so if you have tables with mixed-case names, when you get to the:
$html = theme(‘table’,
Great post awesome code. Thanks you help me a lot i saved a lot of my time.
Hi Rahul
Thanks for the great post it helped me a lot! Possible to have custom search on it?
Newbie drupal module developer!.
Rgds,
bryan
Hi Rahul,
My pagination is not working properly. It gives me same records on every page.
Below is my code
<?php
$output = ”;
$header = array(
array(‘data’ => ‘USER’, ‘field’ => ‘title’),
array(‘data’ => ‘Created On’, ‘field’ => ‘created’, ‘sort’ => ‘desc’),
array(‘data’ => ‘DETAILS’)
);
$query = db_select(‘node’, ‘n’)
->extend(‘PagerDefault’)->limit(20)->extend(‘TableSort’)->orderByHeader($header)
->condition(‘type’, ‘nontextmaterials’)
->fields(‘n’, array(‘nid’, ‘title’, ‘created’));
$results = $query->execute();
$rows = array();
foreach($results as $row)
{
$rows[] = array(‘data’ => array(
$row->title,
date(“m/d/y”, $row->created),
l(t(‘View Details’), ‘node/’.$row->nid.’/complete’, array(‘attributes’ => array(‘target’ => ‘_blank’))),
));
}
$output .= theme_table(
array(
“header” => $header,
“rows” => $rows,
“attributes” => array()
)
);
$output .= theme(‘pager’, array(‘tags’ => array()));
return $output;
?>
above code is executing properly but when clicked on any of the pagination links shows same data in every page
i am not able to figure it out where i am wrong kindly suggest me if i need to change any thing in my code.
Hi, may i know how to add a filtering of data? Let’s say i have a table of records with a column ‘status’ having either ‘New’, ‘Edited’, ‘No action required’. I wish to display the table of records according to user’s option chosed.
Hope u can help.
Thanks!
I want data rows is some fom item as: textfield, select,… is Any body know?
It was very useful, thanks
Really great job! Keep on 🙂
Greetings from Germany
Felix
I constantly find all these great looking code snippets for Drupal but I have no idea where to put them in Drupal.
Help!
Thanks alot rahul, it helped me so well….
I have a table of data which can be filtered by Ajax through a drop down box. If I sort my Table after applying an ajax filter I get a white screen.
Any idea why this might happen and how I could fix it.
Thanks