background image
HomeRecent PostsDrupalSearchTagsRSSContactAboutAccount

Taking a SQL statement and generating a table from the results
Eric.London's picture
Submitted by Eric.London on 6/2/2009
Tags:

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;
}
?>

Integrating the jQuery Accordion user interface plugin with a Drupal item list
Eric.London's picture
Submitted by Eric.London on 10/20/2008
Tags:

You've probably seen the accordion effect in use at Apple's website. I recently implemented this plugin in a Drupal module to get a similar effect.

First, download the plugin from Bassistance. I stuck the jquery.accordion.js in my module directory and then included it using the following code:

<?php
drupal_add_js(drupal_get_path('module','MYMODULENAME') . '/jquery.accordion.js');
?>

I created a dataset to use with the theme_item_list function. For this example, I'll query the node table.

<?php
$sql = "select title from {node} where status='1' order by title asc";
$resource = db_query($sql);
$results = array();
while ($row = db_fetch_array($resource)) $results[] = $row;
?>

I looped through the dataset and created an item list array consisting of an A tag and a DIV.

<?php
$items = array();
foreach ($results as $k => $v) {
  // NOTE: you should use the l() function here, excuse my example
  $items[] = "<a>{$v['title']}</a><div>MYTEXTHERE</div>";
}
?>

I used the theme function to generate the html for a UL with a unique identifier.

<?php
$title = MYTITLE;
$type = 'ul';
$attributes = array(
  'id' => 'MYITEMLISTID',
);
$page_contents .= theme('item_list', $items, $title, $type, $attributes);
?>

Last, I added the jQuery to apply the accordion effect to my UL.

<?php
$(document).ready(function(){
  $('#MYITEMLISTID').accordion();
});
?>

Querying the Drupal database and fetching all the results in an associative array
Eric.London's picture
Submitted by Eric.London on 6/6/2008
Tags:

Here's a code snippet to show how to execute a query and fetch all the results into an associative array:

<?php
// create the SQL in a string
$sql = "
  select *
  from {node} n
  where n.status = '1'";

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

// loop through the resource and fetch the rows
$results = array();
while ($row = db_fetch_array($resource)) $results[] = $row;

// show the results
echo "<pre>" . print_r($results, TRUE) . "</pre>";
?>

Syndicate content