6

I am using the Drupal 7 and i want to use the Pager into my query

This is my query and the way i can use

$qury = db_select("SELECT `entity_id` , `field_keywords_value` , COUNT( DISTINCT ( `entity_id` )) total 
FROM `field_data_field_keywords`
WHERE `bundle` = 'song' 
GROUP BY `field_keywords_value`");

$query = $qury->extend('PagerDefault'); 
        $result = $query
  ->extend('PagerDefault')
  ->limit(2)
  ->execute();

but it give me the error, PDOException: erro

And if i can use it in the following way:

$qury = db_query("SELECT `entity_id` , `field_keywords_value` , COUNT( DISTINCT ( `entity_id` )) total FROM `field_data_field_keywords` WHERE `bundle` = 'song'
                GROUP BY `field_keywords_value`");
$query = $qury->extend('PagerDefault'); 
$result = $query
  ->extend('PagerDefault')
  ->limit(2)
  ->execute();
echo "<pre>";
print_r($result);
die('tert');

Then my page not working and give me the blank page.

Any help will be appreciated.

2
  • You have a typo in your code: The first line uses $qury as variable, while the others use $query. You are also extending twice $query with the same class, when doing it once is enough. – kiamlaluno Nov 2 '12 at 9:29
  • May you report the exact error you are given? – kiamlaluno Nov 2 '12 at 9:29
4

First check which Drupal version you are using

if using Drupal 7 the use see below given example

    <?php
$result = db_select('table_name', 'table_alias')
    ->fields('table_alias',array('column_name1', 'column_name2', 'column_name1', .....))//SELECT the fields from user
    ->condition('type', 'article')
    ->extend('PagerDefault')
    ->limit(10)  // Set the limit per records
    ->orderBy('created_date', 'DESC')//ORDER BY created or 
    ->execute()
    ->fetchAssoc();
?>

See more example at

http://api.drupal.org/api/drupal/includes!database!database.inc/function/db_select/7

1

Check examples module how pager is given in this.

You are using select in select query. So its giving exception error.

For running SQL queries as it is, use db_query(). ex:- db_query("select emmpno from emp") For running db_select() we need to follow Drupal standards. ex:- $query=db_select('emp','e'); $result=$query->fields()->execute();

No select in a select.

4
  • Thanks for your reply. But i am new in Drupal and i dont know how i can use this query according to D7 rule. Can you please help me to resolve this issue. thank in advance – user1773435 Nov 1 '12 at 10:55
  • Try this code $qeury = db_query("SELECT entity_id , field_keywords_value , COUNT( DISTINCT ( entity_id )) total FROM 'field_data_field_keywords' WHERE 'bundle` = ':dub'",array(':bun'='song') GROUP BY n.field_keywords_value"); $qeury->extend('PagerDefault'); $result = $query ->limit(2) ->execute(); echo "<pre>"; print_r($result); die('tert'); foreach($result as $row) { //Your code for each row } – Dharmender Nov 2 '12 at 4:33
  • Thanks for your reply. but yesterday i have resolved the porblem by my self like: $query = db_select('field_data_field_keywords')->extend('PagerDefault'); $query->fields('field_data_field_keywords', array('field_keywords_value', 'entity_id')); $query->condition('bundle', 'song'); $query->condition('field_keywords_value', arg(2)); $query->distinct(entity_id); $result = $query ->limit(1) ->execute() ->fetchAll(); hope this is help ful for someone who is new in D7 thanks – user1773435 Nov 2 '12 at 4:37
  • 1
    May you expand your answer? Answers that are merely a link are not considered answers, on Drupal Answers. – kiamlaluno Nov 2 '12 at 9:32
1

An example Drupal 7 query with joins, conditions, limit and pagination:

$query = db_select('table_name1', 't1');
$query->join('table_name2', 't2', 't.id = t2.tid');
$query
->fields('t1', array('column1', 'column2', 'column3'))
->fields('t2', array('column1'))
->condition(db_or()->condition('t1.column2', '%' . db_like($match) . '%', 'LIKE')
                              ->condition('t2.column1', 'string', '=')
);

if($user->uid > 0) { $query->condition('t2.column2', '1', '='); } // only for logged in user

$query->groupBy('t1.id');
$search_results = $query->extend('PagerDefault')->limit(5)->execute(); // set pagination
0

I have resolved the problem bye my self

 $query = db_select('field_data_field_keywords')->extend('PagerDefault'); 
 $query->fields('field_data_field_keywords', array('field_keywords_value', 'entity_id'));    
 $query->condition('bundle', 'song');  $query->condition('field_keywords_value', arg(2)); 
 $query->distinct('entity_id');
 $result = $query ->limit(1)->execute()->fetchAll();

hope this is helpful for someone who is new in D7.

0

// $list is the list of items you are paging through

$list = array ('Cat','Dog','Mouse','Horse','Pony','Winged mongoose'); $per_page = 2;

// Initialise the pager

$current_page = pager_default_initialize(count($list), $per_page);

// Split your list into page sized chunks

$chunks = array_chunk($list,$per_page, TRUE);

// Show the pager

print theme('pager', array('quantity',count($list)));

// Show the appropriate items from the list

print theme('item_list', array( 'items' => $chunks[$current_page], ));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.