Would you like to be notified for every new articles? Please click HERE to subscribe for newsletter.

Working With Table And Pagination In Drupal 7

  • Posted on: 14 May 2011
  • By: admin

When developing a module for Drupal 7 you can display the query result into a table and then display it along with the pagination. You can do it relatively easy with functions provided by Drupal. Look at the code below to see how to create a table with pagination in Drupal 7.

The example of table with paging for Drupal 8 is available at http://w3shaman.com/article/drupal-8-sortable-table-pager.
Sample Module

This is the sample code of a Drupal module that implements table with pagination.


<?php
/**
 * A Drupal 7 module for displaying table with pagination
 */
 
/**
 * Implementation of hook_menu()
 */
function testing_menu(){
  
$items=array();

  

$items['testing/table'] = array(
    
'title' => 'Testing Table',
    
'page callback' => '_testing_table',
    
'access arguments' => array("access content"),
    
'type' => MENU_CALLBACK
  
);

  return 

$items;
}

/**
 * Function for displaying table
 */
function _testing_table(){
  
$output "";

  

// Select table
  
$query db_select("registry""r");
  
// Select fields
  
$query->fields("r", array("name""type""filename""module""weight"));
  
// For pagination
  
$query $query->extend('TableSort')->extend('PagerDefault')->limit(50);

  

// Execute query
  
$result $query->execute();

  

// Prepare table header
  
$header = array('Name''Type''Filename''Module''Weight');

  

$rows = array();

  

// Looping for filling the table rows
  
while($data $result->fetchObject()){
    
// Fill the table rows
    
$rows[] = array(
      
$data->name,
      
$data->type,
      
$data->filename,
      
$data->module,
      
$data->weight
    
);
  }

  

// Output of table with the paging
  
$output theme_table(
    array(
      
"header" => $header,
      
"rows" => $rows,
      
"attributes" => array(),
      
"sticky" => true// Table header will be sticky
      
"caption" => "",
      
"colgroups" => array(),
      
"empty" => t("Table has no row!"// The message to be displayed if table is empty
    
)
  ).
theme("pager");

  return 

$output;
}
?>
      
Make It Sortable

You can also make the table to be sortable if you click its header. Just do the following modifications to your current code.

  • Add this code to the beginning of _testing_table() function.
    <?php
    if(isset($_GET['sort']) && isset($_GET['order'])){
      
    // Sort it Ascending or Descending?
      
    if($_GET['sort'] == 'asc')
        
    $sort 'ASC';
      else
        
    $sort 'DESC';

      

    // Which column will be sorted
      
    switch($_GET['order']){
        case 
    'Name':
          
    $order 'name';
          break;
        case 
    'Type':
          
    $order 'type';
          break;
        case 
    'Filename':
          
    $order 'filename';
          break;
        case 
    'Module':
          
    $order 'module';
          break;
        default:
          
    $order 'name';
      }
    }
    else{
      
    // Default sort
      
    $sort 'ASC';
      
    $order ' name';
    }
              
    ?>
  • Then add these line after the $query->fields() command.
    <?php
    // Set order by
    $query->orderBy($order$sort);
              
    ?>
  • For the last, prepare the table header like this.
    <?php
    // Prepare table header
    $header = array(
      array(
        
    "data" => t('Name'),
        
    "field" => "name"
      
    ),
      array(
        
    "data" => t('Type'),
        
    "field" => "type"
      
    ),
      array(
        
    "data" => t('Filename'),
        
    "field" => "filename"
      
    ),
      array(
        
    "data" => t('Module'),
        
    "field" => "module"
      
    ),
      array(
        
    "data" => t('Weight'),
        
    "field" => "weight"
      
    )
    );
              
    ?>
  • And, this is the complete modified function.
    <?php
    /**
     * Function for displaying table
     */
    function _testing_table(){
      
    // Check if there is sorting request
      
    if(isset($_GET['sort']) && isset($_GET['order'])){
        
    // Sort it Ascending or Descending?
        
    if($_GET['sort'] == 'asc')
          
    $sort 'ASC';
        else
          
    $sort 'DESC';

        

    // Which column will be sorted
        
    switch($_GET['order']){
          case 
    'Name':
            
    $order 'name';
            break;
          case 
    'Type':
            
    $order 'type';
            break;
          case 
    'Filename':
            
    $order 'filename';
            break;
          case 
    'Module':
            
    $order 'module';
            break;
          default:
            
    $order 'name';
        }
      }
      else{
        
    // Default sort
        
    $sort 'ASC';
        
    $order ' name';
      }

      

    $output "";

      

    // Select table
      
    $query db_select("registry""r");
      
    // Selected fields
      
    $query->fields("r", array("name""type""filename""module""weight"));
      
    // Set order by
      
    $query->orderBy($order$sort);
      
    // Pagination
      
    $query $query->extend('TableSort')->extend('PagerDefault')->limit(50);

      

    // Execute query
      
    $result $query->execute();

      

    // Prepare table header
      
    $header = array(
        array(
          
    "data" => t('Name'),
          
    "field" => "name"
        
    ),
        array(
          
    "data" => t('Type'),
          
    "field" => "type"
        
    ),
        array(
          
    "data" => t('Filename'),
          
    "field" => "filename"
        
    ),
        array(
          
    "data" => t('Module'),
          
    "field" => "module"
        
    ),
        array(
          
    "data" => t('Weight'),
          
    "field" => "weight"
        
    )
      );

      

    $rows = array();

      

    // Looping for filling the table rows
      
    while($data $result->fetchObject()){
        
    // Fill the table rows
        
    $rows[] = array(
          
    $data->name,
          
    $data->type,
          
    $data->filename,
          
    $data->module,
          
    $data->weight
        
    );
      }

      

    // Output of table with the paging
      
    $output theme_table(
        array(
          
    "header" => $header,
          
    "rows" => $rows,
          
    "attributes" => array("width"=>"100%"),
          
    "sticky" => true// Table header will be sticky
          
    "caption" => "",
          
    "colgroups" => array(),
          
    "empty" => t("Table has no row!"// The message to be displayed if table is empty
        
    )
      ).
    theme("pager");

      return 

    $output;
    }
              
    ?>

    Comments

    Thanks a lot

    Thanks,
    Very useful content.

    terimakasih banyak karena anda sangat membantu sekali

    thank you so much.

    Please note, this will not work if you use chaining on the extend() method.
    Meaning this:
    $query->extend('TableSort')->extend('PagerDefault')->limit(50);

    will not work. But this:

    $query = $query->extend('TableSort')->extend('PagerDefault')->limit(50);

    Does work.

    Josh, is it true you cannot simply chain ->extend on the single query? Why is that?
    thanks.

    This works for me, Thanks Josh for saving my time.

    It is very useful content..Thank you so much..

    Why do I get this message? Can you tell me what's wrong? Thank You!
    Fatal error: Cannot redeclare testing_menu() (previously declared in C:\wamp\www\drupal-7.12\modules\php\php.module(80) : eval()'d code:9) in C:\wamp\www\drupal-7.12\modules\php\php.module(80) : eval()'d code on line 20

    It seems like the module name in this example conflicted with one in Drupal core. You just need to change the module name in this example.

    Is there a way to add show-all link next to pager?
    Thanks

    Yes, I think it's possible. But you have to write additional code.

    So perfect tip,
    Thank you very much !

    Nice Article

    thank you!

    Just a comment:
    i prefer to use $output = theme("table", ....
    instead of use $output = theme_table( ....

    excellent and clear

    how to use above code when tables are created from WYSIWYG editor

    In your example, orderby cause should be replace with orderByHeader so code would be....

    // Set order by
    $query->orderByHeader($header)

    and in that case $header should be declare before this statement.

    If we implement that than we can remove below code..

    // Check if there is sorting request
    if(isset($_GET['sort']) && isset($_GET['order'])){
    // Sort it Ascending or Descending?
    if($_GET['sort'] == 'asc')
    $sort = 'ASC';
    else
    $sort = 'DESC';

    // Which column will be sorted
    switch($_GET['order']){
    case 'Name':
    $order = 'name';
    break;
    case 'Type':
    $order = 'type';
    break;
    case 'Filename':
    $order = 'filename';
    break;
    case 'Module':
    $order = 'module';
    break;
    default:
    $order = 'name';
    }
    }
    else{
    // Default sort
    $sort = 'ASC';
    $order = ' name';
    }

    That's save a lot of my time.
    Thanks Dude.

    Below code:

    }
    else{
    // Default sort
    $sort = 'ASC';
    $order = ' name';
    }

    This OK if want to ascending order for initial condition but for descending one, must be:

    }
    else{
    // Default sort
    $sort = 'DESC';
    $order = ' name';
    $_GET['sort'] = $sort;
    $_GET['order'] = $order;
    }

    i wanna to know how to display my query results as a view

    Thanks alot it's very helpfull for me.......................

    very helpul tutorial..............

    When I have tried to implement the script in my PHP application it was showing many errors. It would be grateful if you could share with us the ways on how to rectify those errors in this site.

    as

    Add new comment

    Limited HTML

    • Allowed HTML tags: <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li>
    • Lines and paragraphs break automatically.

    Plain text

    • No HTML tags allowed.
    • Web page addresses and e-mail addresses turn into links automatically.
    • Lines and paragraphs break automatically.
    By submitting this form, you accept the Mollom privacy policy.