example of using pager_query()

Useful code that can be stolen from the taxonomy module:

function taxonomy_select_nodes($tids = array(), $operator = 'or', $depth = 0, $pager = TRUE, $order = 'n.sticky DESC, n.created DESC') {
  if (count($tids) > 0) {
    // For each term ID, generate an array of descendant term IDs to the right depth.
    $descendant_tids = array();
    if ($depth === 'all') {
      $depth = NULL;
    }
    foreach ($tids as $index => $tid) {
      $term = taxonomy_get_term($tid);
      $tree = taxonomy_get_tree($term->vid, $tid, -1, $depth);
      $descendant_tids[] = array_merge(array($tid), array_map('_taxonomy_get_tid_from_term', $tree));
    }

    if ($operator == 'or') {
      $str_tids = implode(',', call_user_func_array('array_merge', $descendant_tids));
      $sql = 'SELECT DISTINCT(n.nid), n.sticky, n.title, n.created FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE tn.tid IN ('. $str_tids .') AND n.status = 1 ORDER BY '. $order;
      $sql_count = 'SELECT COUNT(DISTINCT(n.nid)) FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE tn.tid IN ('. $str_tids .') AND n.status = 1';
    }
    else {
      $joins = '';
      $wheres = '';
      foreach ($descendant_tids as $index => $tids) {
        $joins .= ' INNER JOIN {term_node} tn'. $index .' ON n.nid = tn'. $index .'.nid';
        $wheres .= ' AND tn'. $index .'.tid IN ('. implode(',', $tids) .')';
      }
      $sql = 'SELECT DISTINCT(n.nid), n.sticky, n.title, n.created FROM {node} n '. $joins .' WHERE n.status = 1 '. $wheres .' ORDER BY '. $order;
      $sql_count = 'SELECT COUNT(DISTINCT(n.nid)) FROM {node} n '. $joins .' WHERE n.status = 1 '. $wheres;
    }
    $sql = db_rewrite_sql($sql);
    $sql_count = db_rewrite_sql($sql_count);
    if ($pager) {
      $result = pager_query($sql, variable_get('default_nodes_main', 10), 0, $sql_count);
    }
    else {
      $result = db_query_range($sql, 0, variable_get('feed_default_items', 10));
    }
  }

  return $result;
}

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

i was in the same boat as you a couple of weeks ago..it worked for me

//This is numbers per page
$num_per_page = 10;

//actual query
$query = "SELECT nid,uid FROM {node} WHERE uid=%d AND type='jobs' ORDER BY created DESC";

//the count query should be similar to the query above
$count_query = "SELECT COUNT(*) AS row_count FROM {node} WHERE uid=%d AND type='jobs' ORDER BY created DESC";

//pager_query function
$result = pager_query($query, $num_per_page, 0, $count_query, $user_load->uid);

//dont forget
$output .= theme('pager');

Hope this helps..

--------------------------------------------------
http://www.usingdrupal.com
Tutorial Screencasts

Hi I'm new in drupal and I'm trying to do a pagination in a table which the results are coming from an array.

The array looks like this :

Array
(
    [0] => Array
        (
            [0] =>Product 1	   
            [1] => $1.05
        )

    [1] => Array
        (
            [0] => Product 2   
            [1] => $1.85
        )
and so on (more then 100 results...
)

I have no idea how to do that, please help?

Thanx

-Raff

Hi,
I am new to Drupal. I tried using pager_query() for my data to be shown as a paginated result. I have a problem here. My table consists of some 30000 rows and i want to show only the 1st 50 records which are descending ordered. Looking at the code for the pager_query, it is constructed in a way that i could not limit the records retrieved as per my wish. Irrespective of whatever is the number of records i need to show in my site, drupal wants to retrieve all the records from the table. Hope my question is clear. To put in simple words, "How can i show only a limited number of rows that i desire instead of retrieving all the records from the table?"

Any help is greatly thanked.

Regards,
~ Presel Karthik

I never knew this existed before today. Here is what I am doing:

function beracah_page_homes($query) {

	$count = db_result(db_query($query));
	$query_count = db_query("SELECT $count");
	$result = pager_query("$query, 10, 0, $count_query");
	
	// do your code here... put code in $output
	
	// display navigation
	$output .= theme('pager', NULL, 10);
	return $output;

}

The pagination outputs at the bottom fine. But when I hit a page number or Next, I just get a blank page. Anyone know why? I am passing a predefined $query into this function, it is coming from a form POST / real estate search form.

I feel like im very close.

==============
delaware web design
delaware website design

You can find a comprehensive example at:

http://molecularsciences.org/drupal/drupal_paging

Quick question: Does anyone know how I can number each results? For example, if the query yields 8000 results that are split into pages of 20, right now I would see this on each page:

Result 1
Result 2
Result 3
...
Result 20

and so on for all 400 page.
What I would like is for page one to display:

1. Result 1
2. Result 2
3. Result 3
....
20. Result 20

and then for page 2 to display:

21. Result 21
22. Result 22
22. Result 23
....
40. Result 40

and so on for all 400 pages.

If you're confused by what I mean, check www.reddit.com.

Any advice on how I can modify my SQL query would be greatly appreciated.

Is it possible to use this with 2 different tables in a page? if so, can anyone pls tell me how? thx so much

Click here pager_query()