function pager_query

You are here

4.6 pager.inc pager_query($query, $limit = 10, $element = 0, $count_query = NULL)
4.7 pager.inc pager_query($query, $limit = 10, $element = 0, $count_query = NULL)
5 pager.inc pager_query($query, $limit = 10, $element = 0, $count_query = NULL)
6 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

28 calls to pager_query()
aggregator_feed_items_load in modules/aggregator/aggregator.pages.inc
Load feed items by passing a SQL query.
blog_page_last in modules/blog/blog.pages.inc
Menu callback; displays a Drupal page containing recent blog entries of all users.
blog_page_user in modules/blog/blog.pages.inc
Menu callback; displays a Drupal page containing recent blog entries of a given user.
comment_admin_overview in modules/comment/comment.admin.inc
Form builder; Builds the comment overview form for the admin.
comment_render in modules/comment/comment.module
Renders comment(s).

... See full list

1 string reference to 'pager_query'
drupal_error_handler in includes/common.inc
Log errors as defined by administrator.

File

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);
}

Comments

When theming a page showing results of a query executed with pager_query(), don't forget to add the pager to the end of the page using theme('pager'). See theme_pager().

The $count_query is case sensitive, you have to write your SQL in uppercase (SELECT and FROM) to make it work

$query also case sensitive... If you use small select and from in your sql query it doesn't work. Use SELECT and FROM, same applies to ORDER BY. This is because of preg_replace is not checking for case sensitive. Some one need to do change this in the core if i am not wrong.

pager_query() uses your original query to create the $count_query parameter by taking your original query and replacing your column names with 'COUNT(*)'. This $count_query is used to calculate the total number records that pager_query() will have to page.

For example, if your original query were:

"SELECT nid, type FROM node WHERE status = '1' ORDER BY sticky DESC, created DESC"

pager_query() would copy it and rewrite it, replacing the column names 'nid, type' with 'COUNT(*)', as:

"SELECT COUNT(*) FROM node WHERE status = '1' ORDER BY sticky DESC, created DESC"

to use it as the $count_query.

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 write your own query to count the total number of records that pager_query() will have to page and pass that query in the $count_query parameter.

If you need to supply any arguments to your query just add them after all the pager_query arguments like so:

Example Query:

<?php
  $query
= "SELECT heading, summary, publish_date FROM {news} WHERE category_id = %d
            ORDER BY publish_date DESC"
;
 
$result = pager_query($query, 10, 0, NULL, $cat_id);
?>

So, for the pager_query example above let me explain the arguments:
1st arg ($query) = The query to be executed.
2nd arg (10) = $limit The number of query results to display per page.
3rd arg (0) = $element The integer number of the pager we are working with.
4th arg (NULL) = $count_query Just put NULL which is the default for this variable.
5th arg ($cat_id) = The argument that needs to be passed to the query itself.

Hope this helps.

sure did... this should be with the api documentation..

I misread this and thought the $count_query parameter cannot have a HAVING COUNT or GROUP BY clause, but it can have both if you need it.

In my case I was trying to page a search result for core profiles. Here's an example of how it can be done:

<?php
$query = "SELECT v.uid
  FROM profile_values AS v
INNER
  JOIN profile_fields AS f
    ON f.fid = v.fid
WHERE ( f.name = 'profile_field1'
     AND v.value = $value1 )
    OR ( f.name = 'profile_field2'
     AND v.value = $value2 )
GROUP
    BY v.uid
HAVING COUNT(*) = 2";

$count_query = "SELECT COUNT(*) FROM (" . $query . ") AS count_query";

$result = pager_query($query, 10, 0, $count_query);
$output .= theme('pager', NULL, 10, 0);

Thanks for that snippet.

Ive noticed that there doesn't appear to be a function for getting the number of rows from a result in D6. I'm guessing thats why a second query is needed to get the count for the pager. Is there a reason why there isn't an equivalent to something like php's mysqli?:

mysqli_num_rows (mysqli_result)

EDIT: Its because some other DB's dont have this functionality so to ensure drupal is more portable they dropped it from D6.

<?php
$query
= "SELECT title FROM node ORDERY BY nid DESC";
$count_query = "SELECT COUNT(*) FROM (" . $query . ") AS count_query";

$result = pager_query($query, 10, 0, $count_query);

$output .= theme('pager', NULL, 10, 0);

print

$result;

print

$output;
?>

no result or pager on my page. i'm not sure what's going on. instead of $output i have $content variable so i set the theme function to $content as well and i tried to print that and my page still comes up blank

Can you tell me, where have you written this code?
Try with

return $output;

Let me know if this helps you.

Regards.

this works ;)

hi,

i got the same problem, but i its working now.
just change the select, where and from to uppercase letter in your query.

like

SELECT f.filepath, f.filename, n.nid, n.title FROM files f inner join content_type_ngallery cn on f.fid = cn.field_ngal_fid inner join node n on n.nid = cn.nid WHERE f.uid='". $uid ."' and n.type = 'ngallery'", 4, 0, NULL

and it works for me.

If you're like me you were wondering if you could pass arguments dynamically to your count query when you plug it into pager_query. Well you can just fine, but the method is odd. Let's say you're storing your SQL arguments in an array called: $sql_args. This array contains all the arguments all of your queries would need. Most likely, you assume (as I did) that different arguments would be passed to the two different SQL strings. I imagined it would iterate through the array, count the number of elements needed to populate the main SQL query and then just pass that number over and continue iterating through the array's elements with the count query (what a wonderful world). Basically, if your main SQL query has four args and you make the first four members of your array those args, I assumed that the fifth member of the array will be used to populate the count query since the array will pick back up iterating right where it left off.

It doesn't work like this at all. Instead, when pager_query goes to pass arguments to the SQL count query, it restarts iterating through the $sql_args array again. In other words, it passes the first element in the array to the SQL count query. Translation: if you want to pass SQL arguments to both your main query and count query they have to line up.

Example. This will work:

<?php
  $main_sql
= "SELECT * FROM {dummy_table} WHERE id = '%s' AND status = '%s'";
 
$count_sql = "SELECT COUNT(*) FROM {dummy_table} WHERE id = '%s';"
 
$sql_args = array(
   
$some_id_var,
   
$some_status_var,
  );
 
$limit = 100
  $results
= pager_query($main_sql, $limit, 0, $count_sql, $sql_args);
?>

This will give you exactly what you want, but ONLY because the first argument you need in the main query happens to be the first (and only) argument you need in the count query.

This example will not work:

<?php
  $main_sql
= "SELECT * FROM {dummy_table} WHERE status = '%s' AND id = '%s'";
 
$count_sql = "SELECT COUNT(*) FROM {dummy_table} WHERE id = '%s'";
 
$sql_args = array(
   
$some_status_var,
   
$some_id_var,
  );
 
$limit = 100
  $results
= pager_query($main_sql, $limit, 0, $count_sql, $sql_args);
?>

Notice how the only differences are that in the main SQL query we have swapped status' and id's position and we did the same in $sql_args so our arguments will line up. This will result in passing $some_status_var to your count query as its argument, which will mess things up (since you obviously are not looking for that). This limitation does not seem to be able to be beaten, you cannot pass in multiple arrays (like db_query, when an array is passed in it is the ONLY thing that will be used for arguments) and if you pass in a list of arguments (manually populate them into pager_query) it repeats the same behavior and causes the same problem. The only real solution is to only use a SQL count query when you absolutely must, as the default given by pager_query is pretty good most of the time, and if you MUST use a SQL count query, populate it when building the string like so:

$count_query = "SELECT COUNT(*) FROM {dummy_table} WHERE id = '$some_id_var'";

Which can be dangerous. If you want to enjoy the security that pager_query and db_query give you by escaping your arguments, then you simply have to make sure your argument order lines up in the main query and the count query. Hope this helped someone, reply and ask a question if this was at all confusing. Or if I gave some wrong info!

hi i use code below in custom block for show 2 item per page but just showing 1 item pls say me what is problem of this code?

<?php
if ( arg(0) == 'node' && is_numeric(arg(1)) && ! arg(2) )
{
 
$node = node_load(arg(1));
 
$nidd=$node->nid;
 
$num_per_page =2;
 
$related_trailer_sql="SELECT * FROM fimdbcontent_field_trailer fft INNER JOIN fimdbfiles fif ON fft.field_trailer_fid=fif.fid where nid=%d";
 
$count_query = "SELECT COUNT(*) AS row_count FROM fimdbcontent_field_trailer fft INNER JOIN fimdbfiles fif ON fft.field_trailer_fid=fif.fid where nid=%d";
 
$result = pager_query($related_trailer_sql, $num_per_page, 0, $count_query,$nidd);
  while(
$row=db_fetch_array($result)){
   
$output=($row['nid'].'<br>');
   
$video_url=$row['filepath'];
   }
$output.='<div style="overflow:hidden;" class="video-item">
      <div class="slate">
      <a data-type="single" data-video="vi1860541977" data-context="fimdb" class="related-video-link" itemprop="trailer"
         href="/fimdb/'
.$video_url.'" rel="shadowbox;width=800;height=600;">
         <img src="/fimdb/test.jpg" alt="video"  class="related-video">
         </a>
         </div>
         </div>'

$output.= theme('pager',NULL,$num_per_page,0);
print(
$output);
}

?>

@hjc1710, I was able to solve this using MySQL inline comments

Notice the inline comment on the count query. That should now work.

<?php
  $main_sql
= "SELECT * FROM {dummy_table} WHERE status = '%s' AND id = '%s'";
 
$count_sql = "SELECT COUNT(*) /* %s */ FROM {dummy_table} WHERE  id = '%s'";
 
$sql_args = array(
   
$some_status_var,
   
$some_id_var,
  );
 
$limit = 100
  $results
= pager_query($main_sql, $limit, 0, $count_sql, $sql_args);
?>

because the regex doesnt match if there are breaks between the SELECt and FROM
and so it doesnt create a correct count query