Drupal: Incorrect Pager Results

Submitted by ebeyrent on Thu, 12/13/2007 - 09:48

I've been working on a Drupal module that generates a search form and presents the results below the form. However, I ran into a strange issue where the database query returned 3 records, and yet, the pager was displaying 9 pagination links.

The code that builds and handles the query looks like this:

<?php
$query = 'select
  n.nid,
  n.title,
  DATE_FORMAT(FROM_UNIXTIME(n.created), \'%c/%e/%Y\') as created,
  c.field_product_price_value as price,
  d.name,
  t.tid
from
  {node} n
  left join {node_revisions} r on r.vid = n.vid
  left join {content_type_galleria_product} c on c.nid = n.nid
  left join {term_node} t on t.nid = n.nid
  left join {term_data} d on d.tid = t.tid ';

$groupby = ' group by n.nid';
$clauses = array();
$clauses[] = 'n.type = \'galleria_product\'';
   
foreach($form_values as $key => $value)
{   
  if(($key) && ($value != ''))
  {
    switch($key)
    {
      case 'name':
        $clauses[] = 'n.title like \'%%'.db_escape_string($value).'%%\'';
        break;
      case 'price_range':
        $clauses[] = 'c.field_product_price_value <= '.db_escape_string($value);
        break;
      case 'category':
        $clauses[] = 't.tid = '.db_escape_string($value);
        break;
      case 'created':
        switch($value)
        {   
          case 'today':
            $clauses[] = 'DATE(FROM_UNIXTIME(n.created)) = CURDATE()';
            break;
          case 'current_week':
            $clauses[] = 'WEEK(FROM_UNIXTIME(n.created)) = WEEK(NOW())';
            break;
          case 'current_month':
           $clauses[] = 'MONTH(FROM_UNIXTIME(n.created)) = MONTH(NOW())';
           break;
         case 'current_year':
           $clauses[] = 'YEAR(FROM_UNIXTIME(n.created)) = YEAR(NOW())';
           break;
       }
       break;
     default:
       break;
    }
  }
}
   
$limit = 30;
$header = array(
  array('data' => t('Name'), 'field' => 'n.title', 'sort' => 'asc'),
  array('data' => t('Rating')),
  array('data' => t('Price'), 'field' => 'c.field_product_price_value'),
  array('data' => t('Category'), 'field' => 'd.name'),
  array('data' => t('Created'), 'field' => 'n.created')
);
   
$query .= (count($clauses) ? 'WHERE ' . implode(' AND ', $clauses) : '');
$tablesort = tablesort_sql($header);
$result = pager_query($query.$groupby.$tablesort, $limit, 0);
$rows = array();
 
// Retrieve all the data found by the query
while($data = db_fetch_array($result))
{
  $current_avg = votingapi_get_voting_results('node', $data['nid'], 'percent', 'vote', 'average');
  $stars = variable_get('fivestar_stars_'. (!isset($node) ? 'default' : $node->type), 5);
  $rows[] = array(
    l($data['title'], 'node/'.$data['nid']),
    theme('fivestar_static', $current_avg[count($current_avg)-1]->value, $stars),
    '$'.$data['price'],
    l($data['name'], 'taxonomy/term/'.$data['tid']),
    $data['created'],
  );
}
   
if(empty($rows))
{
  $rows[] = array(array('data' => t('Your search failed to find any products.'), 'colspan' => 3));
}

$output = theme('table', $header, $rows);
$output .= theme('pager', null, $limit);
?>

After banging my head against the wall for a few hours, I came across a post by Kris Buytaert which explained why I was seeing this problem.

To summarize, the code that produces the pagination links is in pager.inc. Within this code is this bit:

<?php

$count_query = preg_replace(array('/SELECT.*?FROM /As', '/ORDER BY .*/'), array('SELECT COUNT(*) FROM ', ''),$query);

?>

You'll note that the pattern matching is case insensitive. As my query was written all in lowercase, the pagination code was not matching anything. All I had to do was rewrite the query such that the MySQL keywords were in uppercase.

A bug, to be sure, considering that the SQL Standard does not call for specific case. As pager.inc is part of the Drupal core, hopefully it will get patched and fixed soon.

[tags]Drupal, module, pager, query[/tags]

ebeyrent's blog

Comments

Post new comment

About Erich

Erich is a web developer and a native New Englander who is passionate about life, the universe, and everything.

He is a Drupal consultant, previously employed as a senior developer at Harvard University, working on the IQSS OpenScholar project. Prior to joining the team at Harvard, he was the engineering manager at CommonPlaces e-Solutions, in Hampstead, NH, contributing as the lead engineer on the Greenopolis.com and Twolia.com.

Erich is active in the Drupal community, having contributed modules and patches to the community. He presented at DrupalCon in Szeged Hungary, and co-presented at DrupalCon 2009 in Washington, DC.

Erich lives in New Hampshire with his wife, two sons, and three weimaraners. When not writing code, Erich enjoys landscaping and woodworking.