Tabular data with Pager, Sorting and Filters

There lots of solution available in Drupal to create tabular data with form but the best solution is to use "theme() function" for this. When I was a beginner in Drupal I have created a table using "form API" in which I have included "for loop" and "field type" etc. to achieve this task. That time I don't care of coding standard but my main aim was to finish the task by hook or by crook. Recently I was working on a project where my client requirement was very similar to views where they want "Tabular data with search and pager" for the custom table. I can do it very easily with a view but they want a custom module for this so I have created a custom module to achieve this task. The main thing of this tabular data is the auto-complete box for search keywords. so when the user starts typing keywords in the search box then an auto-populated list of keywords appear. Here I am sharing my experience for this task: 

First, create a custom module so go to your project directory->sites->all->modules here you have to create 2 folders named "Contrib" and "Custom".

Contrib: Where you put all contributed module in it.

Custom: Where you create all custom module in it.

In custom module create a folder called mytestmodule and then "mytestmodule.info, mytestmodule.module" files in it. Now open mytestmodule.info file and put this code in it.

name = Custom search form with tabular data
description = Custom form with tabular data, paging, sorting and auto-complete search box.
core = 7.x
package = Custom
files[] = mytestmodule.module

Now Open mytestmodule.module file and follow these steps

  1. Create path where you will call your form with tabular data.
/** * Implements hook_menu(). */

function mytestmodule_menu() {

  $items = array();
  $items['mytestmodule'] = array( // path where form will call
    'title' => t('My test module'),
    'page callback' => 'drupal_get_form',
    'page arguments' => array('_mytestmodule_list_form'),
    'access callback' => TRUE, );

  $items['mytestmodule/autocomplete'] = array( // path for auto-complete textbox
    'title' => t('Autocomplete path'),
    'page callback' => '_mytestmodule_autocomplete',
    'access arguments' => array('use autocomplete'), //or whatever permission makes sense
    'type' => MENU_CALLBACK );

  return $items;
}

2. Create your form with tabular data.

/** * Callback function return form with themed table and searchbox */

function _mytestmodule_list_form($form, &$form_state) {
  $header = array(
    array('data' => t('Sno'),'field' => 'sno'),
    array('data' => t('Title'),'field' => 'title'),
    array('data' => t('Unique Key'),'field' => 'unique_key'),
    array('data' => t('description'),'field' => 'description'),
  );

  $query = db_select('mytestmodule', 'co');
  $query->fields('co', array());
  if (isset($form_state['filters']['title'])) {
    $query->condition('title', '%' . db_like($form_state['filters']['title']) . '%', 'LIKE');
  }

  $query->extend('TableSort')->extend('PagerDefault')->limit(22);
  $result = $query->execute();

  $form = array();
  $form['title'] = array(
    '#type' => 'textfield',
    '#title' => t('Title'),
    '#autocomplete_path' => 'mytestmodule/autocomplete',
  );

  $form['submit'] = array(
    '#type' => 'submit',
    '#value' => t('Filter'),
  );

  $rows = array();
  // Looping for filling the table rows

  foreach ($result as $ord) { // Fill the table rows
    $rows[] = array(
    $ord->sno,
    $ord->title,
    $ord->unique_key,
    $ord->description,
    );
  }

  //show data in tabular form

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

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

  return $form;

}

3. Create submit hander for callback form function

/** * Submit handler for Callback function */

function _mytestmodule_list_form_submit($form, &$form_state) {
  $form_state['filters']['title'] = $form_state['values']['title'];
  $form_state['rebuild'] = TRUE;
}

4. Autocomplete helper function

/** * Autocomplete helper */

function _mytestmodule_autocomplete($string) {
  $matches = array();
  //prepare query for autocomplete list of data

  $result = db_select('mytestmodule', 'c')
  ->fields('c', array())
  ->condition('title', '%' . db_like($string) . '%', 'LIKE')
  ->execute();

  // save the query to matches

  foreach ($result as $row) {
    $matches[$row->title] = check_plain($row->title);
  }

  // Return the result to the form in json
  drupal_json_output($matches);
}

Tags