Dec 13

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:

PLAIN TEXT
PHP:
  1. <?php
  2. $query = 'select
  3.   n.nid,
  4.   n.title,
  5.   DATE_FORMAT(FROM_UNIXTIME(n.created), \'%c/%e/%Y\') as created,
  6.   c.field_product_price_value as price,
  7.   d.name,
  8.   t.tid
  9. from
  10.   {node} n
  11.   left join {node_revisions} r on r.vid = n.vid
  12.   left join {content_type_galleria_product} c on c.nid = n.nid
  13.   left join {term_node} t on t.nid = n.nid
  14.   left join {term_data} d on d.tid = t.tid ';
  15.  
  16. $groupby = ' group by n.nid';
  17. $clauses = array();
  18. $clauses[] = 'n.type = \'galleria_product\'';
  19.    
  20. foreach($form_values as $key => $value)
  21. {   
  22.   if(($key) && ($value != ''))
  23.   {
  24.     switch($key)
  25.     {
  26.       case 'name':
  27.         $clauses[] = 'n.title like \'%%'.db_escape_string($value).'%%\'';
  28.         break;
  29.       case 'price_range':
  30.         $clauses[] = 'c.field_product_price_value <= '.db_escape_string($value);
  31.         break;
  32.       case 'category':
  33.         $clauses[] = 't.tid = '.db_escape_string($value);
  34.         break;
  35.       case 'created':
  36.         switch($value)
  37.         {   
  38.           case 'today':
  39.             $clauses[] = 'DATE(FROM_UNIXTIME(n.created)) = CURDATE()';
  40.             break;
  41.           case 'current_week':
  42.             $clauses[] = 'WEEK(FROM_UNIXTIME(n.created)) = WEEK(NOW())';
  43.             break;
  44.           case 'current_month':
  45.            $clauses[] = 'MONTH(FROM_UNIXTIME(n.created)) = MONTH(NOW())';
  46.            break;
  47.          case 'current_year':
  48.            $clauses[] = 'YEAR(FROM_UNIXTIME(n.created)) = YEAR(NOW())';
  49.            break;
  50.        }
  51.        break;
  52.      default:
  53.        break;
  54.     }
  55.   }
  56. }
  57.    
  58. $limit = 30;
  59. $header = array(
  60.   array('data' => t('Name'), 'field' => 'n.title', 'sort' => 'asc'),
  61.   array('data' => t('Rating')),
  62.   array('data' => t('Price'), 'field' => 'c.field_product_price_value'),
  63.   array('data' => t('Category'), 'field' => 'd.name'),
  64.   array('data' => t('Created'), 'field' => 'n.created')
  65. );
  66.    
  67. $query .= (count($clauses) ? 'WHERE ' . implode(' AND ', $clauses) : '');
  68. $tablesort = tablesort_sql($header);
  69. $result = pager_query($query.$groupby.$tablesort, $limit, 0);
  70. $rows = array();
  71.  
  72. // Retrieve all the data found by the query
  73. while($data = db_fetch_array($result))
  74. {
  75.   $current_avg = votingapi_get_voting_results('node', $data['nid'], 'percent', 'vote', 'average');
  76.   $stars = variable_get('fivestar_stars_'. (!isset($node) ? 'default' : $node->type), 5);
  77.   $rows[] = array(
  78.     l($data['title'], 'node/'.$data['nid']),
  79.     theme('fivestar_static', $current_avg[count($current_avg)-1]->value, $stars),
  80.     '$'.$data['price'],
  81.     l($data['name'], 'taxonomy/term/'.$data['tid']),
  82.     $data['created'],
  83.   );
  84. }
  85.    
  86. if(empty($rows))
  87. {
  88.   $rows[] = array(array('data' => t('Your search failed to find any products.'), 'colspan' => 3));
  89. }
  90.  
  91. $output = theme('table', $header, $rows);
  92. $output .= theme('pager', null, $limit);
  93. ?>

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:

PLAIN TEXT
PHP:
  1. <?php
  2.  
  3. $count_query = preg_replace(array('/SELECT.*?FROM /As', '/ORDER BY .*/'), array('SELECT COUNT(*) FROM ', ''),$query);
  4.  
  5. ?>

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: , , ,

Related:

Posted in Technology, PHP, SQL, Drupal | No Comments »