By ziobudda on

Hi all. I have this query:

select * from term_node tn LEFT JOIN node n on tn.nid = n.nid where tn.tid = 1157 ORDER BY n.created;

That is executed in this piece of code (in my modules):

                $query = "select * from {term_node} tn 
						LEFT JOIN {node} n on tn.nid = n.nid
						where tn.tid = ".$tid[0]->tid." 
						ORDER BY n.created";	
		echo $query;
		$result = pager_query($query, 10);
		while($data = db_fetch_object($result)) {
			$node = node_load($data->nid);
			$out .= theme("node",$node);
		}

Now I want to add pager funciont so I have read to add this line to my module:

$out .= theme('pager',NULL,10);

Is this correct ? I ask this because in my DB there are only 5 term node that have tid = 1157, but for drupal there are 587 results (I have see this inserted a line into theme_pager() function).

Why this ? Where is the error ?

M.

⋅ Categories: Drupal 5.x

Comments

Folkert’s picture

Folkert commented

The first argument of pager should be an array. so maybe try:

$output .= theme('pager', array(), 10);

The function itself will take care if you have <10 items so this should be fine as is.

hth,

ziobudda’s picture

ziobudda commented

tnx for the reply, but my problem is not resolved. I don't know how e why, but for only 10 node (SELECT COUNT(*)) , drupal think that there are 58 nodes.

M.

Freelancer Senior Drupal Developer -- http://www.ziobuddalabs.it

ebeyrent’s picture

ebeyrent commented

I am having the exact same issue on Drupal 5.3.


$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);

In the database, only three records are returned. However, the theme('pager') call creates next and previous links, and 9 page links.

Any ideas where to start looking?

ebeyrent’s picture

ebeyrent commented

You are NOT going to believe this...

The answer (for me, at least) was found here: http://www.krisbuytaert.be/blog/?q=taxonomy/term/484

My query was all in lower case, and the code in pager.inc is doing a preg_replace on the query without being case insensitive.

I changed my query to:


$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 ';

And it works now.

Petrica’s picture

Petrica commented

I run into the same problem. After i checked the caps, the query still didn't worked. Than I change it from:

$query = "
SELECT n1.nid, f.filepath, d.field_sex_value

FROM
{node} AS n1,
{node} AS n2,
{files} AS f,
{content_type_date_personale} AS d

WHERE
n1.type = 'tip_poza_utilizator'
AND n2.type = 'date_personale'
AND n1.uid = n2.uid
AND f.nid = n1.nid
AND d.nid = n2.nid
";

to

$query = "SELECT n1.nid, f.filepath, d.field_sex_value FROM {node} AS n1, {node} AS n2, {files} AS f, {content_type_date_personale} AS d WHERE n1.type = 'tip_poza_utilizator' AND n2.type = 'date_personale' AND n1.uid = n2.uid AND f.nid = n1.nid AND d.nid = n2.nid";

And it worked.
Hope that helpes.