Drupal 6: Taking a SQL statement and generating a table from the results

Functionality that I regularly create in applications is the ability to take a SQL statement and return a table of results for reports, debugging, etc. Here’s a helper function I created to implement this functionality. I purposely left off pagination, sortable columns, and other features to focus on how the Drupal theme_table() function operates. This helper function will use the column names from the SQL as the table column headers, so make sure they are properly formatted.

<?php

// first, I defined this function in my module:
function _MYMODULE_sql_to_table($sql) {

  $html = "";

  // execute sql
  $resource = db_query($sql);

  // fetch database results in an array
  $results = array();
  while ($row = db_fetch_array($resource)) $results[] = $row;

  // ensure results exist
  if (!count($results)) {
    $html .= "Sorry, no results could be found.";
    return $html;
  }

  // create an array to contain all table rows
  $rows = array();

  // get a list of column headers
  $columnNames = array_keys($results[0]);

  // loop through results and create table rows
  foreach ($results as $key => $data) {

    // create row data
    $row = array();

    // loop through column names
    foreach ($columnNames as $c) {
      $row[] = array(
        'data' => $data[$c],
        'class' => strtolower(str_replace(' ', '-', $c)),
      );
    }

    // add row to rows array
    $rows[] = $row;

  }

  // loop through column names and create headers
  $header = array();
  foreach ($columnNames as $c) {
    $header[] = array(
      'data' => $c,
      'class' => strtolower(str_replace(' ', '-', $c)),
    );
  }

  // generate table html
  $html .= theme('table', $header, $rows);

  return $html;

}

// then you can call it in your code...
function _MYMODULE_some_page_callback() {

  $html = "";

  $sql = "select * from {node}";

  $html .= _MYMODULE_sql_to_table($sql);

  return $html;
}

?>