I need to be able to generate a link that will open up a new window and show the contents of a table in my database. Any ideas? I was able to do what I want using the dba module but when using that module people are able to get to the page listing all the database tables. I only want them to be able to access the specific table that they are linked to.

Comments

crick’s picture

if you want to see what the site to help better understand what i'm saying. go to www.crickster.com and look up Google's stock symbol (GOOG) then click on the "GOOG" link. you'll notice that anyone can click on the "tables" tab and view my entire database and that of course is very bad.

mooffie’s picture

First, read "Displaying table data from DB",

http://drupal.org/node/83874

But... it seems that you don't want to show all the records in the table. So pass the parameter (e.g. the stock symbol) using the url ('node/1234?symb=GOOG') and embed it in the SQL query:

... = db_query("... WHERE symb = '%s'", $_REQUEST['symb']);
crick’s picture

I appreciate your responce as it has set me in the right direction. One thing I need a little elaboration on is getting the table into a new window. If you look at the site and look up a symbol you will notice that now you are given two sets of rows with a different set of headers. The first row is coming from a table called stockapi. stockapi has only one row for each different symbol, which contains the latest quote data.

The second is a set of rows that you see contains old stock data or "history" and is retrieved from a table called stockapi_$symbol (where if $symbol = GOOG; the db table would be stockapi_GOOG.

here is the code i'm currently using to generate that 2nd set of rows:

function stock_do_history($format_history, $symbol) {

  // Get the column headers
  $headers = stock_get_history_headers($format_history);

    if (!empty($symbol)) {
         $result = db_query("SELECT 
       updated, current_price, change_amt, opening, high, low, volume, last_date, last_time
       FROM {stockapi_$symbol} ORDER BY updated DESC");
       while($row = db_fetch_array($result))
  {
      $rows[] = $row;
  }
 }
  if (!empty($rows)) {
    return theme('table', $headers, $rows);
  }
}

Instead of having the 2nd set of rows displayed beneath the 1st row, I'd like to click on the "Symbol" in the first row and have a new window open up containing the 2nd set of rows. Also, I need to be able to break them up into multiple pages so that it doesn't display everything on one page.

mooffie’s picture

I'd like to click on the "Symbol" in the first row and have a new window open up containing the 2nd set of rows.

When I hover with my mouse over GOOG I see that it points me to '/stock/?symbol=GOOG'.

You should change such links to: '/stock/history/GOOG'.

Then, in your module (let's call it 'stock'), add the following callback:

function stock_menu($may_cache) {
  ...
    $items[] = array(
      'path' => 'stock/history',
      'callback' => 'stock_history'
      ...
   );
   ...
)
		
function stock_history($symb) {
   // print the history records of symbol $symb
}

It's hard for me to be more specific because I don't see the code. I don't know how it's organized, I don't know your drupal/coding abilities.

Also, I need to be able to break them up into multiple pages so that it doesn't display everything on one page.

That's trivial. Let's first tackle the first problem.

crick’s picture

I really appreciate your help on this. Below is the entire module.


//$Id: stock.module,v 1.17.2.2 2006/09/12 14:46:25 kbahey Exp $

// Copyright 2004-2005 Khalid Baheyeldin 2bits . com


function stock_help($section) {
  $output = '';

  switch ($section) {
    case 'admin/modules#description':
      $output = t('Stock quote page and block');
      break;
      
    case 'admin/help#stock':
    case 'admin/settings/stock':
      $output = t('This module provides stock quotes. It either displays a page format or a block format. Users can store their own stock symbols, and have a block that tells them how their portfolio is performing.');
      break;
  }
  
  return $output;
}
  
function stock_settings() {
  if (stock_check_dependancies()) {   
    $form['stock_description'] = array(
      '#type' => 'textarea',
      '#title' => t('Description'),
      '#default_value' =>  variable_get('stock_description', t('This is the stock quote page.')),
      '#cols' => 70,
      '#rows' => 7,
      '#description' => t('This text will be displayed at the top of the stock quote page'),
    );

    $form['stock_block_title'] = array(
      '#type' => 'textfield',
      '#title' => t('Stock block title text'),
      '#default_value' => variable_get('stock_block_title', t('stocks')),
      '#size' => 35,
      '#maxlength' => 255,
      '#description' => t('If configured as a block, this text shows as the block title.'),
    );

    $form['stock_overview_title'] = array(
      '#type' => 'textfield',
      '#title' => t('Navigation link text'),
      '#default_value' => variable_get('stock_overview_title', t('stock quote')),
      '#size' => 35,
      '#maxlength' => 255,
      '#description' => t('The text in the navigation link which points to the stock quote page.'),
    );
  }  
    
  return $form;
}  

function stock_perm() {
  return array ('use stock');
}

function stock_link($type, $node = 0, $main = 0) {
  $links = array();

  if ($type == 'page' && user_access('use stock')) {
    $links[] = l(t('stock'), 'stock');
  }

  return $links;
}

function stock_menu($may_cache) {
  $items = array();

  $title  = t(variable_get('stock_overview_title', 'stock quote'));
  $path   = 'stock';

  $items[] = array(
    'path'     => 'stock',
    'title'    => $title,
    'access'   => user_access('use stock'),    
    'callback' => 'stock_page',
    'weight'   => 0,    
  );

  return $items;
}

// Display the stock page
function stock_page() {
  if (stock_check_dependancies()) {
    $output = stock_contents('page');
  }

  return theme('stock_page', $output);
}

function stock_check_dependancies() {
  if (!module_exist('stockapi')) {
    drupal_set_message('stockapi module is not configured');
    return false;
  }
  return true;
}

function stock_block($op = 'list', $delta = 0) {
  $title = variable_get('stock_block_title', 'stocks');

  switch ($op) {
    case 'list':
      $block[0]['info'] = t($title);
      break;

    case 'view':
      switch ($delta) {
        case 0:
          $block['subject'] = t($title);
         if (user_access('use stock')) {
           $block['content'] = theme('stock_page', stock_contents('block'));
         }
         else {
           $block['content'] = t('You do not have permission to access this feature');
         }
          break;
        }
      break;
    }
    return $block;
}

// Display the form
function stock_form($symbols) {
  global $user;

  $form['symbol'] = array(
    '#type' => 'textfield',
    '#title' => t('Stock Symbol(s)'),
    '#default_value' => $symbols,
    '#size' => 40,
    '#maxlength' => 255,
    '#description' => t('Enter one or more space separated stock symbols to receive quotes on.'),
  );
     
  $form['button_quote'] = array(
    '#type' => 'button',
    '#input' => TRUE,
    '#name' => 'op',
    '#button_type' => 'submit',
    '#value' => t('Quote'),
    '#execute' => TRUE,
  );        
   
  // If user is logged in, then show him a Save button
  if ($user->uid) {  
    $form['button_save'] = array(
      '#type' => 'button',
      '#input' => TRUE,
      '#name' => 'op',
      '#button_type' => 'submit',
      '#value' => t('Save'),
      '#execute' => TRUE,
    );      
  }
  
  $form['#method'] = 'post';

  return drupal_get_form('stock_form', $form);
}

// Database Functions

// Get the saved symbols for the user
function stock_get_user_quotes() {
  global $user;
  return db_result(db_query("SELECT symbols FROM {stock} WHERE uid = %d", $user->uid));
}

// Save the symbols for the user
function stock_save_user_quotes($symbols) {
  global $user;

  // Delete the user's saved stocks
  db_query("DELETE FROM {stock} WHERE uid = %d", $user->uid);
  // Insert the new ones
  db_query("INSERT INTO {stock} VALUES (%d, '%s')", $user->uid, $symbols);
}

function stock_contents($format = 'block') {
  global $user;  

  switch ($format) {
    case 'block':
      if ( $user->uid ) {
        // get the stored tickers
        $symbols = stock_get_user_quotes();

        // get the quotes
        $output = stock_do_quote('short', $symbols);
      }
      else {
        $output = l('login', 'user/login') . ' or ' . l('register', 'user/register') . ' for portfolio';
      }
      break;

    case 'page':      
      $op = isset($_POST['op']) ? $_POST['op'] : '';
      $edit = isset($_POST['edit']) ? $_POST['edit'] : '';  
      $symbols = isset($edit['symbol']) ? strtoupper($edit['symbol']) : '';          
      $saved_symbols = '';
      $output = variable_get('stock_description', 'This is the default stock quote page.');            
      
      if ($op == t('Save') && $user->uid) {
        // If the user pressed the Save button, and is logged in, save their preferences        
        stock_save_user_quotes($symbols);        
        drupal_set_message(t('Your portfolio has been saved.'));
      }
      elseif ($op == t('Quote') && $user->uid) {
        // If the user pressed the Quote button, and is logged in, show his saved portfolio also, if it exists.
        $saved_symbols = stock_get_user_quotes();               
      }
      else{
        if (empty($symbols)) {
          // If nothing is entered, then get those saved, if any
          $symbols = stock_get_user_quotes();        
        }
      }
      
      if (!empty($symbols)) {      
      	$output .= stock_do_quote('long', $symbols);
      }

       if (!empty($symbols)) {      
      	$output .= stock_do_history('long', $symbols);
      }
  
      if(!empty($saved_symbols)){
        $output .= '<p>'. l(t('Your registered portfolio is'), 'stock') .': '. $saved_symbols .'</p>';    
      } 
      
      $output .= stock_form($symbols);          
      break;
  }     
  
  return $output;
}

function stock_do_quote($format = 'long', $symbol_list) {

  // Get the column headers
  $headers = stock_get_headers($format);

  // Convert the space separated list of symbols into an array
  $symbol_list = explode(' ', trim($symbol_list));

  foreach ($symbol_list as $symbol) {
    if (!empty($symbol)) {
      $stock = stockapi_load($symbol);
      if ($stock[8] != 'N/A') {
        // Date is 'N/A' means an invalid stock symbol  
        $rows[] = array('data'=> stock_process_data($stock, $headers));
      }else{
        drupal_set_message(t('Invalid symbol') .': '. $symbol);       
      }
    }
  }
  
  if (!empty($rows)) {
    return theme('table', $headers, $rows);
  }
}

function stock_do_history($format_history, $symbol) {

  // Get the column headers
  $headers = stock_get_history_headers($format_history);

    if (!empty($symbol)) {
         $result = db_query("SELECT 
       updated, current_price, change_amt, opening, high, low, volume, last_date, last_time
       FROM {stockapi_$symbol} ORDER BY updated DESC");
       while($row = db_fetch_array($result))
  {
      $rows[] = $row;
  }
 }
  if (!empty($rows)) {
    return theme('table', $headers, $rows);
  }
}

function theme_stock_page($output) {
  $css_path = drupal_get_path('module', 'stock') .'/stock.css";';
  drupal_set_html_head('<style type="text/css">@import "/'. $css_path .'</style>');   
  return $output;
}

function stock_get_headers($format = 'long') {
  // keys in these arrays must match the column names in the
  // stockapi table
  $stock_long_array = array (
    'symbol'        => "Symbol",
    'name'          => "Name",
    'current_price' => "Last",
    'change_amt'    => "Change",
    'opening'       => "Opening",
    'high'          => "High",
    'low'           => "Low",
    'volume'        => "Volume",
    'last_date'     => "Date",
    'last_time'     => "Time"
  );

  $stock_short_array = array (
    'symbol'        => "Symbol",
    'current_price' => "Last",
    'change_amt'    => "Change"
  );

  $headers = $stock_long_array;
  if ($format == 'short') {
    $headers = $stock_short_array;
  }
  
  return $headers;
}

function stock_get_history_headers($format_history) {
  $history_headers = array (
    'updated'        => "Updated",
    'current_price' => "Price",
    'change_amt'    => "Change",
    'opening'       => "Opening",
    'high'          => "High",
    'low'           => "Low",
    'volume'        => "Volume",
    'last_date'     => "Date",
    'last_time'     => "Time"
  );

  $headers = $history_headers;

  return $headers;
}

function stock_process_data($data, $headers) {
  $columns = array(); 
 
  foreach($headers as $key => $value) {
    $field = $data[$key];        
    
    // Special handling for some fields
    switch($key) {
      case 'symbol':
        $columns[] = _add_full_quote($field);
        break;
      case 'change_amt':
        $columns[] = _right_align(_up_down_tick(_decimals($field)));
        break;
      case 'current_price':
      case 'opening':
      case 'high':
      case 'low':
        $columns[] = _right_align(_decimals($field));
        break;
      case 'volume':
        $columns[] = _right_align($field);
        break;
      default:
        $columns[] = $field;
        break;
    }
  }

  return $columns;
}

function _add_full_quote($field) {
  // For symbol, we make it upper case
  $symbol = strtoupper($field);

  // Then we create a URL for the full quote page
  $url = drupal_get_path('module', 'stock') . '/history/' . $symbol;
  
  return l($symbol, $url, array('target' => '_blank'), NULL, NULL, TRUE);
}

function _right_align($field) {
  return '<div align="right">'.$field.'</div>';
}

function _decimals($field) {
  return number_format($field, 2);
}

function _up_down_tick($field) {
  // Add a style for the change field, so we can
  // add color, up tick/down tick, ...etc.
  $style='plus';
  if ($field < 0) {
    $style='minus';
  }  
  return '<div class="' . $style .'">'. $field .'</div>';
}

function stock_update_1() {
  return _system_update_utf8(array('stock'));
}

I started with the contributed stockapi.module and stock.module and have modified them. I have very little programming experience so I truely do appreciate you willingness to help. This is stock.module