blog-banner

Drupal 7 - Creating Drupal Style Tables with Paging, Sorting and Filter

  • Drupal 7
  • HOOK_MENU
  • Pager

Drupal 7 Theme Table

 

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 same road trip I went through in Drupal 7. Let's begin with a step-by-step process to create tables in D7.

 
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 have 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/queries please do post a comment below.

 

Get awesome tech content in your inbox