How to page a custom DB query

http://api.drupal.org/api/5<

bdragon< - December 12, 2006 - 14:52

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

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

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

http://api.drupal.org/api/5/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:

<?php
  $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';<br />
 
$header = array(<br />
  array(
'data' =>; 'cid', 'field' =>; 'cid'),<br />
  array(
'data' =>; 'cnid', 'field' =>; 'cnid'),<br />
  array(
'data' =>; 'left', 'field' =>; 'l', 'sort' =>; 'asc'),<br />
  array(
'data' =>; 'right', 'field' =>; 'r'),<br />
  array(
'data' =>; 'depth', 'field' =>; 'd'),<br />
  array(
'data' =>; 'title'),<br />
  );<
br />
  <
br />
 
$sql .= tablesort_sql($header);<br />
  <
br />
 
$result = pager_query($sql, 50);<br />
  <
br />
  while (
$data = db_fetch_object($result)) {<br />
   
$rows[] = array($data->;cid, $data->;cnid, $data->;l, $data->;r, $data->;d, str_repeat('--',$data->;d).$data->;title);<br />
  }<
br />
  <
br />
  if (!
$rows) {<br />
   
$rows[] = array(array('data' =>; t('Empty at the moment..'), 'colspan' =>; '6'));<br />
  }<
br />
  <
br />
  echo
theme('table', $header, $rows);<br />
  echo
theme('pager', NULL, 50, 0);<br />
 
?>
;<

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.