How to page a custom DB query
czheng - December 12, 2006 - 19:29
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:
<?php
$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
Does this help?
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
Check out
Check out pager_query():
http://api.drupal.org/api/4.7/function/pager_query
----------------------
Current Drupal project: http://www.ubercart.org
http://api.drupal.org/api/4.7
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:
<?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';
$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.
Got it working, thanks very
Got it working, thanks very much.
Great post!
Great post! Very instructive and helpful. Glad I found it. Thank you!