How do I paginate results with db_query?

Question

How do I paginate results with db_query in Drupal 7? I didn't find any example using db_query.

Answer 1

db_select() is the way to go using a so called query extender. Here is a simple example:

$query = db_select('node', 'n')
  ->condition('type', 'article')
  ->extend('PagerDefault')
  ->limit(10);

Then theme_pager() can be used for outputting a pager.

Find more details on this drupal.org working example

Answer 2

The following code works successfully for me.

$limit = 10;
$start = isset($_REQUEST['page']) ? $_REQUEST['page'] * $limit : 0;
$query = "<your query>";
$count_query = "<count query>";//count(*) AS total
$count_result = db_query($count_query);
$query_items = db_query_range($query,$start,$limit);
$items = $query_items->fetchAll();

pager_default_initialize($count_result->total, $limit, $element = 0);
$pager = theme('pager',array("quantity"=>5));
return theme("<page-theme>",array("items"=>$items ,"pager"=>$pager));

Answer 3

This worked for me:

  // $list is the list of items you are paging through
  $list = array('Cat', 'Dog', 'Mouse', 'Horse', 'Pony', 'Winged mongoose');
  $per_page = 2;

  // Initialise the pager
  $current_page = pager_default_initialize(count($list), $per_page);

  // Split your list into page sized chunks
  $chunks = array_chunk($list, $per_page, TRUE);

  // Show the pager
  print theme('pager', array('quantity', count($list)));

  // Show the appropriate items from the list
  print theme('item_list', array(
      'items' => $chunks[$current_page],
  ));