Hi folks,

A quick question that I'm hoping has a quick solution. I've got a handful of custom views on my site that I create using DB queries like this one:


$display_type = "'content_journal_issue'";
$sql = "SELECT * FROM node WHERE node.type LIKE $display_type ORDER BY node.created DESC";
$result = db_query($sql);
   while($anode = db_fetch_object($result)) {
     $cissue .= node_view(node_load(array('nid' => $anode->nid)), 1);
   print "<div class='issue-view'>" . $cissue . "</div>";

Is there an easy way to get these results paged using Drupal's paging system? I've poked around for a bit on the forums but haven't found an answer...

Thanks in advance,

craig

Comments

styro’s picture

http://api.drupal.org/api/4.7/function/pager_query

Note: I don't actually have any experience using it though :)

--
Anton
New to Drupal? | Forum posting tips | Troubleshooting FAQ

rszrama’s picture

Check out pager_query():

http://api.drupal.org/api/4.7/function/pager_query

----------------------
Current Drupal project: http://www.ubercart.org

bdragon’s picture

http://api.drupal.org/api/4.7/function/tablesort_sql
This will let sorting and limits work.

http://api.drupal.org/api/4.7/function/pager_query
This will perform a limit/top query.

http://api.drupal.org/api/4.7/function/theme_table
This has the documentation on how you need to set up your $header and $rows arrays.

http://api.drupal.org/api/4.7/function/theme_pager
This is used to do the pager links on the bottom.

Here's an example page snippet that I use on one of my testing sites:

$sql = 'SELECT l.*,c.cnid,n.title FROM {category} c INNER JOIN {node} n ON c.cid=n.nid INNER JOIN {category_lrd} l ON c.cid=l.cid';
$header = array(
  array('data' => 'cid', 'field' => 'cid'),
  array('data' => 'cnid', 'field' => 'cnid'),
  array('data' => 'left', 'field' => 'l', 'sort' => 'asc'),
  array('data' => 'right', 'field' => 'r'),
  array('data' => 'depth', 'field' => 'd'),
  array('data' => 'title'),
);

$sql .= tablesort_sql($header);

$result = pager_query($sql, 50);

while ($data = db_fetch_object($result)) {
    $rows[] = array($data->cid, $data->cnid, $data->l, $data->r, $data->d, str_repeat('--',$data->d).$data->title);
  }

  if (!$rows) {
    $rows[] = array(array('data' => t('Empty at the moment..'), 'colspan' => '6'));
  }

  echo theme('table', $header, $rows);
  echo theme('pager', NULL, 50, 0);

All columns with a field attribute in the header are sortable. The tablesort_sql line makes it work.

Hope this is enough to go by.

czheng’s picture

Got it working, thanks very much.

ardee-1’s picture

Great post! Very instructive and helpful. Glad I found it. Thank you!