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: