In part one, Sortable Tables in Drupal, I went over the basics of producing a sortable table in drupal. In this post, I'm going to extend the example to show you how to integrate paging with a sortable table.
In case you're not already familiar, paging is simply breaking up a lot of content into managably sized pages. It prevents large amounts of content from slowing down page loading, and makes it easier for users to navigate through lots of content.
Drupal, of course, has built-in paging capabilities. There are a number of functions to support paging, but we only really need to know about two: pager_query() and theme_pager().
The pager_query() function performs the database query in place of the standard db_query() function. It takes four or more arguments: $query is the query to run on the database; $limit is the number of results per page, it defaults to ten; $element is optional, and used to distinguish between multiple pagers on a page; $count_query is only needed if your original query uses a COUNT(*) or GROUP BY clause that may interfere with the logic used to rewrite the original query to count the results. The function counts the total number of results of the query and uses the information to provide global pager-related variables, it then performs a db_query_range() call using the original query; finally, any variables to be inserted into the query should be passed along in order. Check out the api documentation for more details.
The theme_pager() function produces the paging links. It takes four arguments. The first, $tags, is an array of labels to use for the pager controls; it goes first, previous, middle(?) (this array index doesn't appear to be used anywhere in the theming function), next, last. Next is $limit which is the same limit passed to pager_query(), then $element - also the same as in pager_query(). Then $parameters is an array of query string parameters that will be added to the pager links, and $quantity is the number of page links to display.
We're going to use the same example table we made last time and add paging to it. This is pretty easy as there are only two changes.
Our example starts off exactly the same way: first define our header, then write our query and use a call to tablesort_sql() to provide the query result ordering.
$header = array(
array(
'data' => t('Title'),
'field' => 'n.title',
'sort' => 'asc',
),
array(
'data' => t('Type'),
),
array(
'data' => t('Author'),
'field' => 'u.name',
),
array(
'data' => t('Date Created'),
'field' => 'n.created',
),
);
$query = "SELECT n.title, n.created, n.type, u.name
FROM {node} AS n
LEFT JOIN {users} AS u
ON n.uid = u.uid"
. tablesort_sql($header);
Now we have a change: instead of using the standard db_query() call, we're going to use pager_query().
$limit = 15;
$result = pager_query($query, $limit);
Remember, $limit is the maximum number of results that will be shown per page. We're not using multiple pagers, or a rewrite-incompatible query, and our query doesn't require any substitution, so all we need to pass in is the query and the limit.
We define the table rows in the exact same way as we did before.
$rows = array();
while ($row_object = db_fetch_object($result)) {
$rows[] = array(
'data' => array(
//Title
array(
'data' => $row_object->title,
),
//Type
array(
'data' => $row_object->type,
),
//Author
array(
'data' => $row_object->name,
),
//Date created
array(
'data' => date('M d, Y', $row_object->created),
),
),
);
}
Now, we output the table, just as we did before. But, we won't have any paging unless we also print out the paging links.
print theme('table', $header, $rows);
$tags = array(
'first',
'<prev',
'',
'next>',
'last',
);
print theme('pager', $tags, $limit);
As I mentioned before, the $tags array provides text for the first, previous, next, and last links. It is optional, so you only need to provide it if you want to replace the default link text. If you create a working version of this example, you may notice that the theme_pager function conveniently adds the sorting query string to the links for you. The sorting links in the table header are also quite considerate and preserve the paging query string.
You can exercise very fine-grained control of the pager output if needed. See
So there you go, two very simple, easy steps to paging glory! Did you know, you can also page things that aren't sortable tables? Just repeat the same two steps: pager_query, theme_pager, and you can page any kind of content in drupal! It's exciting, right? RIGHT?!?!
My next post on this topic will show you how to integrate form elements into a sortable table, so tune in next time!