Creating Sortable, tables with pagination in Drupal 7

With Drupal 7 creating tables are very easy and that too with very cool features like pagination, sort functionality and sticky. These are some of the powerful tools available with Drupal 7 which reduces a hell lot of development time. So, let's see how it works.

Today we will talk about one of the cool feature about Drupal 7 - creating tables from a query and adding cool features like "Sort", "Sticky" and "Pagination". Yes, these are very common ways of Displaying complex data and with Drupal 7 it is very easy to set up such things.

In this tutorial, I will run a simple query to the node table and loop through the result and create a table which will display a table with nodes titles and the node type. It will have the option to sort by the columns and also pagination.

So, here is the key points to the tutorial:

  1. Create a page using hook_menu
  2. Write a query - a select query to the node table with the node title and content type as fields
  3. Handling the query so that we can change it as per the sort and order
  4. Using the theme_table drupal function to create a table

Here is the hook menu for the page that we have created.

/**
 * Implements hook_menu()
 */
function mytable_menu() {
  $items = array();
  $items['tablepage'] = array(
    'title' => t('This is a demo page to show Drupal 7 table API'),
    'description' => t('As the title suggest, This is a demo page to show Drupal 7 table API'),
    'page callback' => 'mytable_table_data',
    'access arguments' => array("access content"),
    'type' => MENU_CALLBACK
  );

  return $items;  
}

And then the function as the page call back. I have added comments at each step to give you an idea what the line of code is doing.

function mytable_table_data() {
  // Initialising output
  $output = '';

  // Table header
  $header = array(
    array('data' => 'Title', 'field' => 'title'),
    array('data' => 'Type', 'field' => 'type'),
  );

  // Setting the sort conditions
  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 'Title':
        $order = 'title';
        break;
      case 'Type':
        $order = 'type';
        break;
      default:
        $order = 'title';
    }
  }
  else {
    $sort = 'ASC';
    $order = 'title';
  }

  // Query object
  $query = db_select("node", "n");
  
  // Adding fields
  $query->fields('n',array('title','type'));

  // Set order by
  $query->orderBy($order, $sort);

  // Pagination
  $query = $query->extend('TableSort')->extend('PagerDefault')->limit(2);

  // Executing query
  $result = $query->execute();
  
  // Looping for filling the table rows
  while($data = $result->fetchObject()) {
    // Adding the rows
    $rows[] = array($data->title, $data->type);
  }

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

  // Returning the output
  return $output;
}
Creating Sortable, tables with pagination in Drupal 7

Creating Sortable, tables with pagination in Drupal 7

You can download the code from this url on Github: https://github.com/amitavroy/Justread-codes/tree/master/mytable

comments powered by Disqus