Home › Drupal Collection

Tablesort_sql Conversion for D7

Mon, 09/21/2009 - 18:45 — admin

Tablesort_sql was a nifty feature that allowed you to create a table from the database and be able to mark the headers so that you could sort the results. In Drupal 7 this feature has been replaced with what the Data Base The Next Generation (DBTNG) refers to as an "Extender." This page is to help convert from the old style to the new.

In D6:

<?php
  $header = array(
    array('data' => 'Title', 'field' => 'title', 'sort' => 'ASC'),
    array('data' => 'Node ID', 'field' => 'nid'),
    array('data' => 'Type', 'field' => 'type'),
    array('data' => 'Created', 'field' => 'created'),
    array('data' => 'Published', 'field' => 'status'),
    array('data' => 'Sticky', 'field' => 'sticky'),
    array('data' => 'Promoted', 'field' => 'promote'),
    );
  $rows = array();
  $noyes = array('No', 'Yes');

  $query = "SELECT * FROM {node}";
  $query .= tablesort_sql($header);
  $results = db_query($query);

  while ($node = db_fetch_object($results)) {
    $rows[] = array(
      l($node->title, 'node/'. $node->nid .'/edit'),
      $node->nid,
      $node->type,
      format_date($node->created),
      $noyes[$node->status],
      $noyes[$node->sticky],
      $noyes[$node->promote],
      );
   }
  return theme('table', $header, $rows);
?>

In D7, this becomes:

<?php
  $header = array(
    array('data' => 'Title', 'field' => 'title', 'sort' => 'ASC'),
    array('data' => 'Node ID', 'field' => 'nid'),
    array('data' => 'Type', 'field' => 'type'),
    array('data' => 'Created', 'field' => 'created'),
    array('data' => 'Published', 'field' => 'status'),
    array('data' => 'Sticky', 'field' => 'sticky'),
    array('data' => 'Promoted', 'field' => 'promote'),
    );
  $rows = array();
  $noyes = array('No', 'Yes');

  $select = db_select('node', 'n')->extend('TableSort');
  $results = $select->fields('n', array('nid', 'title', 'type', 'created', 'status', 'sticky', 'promote'))->orderByHeader($header)->execute();

  foreach ($results as $node) {
    $rows[] = array(
      l($node->title, 'node/'. $node->nid .'/edit'),
      $node->nid,
      $node->type,
      format_date($node->created),
      $noyes[$node->status],
      $noyes[$node->sticky],
      $noyes[$node->promote],
      );
   }
  return theme('table', $header, $rows);
?>

Most of the change is involved in the query itself. There is some additional duplication of fields, which is surprising with the recent emphasis on eliminating that kind of thing (we could spin through the header array and pick up the field list from that).