By damienb on

Hi,

I'm new to drupal development, and am trying to understand where (which hook) is the best place to gather data from an external database for use throughout a page. The query to the database depends on the url of the page called (for example - www.site.com/foo/bar/etc: "SELECT FROM table WHERE $a=foo && $b=$bar && $c=$etc").

Initially, I thought it would be best to use it within a menu callback function, since the url values are readily available as parameters there.

However, this seemed to cause a problem if I want to use one of the results of that query in the title of the page (through the title callback function). The results of the query in the menu callback function weren't available to the title callback (using a global variable) -though that may have been a result of human error on my part. Also- my understanding of a menu callback function is that it should be used for generating specific content for a page, not setting up variables for other functions to use..

Now, I'm wondering if the query would be better placed at hook_init(). I would use $_SERVER['REQUEST_URI'] to parse the url and enter the values into my query, returning data that can be used in other functions on the page. This seems redundant, however, because hook_menu (using wildcard placeholders) serves a similar function of dissecting the url...

Before I waste a lot of time here, I thought I would solicit some expert advice on how to structure the placement of a query to an external database. The database is quite complex, so I'm not quite ready to incorporate it into the Drupal node system just yet...

⋅ Categories: Drupal 6.x

Comments

onglipo’s picture

onglipo commented

Get this great piece of code from here and did a messy tie in after trying all kinds of drupal form based approaches...works; but I am sure there is a better way! Just create a new story with the following php content.

Name: 

$name = $_GET["fname"];
if (!$name) {
  echo 'You can search for a part of your name. Do try different spellings!';
} else {
  echo rshow_table($name, 10);
}
function rshow_table( $name, $rows_per_page = 20) {
  $table = 'VoterList'; 
  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.
  $name = "'%".strtoupper($name)."%'";
//$select = "SELECT * FROM {$table}";
  $select = "SELECT * FROM {$table} WHERE  upper(Name) LIKE $name";
  // 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;
}
damienb’s picture

damienb commented

-y an external database?"

Thanks, but that doesn't really address my question.

Essentially, I'm asking if it is wise to use a menu callback function to query an external database using variables collected from the url. The data collected wouldn't necessarily be placed in the $content variable (returned by the menu callback function).

I would like to create some additional template variables in which this data would be inserted, so the data collected at hook_menu would be need to shared with other functions...

Is that a typical/ wise use of the menu callback function?

As I mentioned in the post, the other option would be to query the database in a hook earlier in the Drupal process - like hook_init(). Again, i'm new to Drupal, and I'm sure I could work this out on my own... But before I go through too many trial and error episodes, I thought i would pose the question to the community: "Is it a common practice to us hook_init() or hook_menu() to query an external database?"

damienb’s picture

damienb commented

hook_init() is the better place for a db query where the info would populate different parts of the page..

This is what I learned about the menu callback functions in hook_menu():

The title callback function seems to be called before the page callback- so info from the title callback function can be shared with the page callback function, but not vice versa.

However, submenu page callback functions cannot access variables created in the parent menu title callback...

Thus, it seems more logical to put a db query in hook_init()...