Drupal 6: Creating a table with sortable columns and pagination from custom SQL

In this tutorial I’ll show you how you can create a table with re-sortable column headers and pagination from custom SQL. I strongly recommend you attempt to implement this functionality using the Views module first :)

<?php

/**
 * Implements hook_menu()
 */
function helper_menu() {

  $items = array();

  $items['pager-test'] = array(
    'title' => t('Pager Test'),
    'description' => t('Pager Test'),
    'page callback' => 'helper_page_callback_pager_test',
    'access arguments' => array('access content'),
    'type' => MENU_CALLBACK,
  );

  return $items;

}

/**
 * Implements page callback for pager-test url
 */
function helper_page_callback_pager_test() {

  // define number of results per page
  $number_results = 10;

  // define a list of columns to select
  $columns = array(
    'nid' => t('ID'),
    'type' => t('Type'),
    'title' => t('Title'),
  );

  // define sql to fetch results
  $sql = "select %s from {node} where status = 1";

  // define sql to fetch number of results
  $sql_count = "select count(*) from {node} where status = 1";

  // create resort header from column list
  $header = array();
  foreach ($columns as $key => $value) {
    $header[] = array(
      'data' => $value,
      'field' => $key,
    );
  }

  // add tablesort to SQL
  $sql .= tablesort_sql($header);

  // execute query
  $resource = pager_query($sql, $number_results, 0, $sql_count, implode(', ', array_keys($columns)));

  // fetch results
  $results = array();
  while ($row = db_fetch_array($resource)) {
    $results[] = $row;
  }

  // define variable to contain page callback output
  $output = "";

  // create table html
  $output .= theme('table', $header, $results);

  // create pager html
  $output .= theme('pager', NULL, $number_results, 0);

  return $output;

}
?>

The above code results in the following table: