Home › Drupal Collection

How to page a custom DB query

Sat, 03/24/2007 - 00:19 — Nancy

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.