function pager_query
6.x pager.inc | pager_query($query, $limit = 10, $element = 0, $count_query = NULL) |
Perform a paged database query.
Use this function when doing select queries you wish to be able to page. The pager uses LIMIT-based queries to fetch only the records required to render a certain page. However, it has to learn the total number of records returned by the query to compute the number of pages (the number of records / records per page). This is done by inserting "COUNT(*)" in the original query. For example, the query "SELECT nid, type FROM node WHERE status = '1' ORDER BY sticky DESC, created DESC" would be rewritten to read "SELECT COUNT(*) FROM node WHERE status = '1' ORDER BY sticky DESC, created DESC". Rewriting the query is accomplished using a regular expression.
Unfortunately, the rewrite rule does not always work as intended for queries that already have a "COUNT(*)" or a "GROUP BY" clause, and possibly for other complex queries. In those cases, you can optionally pass a query that will be used to count the records.
For example, if you want to page the query "SELECT COUNT(*), TYPE FROM node GROUP BY TYPE", pager_query() would invoke the incorrect query "SELECT COUNT(*) FROM node GROUP BY TYPE". So instead, you should pass "SELECT COUNT(DISTINCT(TYPE)) FROM node" as the optional $count_query parameter.
Parameters
$query: The SQL query that needs paging.
$limit: The number of query results to display per page.
$element: An optional integer to distinguish between multiple pagers on one page.
$count_query: An SQL query used to count matching records.
...: A variable number of arguments which are substituted into the query (and the count query) using printf() syntax. Instead of a variable number of query arguments, you may also pass a single array containing the query arguments.
Return value
A database query result resource, or FALSE if the query was not executed correctly.
Related topics
- aggregator_feed_items_load in drupal-6.x/
modules/ aggregator/ aggregator.pages.inc - Load feed items by passing a SQL query.
- blog_page_last in drupal-6.x/
modules/ blog/ blog.pages.inc - Menu callback; displays a Drupal page containing recent blog entries of all users.
- blog_page_user in drupal-6.x/
modules/ blog/ blog.pages.inc - Menu callback; displays a Drupal page containing recent blog entries of a given user.
- comment_admin_overview in drupal-6.x/
modules/ comment/ comment.admin.inc - Form builder; Builds the comment overview form for the admin.
- comment_render in drupal-6.x/
modules/ comment/ comment.module - Renders comment(s).
- drupal_error_handler in drupal-6.x/
includes/ common.inc - Log errors as defined by administrator.
File
- drupal-6.x/
includes/ pager.inc, line 50 - Functions to aid in presenting database results as a set of pages.
Code
function pager_query($query, $limit = 10, $element = 0, $count_query = NULL) {
global $pager_page_array, $pager_total, $pager_total_items;
$page = isset($_GET['page']) ? $_GET['page'] : '';
// Substitute in query arguments.
$args = func_get_args();
$args = array_slice($args, 4);
// Alternative syntax for '...'
if (isset($args[0]) && is_array($args[0])) {
$args = $args[0];
}
// Construct a count query if none was given.
if (!isset($count_query)) {
$count_query = preg_replace(array('/SELECT.*?FROM /As', '/ORDER BY .*/'), array('SELECT COUNT(*) FROM ', ''), $query);
}
// Convert comma-separated $page to an array, used by other functions.
$pager_page_array = explode(',', $page);
// We calculate the total of pages as ceil(items / limit).
$pager_total_items[$element] = db_result(db_query($count_query, $args));
$pager_total[$element] = ceil($pager_total_items[$element] / $limit);
$pager_page_array[$element] = max(0, min((int) $pager_page_array[$element], ((int) $pager_total[$element]) - 1));
return db_query_range($query, $args, $pager_page_array[$element] * $limit, $limit);
}