Home

Drupal show database table display

Submitted by newaction on Thu, 2012-05-17 13:44

I found a snippet
that displayed all the data in a database table and I liked it, but it
was "dirty" code and needed to be fixed up. Meanwhile there was another post about creating a sortable table of data. I thought it might be nice to merge these two ideas.

I really like what I came up with. As a matter of fact, I liked it so much I added it to the Site Documentation module, so anyone can have it without doing any coding or even snippet pasting.

This code takes a table name from the function call and creates a
nice little displayable table of the data in that table. The columns are
labeled with the column name and displayed table is sortable by
clicking in the header.

I created and tested this on Drupal 5.5 and MySql 5.0, but I don't
see anything here that is really release-specific or database-dependent.

<?php
  echo show_table('blocks', 50);

function

show_table($table = NULL, $rows_per_page = 20) {
  if (!$table || !db_table_exists($table)) {
      drupal_set_message(t('You must supply a valid database table name.'), 'error');
        drupal_access_denied();
    }

 

// We get the first (or only) part of the Primary key to be added to the sort sequence.
  $result = db_query("SHOW INDEX FROM {$table}");
  $x = db_fetch_array($result);
  if ($x === FALSE) {
    drupal_set_message(t("The '@table' table has no index defined. This is probably normal.", array('@table' => $table)), 'notice');
    $first_key = NULL;
  }
  else {
    $first_key = $x['Column_name'];
  }

 

drupal_set_title(t('@table Table Contents', array('@table' => ucwords($table))));
  $output = '<p>'. t('Click on a column title to sort by that column.') .'</p><br/>';
  $rows = array();

 

// Now we get the column names from the table and build the header.
  $header = array();
  $result = db_query("SHOW COLUMNS FROM {$table}");

  while (

$col_desc = db_fetch_array($result)) {
    $header[] = array(
      'data' => ucwords(str_replace('_', ' ', $col_desc['Field'])),
      'field' => '`'. $col_desc['Field'] .'`',
      );
  }
 
  // Get the data rows from the table.
  $select = "SELECT * FROM {$table}";
  // Set it up so that the user can sort on any column, but the primary key will always be the last value to sort on.
  $select .= tablesort_sql($header) . ($first_key ? (', '. $first_key .' ASC') : NULL);
  // Do the query so that we can page the data.
  $result = pager_query($select, $rows_per_page);

  while (

$row = db_fetch_array($result)) {
    $line = array();
    foreach ($row as $key => $value) {
      // We use check_markup to apply our filters.
      $line[] = check_markup($value, FILTER_FORMAT_DEFAULT,TRUE);
    }
    $rows[] = $line;
  }

 

// Build the displayable table.
  $output .= theme('table', $header, $rows);
  $output .= theme('pager', $rows_per_page);
  return $output;
}
?>

You may put this into a PHP format page or include it into a custom module.

Ref: http://drupal.org/node/205347