Drupal 7 - Creating Drupal style tables with paging, sorting and filter.

Recently I came across a requirement where I went through all the features in Drupal HTML tables. At times I was startled to know the efficacy of the tables in Drupal 7. I would like to share the the same road trip I went through in Drupal 7. Let's begin with a step-by-step process to create tables inD7

 
1. Create a path using hook_menu:
 
  1. /**
  2.  * Implements hook_menu().
  3.  */
  4. function knackquiz_quiz_menu() {
  5.   $items = array();
  6.   $items['quiz/payments'] = array(
  7.     'title' => 'Paid Quiz list',
  8.     'page callback' => 'drupal_get_form',
  9.     'page arguments' => array('_quiz_payments_list_form'),
  10.     'access arguments' => array("access content"),
  11.     'type' => MENU_CALLBACK
  12.   );
  13.   return $items;
  14. }
 
In the callback function do the following steps.,
 
2. The table headers has the title of each column in the table:
 
  1. $header = array(
  2.     array('data' => t('Order Number'),'field' => 'order_number'),
  3.     array('data' => t('User Name'),'field' => 'name'),
  4.     array('data' => t('Quiz Title'),'field' => 'title'),
  5.     array('data' => t('Status'),'field' => 'status'),
  6.     array('data' => t('Quiz created time'),'field' => 'created'),
  7.     array('data' => t('Bought time'),'field' => 'created'),
  8.   );
 
Note that by default, we specify an ascending sort on the title field. This means that the rendered table will be sorted in ascending order on the title field by default. However the user can subsequently sort on any column on the rendered table by clicking its header.
 
If you do not want a particular column to be sortable, do not specify the 'field' parameter for its header.
 
3.  Next create your Sql query to be executed which returns the sorted and paged results from the database.
 
  1.  $query = db_select('commerce_order', 'co');
  2.     $query->leftJoin('commerce_line_item', 'li', 'li.order_id = co.order_id');
  3.     $query->leftJoin('field_data_commerce_product', 'prod', 'li.line_item_id = prod.entity_id');
  4.     $query->leftJoin('commerce_product', 'p', 'prod.commerce_product_product_id = p.product_id');
  5.     $query->leftJoin('field_data_field_product_quiz_id', 'p_nid', 'p.product_id = p_nid.entity_id');
  6.     $query->leftJoin('node', 'n', 'p_nid.field_product_quiz_id_nid = n.nid');
  7.     $query->leftJoin('users', 'u', 'co.uid = u.uid');
  8.  
  9.     $result = $query
  10.     ->fields('co', array('order_id', 'order_number', 'status','created'))
  11.     ->fields('p', array('title'))
  12.     ->fields('n',array('nid','created'))
  13.     ->fields('u', array('name','uid'))
  14.     ->orderBy($order, $sort)
  15.     ->extend('TableSort')->extend('PagerDefault')->limit(25)
  16.     ->execute();
 
4. Next build filter part for the form:
 
  1. $form = array();
  2.  
  3. $form['filter'] = array(
  4.   '#type' => 'fieldset',
  5.   '#collapsible' => TRUE,
  6.   '#collapsed' => TRUE,
  7.   '#title' => t('Filter option')
  8. );
  9. $form['filter']['filter_user'] = array(
  10.   '#type' => 'textfield',
  11.   '#title' => t('Username'),
  12.   '#size' => 15,
  13. );
  14. $form['filter']['filter_quiz'] = array(
  15.   '#type' => 'textfield',
  16.   '#title' => t('Quiz title'),
  17.   '#size' => 15,
  18. );
  19. $form['filter']['filter_group'] = array(
  20.   '#type' => 'textfield',
  21.   '#title' => t('Group name'),
  22.   '#size' => 15,
  23. );
  24. $form['filter']['submit'] = array(
  25.   '#type' => 'submit',
  26.   '#value' => t('Filter'),
  27. );
 
5. Next Apply filter conditions
 
  1. if (isset($form_state['filters']['user'])) {
  2.   $query->condition('u.name', '%' . db_like($form_state['filters']['user']) . '%', 'LIKE');
  3. }
  4. if (isset($form_state['filters']['quiz'])) {
  5.   $query->condition('p.title', '%' . db_like($form_state['filters']['quiz']) . '%', 'LIKE');
  6. }
 
6. The next step is executing the query and collecting the rows from the resultset. This is a regular loop and requires no explanation (I hope):
 
  1. $rows = array();
  2. // Looping for filling the table rows
  3.   foreach ($result as $ord) {
  4.     // Fill the table rows
  5.     $rows[] = array(
  6.      l($ord->order_number, 'admin/commerce/orders/'. $ord->order_id .'/edit'),
  7.      l($ord->name, 'user/'. $ord->uid .'/edit'),
  8.      l($ord->title, 'node/'. $ord->nid.'/edit'),
  9.       $ord->status,
  10.       format_date($ord->created,'custom','d-M-Y',date_default_timezone()) ,
  11.       format_date($ord->n_created,'custom','d-M-Y',date_default_timezone()) ,
  12.     );
  13.   }
 
7 .Then, we create a table from the headers and the result rows with a simple call to $form['table']
 
  1. $form['table'] = array(
  2.   '#theme' => 'table',
  3.   '#header' => $header,
  4.   '#rows' => $rows,
  5.   '#empty' => t('Table has no row!')
  6. );
 
8 . The final step is to append a pager to the table.
 
  1. $form['pager'] = array('#markup' => theme('pager'));
 
 
You can also make the table sortable by clicking its header. Just do the following modifications to your current code.
 
  1. // Check if there is sorting request
  2. if(isset($_GET['sort']) && isset($_GET['order'])){
  3.   // Sort it Ascending or Descending?
  4.   if($_GET['sort'] == 'asc')
  5.     $sort = 'ASC';
  6.   else
  7.     $sort = 'DESC';
  8.    // Which column will be sorted
  9.    switch($_GET['order']){
  10.      case 'Order Number':
  11.        $order = 'order_number';
  12.        break;
  13.      case 'User ID':
  14.        $order = 'name';
  15.        break;
  16.      case 'Status':
  17.        $order = 'status';
  18.        break;
  19.      case 'Product Name':
  20.        $order = 'title';
  21.        break;
  22.      case 'Quiz created time':
  23.        $order = 'created';
  24.        break;
  25.      case 'Bought time':
  26.        $order = 'created';
  27.        break;
  28.      default:
  29.        $order = 'order_id';
  30.    }
  31.  }
  32.  else {
  33.    // Default sort
  34.    $sort = 'ASC';
  35.    $order = ' order_id';
  36.  }
 
Then add this line after the $query->fields() command.
 
  1. // Set order by
  2. $query->orderBy($order, $sort);
 
This submit handler will make the filter work to get the desired result
 
  1. function _quiz_payments_list_form_submit($form, &$form_state) {
  2.   $form_state['filters']['user'] = $form_state['values']['filter_user'];
  3.   $form_state['filters']['quiz'] = $form_state['values']['filter_quiz'];
  4.   $form_state['rebuild'] = TRUE;
  5. }
 
In case of any suggestions/ query please do post a comment below.

 

Comments

Permalink

when the form is rebuilt, $form_state['filters']['user'] from function _quiz_payments_list_form_submit($form, &$form_state)is not carried to the form function [function _quiz_payments_list_form($form, &$form_state)] so that
$form_state['filters']['user'] is not set in the filter condition query. Filtering fails. It failed for me. Tell me where I am wrong.

Permalink

I'm also running into the same issue as Jaisree. Any way to get user filter working with pager? Thanks.

Hi,

You have to add forme state to the argument of the call back function.
Example:

_quiz_payments_list_form($form, &$form_state){

}

In reply to by Rookie (not verified)

Permalink

I have the same problem. After submit the filter values loss.
I have the arguments:
function registrations_form($form, &$form_state) {

function registrations_form_submit($form, &$form_state) {

Permalink

late to the party, but you don't have to tinker with $_GET parameters to find the ordering factor.

$query->orderByHeader($header);

This is all you want.

Pager will not work this way, as pager expects to work wtih params populated to $_GET while form submit populates them to $_POST

So place a code to grab them from $_GET 

.....

$form['filter']['submit'] = array(

  '#type' => 'submit',

  '#value' => t('Filter'),

);

// HERE GRABBING FROM $_GET[] GOES

    foreach ($form['filters'] as $key => $value) {
        if(!empty($_GET[$key]) && (empty($_POST[$key]) && !isset($_POST[$key]))) {
            $form['filters'][$key]['#default_value'] = $_GET[$key];
            $form_state['filters'][$key] = $_GET[$key];
        }
    }

and populating into $_GET

$form['table'] = array(

  '#theme' => 'table',  '#header' => $header,  '#rows' => $rows,  '#empty' => t('Table has no row!'));

//HERE POPULATING INTO $_GET

    foreach($form_state['filters'] as $k => $v) {
        if(empty($v))
            continue;
        $_GET[$k] = $v;
    }

//old code below

$form['pager'] = array('#markup' => theme('pager'));

 

Permalink

Pagination is not working for me. please help

 

function manage_template_form( $form, &$form_state ){
    $header = array(
        array('data' => t('Template Name'),'field' => 'template_name'),
        array('data' => t('Language'),'field' => 'language'),
        array('data' => t('Action')),        
    );
    
    $query = db_select("gsm_campaign_template",'t')
    ->fields('t');
        
    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 'Template Name':
           $order = 'name';
           break;
         case 'Language':
           $order = 'language';
           break;             
         default:
           $order = 'name';
       }
    }else {
       // Default sort
       $sort = 'ASC';
       $order = 'name';
     }
    $query->orderBy($order, $sort);
    $query->extend('TableSort')->extend('PagerDefault')->limit(10);
    $result = $query->execute()->fetchAll();
  
    
    $rows = array();
    foreach($result as $template ){
        $rows[] = array(
                'template_name' => $template->name,
                'language' => $template->language,                
                'action' => l('View',"template/$template->id").' / '.l('Edit',"template/$template->id/edit").' / '.l('Delete',"template/$template->id/delete"),
            );
    }
    
    $form['table'] = array(
      '#theme' => 'table',
      '#header' => $header,
      '#rows' => $rows,
      '#empty' => t('No campaign found.'),
    
    );
    
    $form['pager'] = array('#markup' => theme('pager'));
  
    return $form;
}
 

Add new comment

The content of this field is kept private and will not be shown publicly.