Eric's Drupal Blog | a place to share Drupal coding experiences - theming, module development, jQuery, Linux shell scripts

Welcome to Eric's Drupal Blog, a place for me to share my Drupal coding experiences. Please post your comments and suggestions and I'll respond at my earliest convenience. Please note, this website is privately operated and is not affiliated with Drupal.

Implementing visual search results with search lucene and lightbox2

Submitted by Eric on Sat, 08/28/2010 - 00:24

I created a Drupal site to host my photography in CCK Imagefield nodes and used Lucene to enhance my search functionality. By default Drupal's search results are text-based so I decided to add some code to show image thumbnails in my search results. I checked out Drupal Lucene's hooks and decided to implement a hook_luceneapi_result_alter() function in my existing module.

<?php
function MYMODULE_luceneapi_result_alter(&$result, $module, $type = NULL) {
  
  // check for node results
  if ($type == 'node') {
  
    // check node type
    if ($result['node']->type == 'image') {
    
      // define an imagecache image path for image thumbnail
      $imagecache_path_thumbnail = file_directory_path() . '/imagecache/thumbnail' . str_replace(file_directory_path(),'',$result['node']->field_image[0]['filepath']);      
      
      // define an imagecache image path for image (large)
      $imagecache_path_large = file_directory_path() . '/imagecache/large' . str_replace(file_directory_path(),'',$result['node']->field_image[0]['filepath']);
    
      // define theme_image() variables
      $alt = check_plain($result['node']->title);
      $title = check_plain($result['node']->title);
      // add rel=lightbox to enable lightbox2 module
      $attributes = array(
        'rel' => 'lightbox',
      );
      // let imagecache define the size
      $getsize = FALSE;
      // generate the image hml
      $image_html = theme('image', $imagecache_path_thumbnail, $alt, $title, $attributes, $getsize);      
    
      if ($image_html) {
                
        // define lightbox link
        $image_link = l(
          $image_html,
          $imagecache_path_large,
          array(
            'html' => true,
            'attributes' => array(
              'rel' => 'lightbox',
            )
          )
        );

        // add data to the result variable, passed by reference
        $result['image_thumbnail'] = $image_link;
        
      }
    
    }
  
  }

}
?>

The above code adds additional data to my search results variables. I then implemented a hook_preprocess_search_result() function in my theme's template.php file to pass this data to the search-result.tpl.php template file.

<?php
function MYTHEME_preprocess_search_result(&$variables) {

  // ...snip...

  // check for lucene node search results
  if ($variables['type']=='luceneapi_node') {

    // check for image
    if ($variables['result']['image_thumbnail']) {    

      // pass additional data to theme template file
      $variables['image_thumbnail'] = $variables['result']['image_thumbnail'];

    }
    
  }

}
?>

And in my theme's search-result.tpl.php template file, I added the following PHP to show the new variable.

<div class="search-result <?php print $search_zebra; ?>">

  <?php if($image_thumbnail): ?>
    <?php print $image_thumbnail; ?>
  <?php endif; ?>

  <!-- ...snip... -->

I also added a few lines of CSS in my theme's style.css file to tidy up the layout.

.search-results.luceneapi_node-results .search-result {
  clear: both;
}

.search-results.luceneapi_node-results .search-result img {
  float: left;
  margin: 0px 20px 20px 0px;
}

The visual results can be seen here on my photo gallery.

3 comments

Using awk, grep, sed, and mysqldump to script and backup your MySQL databases on the command line

Submitted by Eric on Wed, 08/25/2010 - 20:56

In this tutorial, I'll show how you can use awk, grep, and sed (my favorite command line tools) to backup and archive your MySQL databases. This can be useful to schedule a cron job, transfer your databases to another server, or any other type of scripting.

First, you'll have to get acquainted with connecting to and dumping your database on the command line. Depending on your user, credentials, and where the databases are located, your command might look something like this. Please note, there is no space between the password and the "-p" flag.

$ mysqldump -u user -pPASSWORD -h hostname database > database.sql

To simplify my example, I'm going to shorten the mysqldump command to the follow.

$ mysqldump database > database.sql

Now that we're MySQL command line pros, I'll break down each command. I'll start by showing all the databases.

Eric-Londons-MacBook-Pro:backup Eric$ mysql --execute="show databases"
+---------------------+
| Database            |
+---------------------+
| customers           |
| db_pics_ericlondon  |
| db_thedrupalblog_d6 |
| drupal              |
| drupal-pics         |
| drupalmusicproject  |
| itunes              |
+---------------------+

Now, I'll "pipe" the output from the previous command into awk to show the first column data.

Eric-Londons-MacBook-Pro:backup Eric$ mysql --execute="show databases" | awk '{print $1}'
Database
customers
db_pics_ericlondon
db_thedrupalblog_d6
drupal
drupal-pics
drupalmusicproject
itunes

And use grep to remove the first line that says "Database".

Eric-Londons-MacBook-Pro:backup Eric$ mysql --execute="show databases" | awk '{print $1}' | grep -iv ^Database$
customers
db_pics_ericlondon
db_thedrupalblog_d6
drupal
drupal-pics
drupalmusicproject
itunes

And use sed to build the mysqldump command. This one is kinda tricky, sorry. As you can see, I also embedded the date command in there to generate today's date in the format: YYYYMMDD.

Eric-Londons-MacBook-Pro:backup Eric$ mysql --execute="show databases" | awk '{print $1}' | grep -iv ^Database$ | sed 's/\(.*\)/mysqldump \1 > \1.'$(date +"%Y%m%d")'.sql/'
mysqldump customers > customers.20100825.sql
mysqldump db_pics_ericlondon > db_pics_ericlondon.20100825.sql
mysqldump db_thedrupalblog_d6 > db_thedrupalblog_d6.20100825.sql
mysqldump drupal > drupal.20100825.sql
mysqldump drupal-pics > drupal-pics.20100825.sql
mysqldump drupalmusicproject > drupalmusicproject.20100825.sql
mysqldump itunes > itunes.20100825.sql

Lastly, if everything looks good, you can pipe the output back to the command line.

Eric-Londons-MacBook-Pro:backup Eric$ mysql --execute="show databases" | awk '{print $1}' | grep -iv ^Database$ | sed 's/\(.*\)/mysqldump \1 > \1.'$(date +"%Y%m%d")'.sql/' | sh

Eric-Londons-MacBook-Pro:backup Eric$ ls -1
customers.20100825.sql
db_pics_ericlondon.20100825.sql
db_thedrupalblog_d6.20100825.sql
drupal-pics.20100825.sql
drupal.20100825.sql
drupalmusicproject.20100825.sql
itunes.20100825.sql

You could even take this one step further and pipe the output through gzip to compress the dumps :)

Add new comment

Using Drush to download contributed modules and setup a base Drupal installation

Submitted by Eric on Thu, 07/15/2010 - 10:15

Drush is a great command line module for administering a Drupal site. It's core features are listed here. Check out the README.txt for usage and installation documentation.

Drush can be used to setup a base Drupal installation with a few quick commands, awesome. One of it's biggest time saving features is downloading all the contributed modules when setting up a new site. Here are a few commands I use to setup a new Drupal site based on feature sets. The following commands should be run in the root directory of your drupal installation.

# Download development modules and themes:
drush dl admin admin_menu coder devel devel_themer reroute_email simpletest views_bulk_operations --destination=sites/all/modules/contrib/ --uri=http://yourhostname.com

# Download common/essential modules:
drush dl cck views date wysiwyg pathauto token captcha recaptcha location emfield link jquery_ui webform htmlpurifier luceneapi vertical_tabs --destination=sites/all/modules/contrib/ --uri=http://yourhostname.com

# Download SEO modules:
drush dl google_analytics xmlsitemap globalredirect site_verify --destination=sites/all/modules/contrib/ --uri=http://yourhostname.com

# Download structural/building modules:
drush dl context features ctools panels --destination=sites/all/modules/contrib/ --uri=http://yourhostname.com

# Download image handling modules:
drush dl imageapi transliteration filefield mimedetect imagefield imagecache imagecache_actions --destination=sites/all/modules/contrib/ --uri=http://yourhostname.com

NOTE: the destination and uri flags on the drush command are optional, but I recommend using them. In recent Drupal development I stopped using sites/default in favor of sites/hostname.com, to ensure that if I ever decide to move the site into a multi-site configuration, I will not have overlapping sites/default/files directories.

You can even enable the downloaded modules from the command line by using the "drush en" command..

drush help en

2 comments

Exporting iTunes data into MySQL and creating SQL to show top rated albums

Submitted by Eric on Sat, 06/19/2010 - 17:51

In anticipation of my new iPhone 4 (and a larger hard drive), I decided to write a script to help choose which albums I should include. Through iTunes you can export your library as text, which can then be imported into MySQL, and SQL can be executed to show.. well, whatever your heart desires!

I started by exporting my entire music library, and chose Plain Text as the format. This iTunes functionality creates a tab separated list of columns and data.

I then created a new MySQL database and table (SEE: TABLE_NAME) to contain my iTunes song data. I also added an additional column as a primary key, called sql_id.

Next, I created a PHP script to parse the text file, dynamically add the columns to my table, and import all my song data. This code uses PEAR's DB library for the database access layer.

<?php
// define table name
define('TABLE_NAME', 'itunes');

// create database connection
require_once('DB.php');
$dsn = 'mysqli://db_user:db_password@db_host/database_name';
$DB =& DB::connect($dsn);
if (DB::isError($DB)) {
  die($DB->getMessage());
}
$DB->setFetchMode(DB_FETCHMODE_ASSOC);

// load text file
$file = file_get_contents('Music.txt');

// explode on new line
$file = explode("\r", $file);

// get a list of existing columns
$sql = "show columns in " . TABLE_NAME;
$result = $DB->getAll($sql);
$existing_columns = array();
foreach ($result as $key => $value) {
  $existing_columns[] = $value['Field'];
}

// create a variable to contain sql column names
$sql_column_name = array();

// loop through each line in the file
foreach ($file as $key => $value) {

  // explode on tab to get column list
  $exploded = explode("\t", $value);

  // check for first row, which contains column headers
  if ($key == 0) {

    // loop through column list and ensure they exist
    foreach ($exploded as $new_table) {
    
      // create a new column name without spaces
      $new_table = str_replace(' ' , '_', $new_table);

      // check if the new column should be added    
      if (!in_array($new_table, $existing_columns)) {
           
        // define sql to add new column
        $sql = "alter table " . TABLE_NAME . " add column `$new_table` varchar(255) default null";
        $result = $DB->query($sql);
        
        // check for error
        if (!$result) {
          echo "<pre>" . print_r($result, true) . "</pre>";
          die;
        }
                
        // define sql to add index
        $sql = "alter table " . TABLE_NAME . " add index `index_$new_table` ($new_table)";
        $result = $DB->query($sql);
        
        // check for error
        if (!$result) {
          echo "<pre>" . print_r($result, true) . "</pre>";
          die;          
        }
      
      } // end if
      
      $sql_column_names[] = $new_table;
    
    } // end foreach
      
  } // end if ($key[0])
  else {
    
    // prepare values to insert
    $insert_values = array();
    foreach ($exploded as $k => $v) {
      $insert_values[$k] = $DB->quoteSmart($v);
    }
    
    // define SQL to insert data into table
    $sql = "insert into " . TABLE_NAME . " (" . implode(',', $sql_column_names) . ") values (" . implode(',', $insert_values) . ")";
    
    // execute sql
    $result = $DB->query($sql);
    
    // check for SQL error
    if (!$result) {
      echo "<pre>" . print_r($result, true) . "</pre>";
    }
  
  }

}
?>

Now, all my iTunes data is accessible by SQL, sweet. I decided to create a view showing my albums with an average ranking.

create view album_rankings as
select Artist, Album, Genre, avg(My_Rating) as album_rating, count(*) as countX
from itunes
where Album != 'misc'
group by Artist, Album
order by avg(My_Rating) desc, countX desc

And finally, I executed the following SQL to show my highest rated albums.

select *
from album_rankings
where countX > 3

Top Rated Albums

2 comments

Upload an image using the Forms API and attach it to a image/file field in a CCK node

Submitted by Eric on Mon, 06/14/2010 - 19:52

In this tutorial I'll show you how to upload an image using the Forms API, create a new node, and attach the image to the CCK (filefield/imagefield) field. I wrote this code to work with the modules I primarily use for image processing: cck, filefield, imageapi, imagecache, imagefield, mimedetect, and transliteration.

After I installed those modules, I created a new node type (admin/content/types/add) called "Image" and added a single imagefield field.

Image node fields

Next, I created a custom module with a hook_menu() implementation:

<?php
// NOTE: this variable is used through the code,
// so I thought it would be better to put it in a constant
define('IMAGE_UPLOAD_CONTAINER', 'image_upload');

/**
 * Implements hook_menu()
 */
function helper_menu() {

  // create a blank array of menu items
  $items = array();
  
  // define page callback for upload form
  // NOTE: you'll want to restrict permission better [see: access arguments]
  $items['upload'] = array(
    'title' => t('Upload'),
    'description' => t('Upload'),
    'page callback' => 'drupal_get_form',
    'page arguments' => array('helper_page_callback_upload_form'),
    'access arguments' => array('access content'),
    'type' => MENU_CALLBACK,
  );
  
  // return menu items
  return $items;

}
?>

I defined the form function page callback:

<?php
/**
 * Implements page callback for upload form
 */
function helper_page_callback_upload_form() {

  // create an empty form array
  $form = array();
  
  // set the form encoding type
  $form['#attributes']['enctype'] = "multipart/form-data";
  
  // add a file upload file
  $form[IMAGE_UPLOAD_CONTAINER] = array(
    '#type' => 'file',
    '#title' => t('Upload an image'),
  );
   
  // add a submit button
  $form['submit'] = array(
    '#type' => 'submit',
    '#value' => 'Submit',
  );
  
  // return form array
  return $form;

}
?>

This page callback function results in the following form:

Image node form

Then I added the form validation and submit handler functions:

<?php
/**
 * Implements form validation handler
 */
function helper_page_callback_upload_form_validate($form, &$form_state) {

  // if a file was uploaded, process it.
  if (isset($_FILES['files']) && is_uploaded_file($_FILES['files']['tmp_name'][IMAGE_UPLOAD_CONTAINER])) {

    // validate file extension
    // NOTE: you can ellaborate on this code and add additional validation
    if ($_FILES['files']['type'][IMAGE_UPLOAD_CONTAINER] != 'image/jpeg') {
      form_set_error(IMAGE_UPLOAD_CONTAINER, 'Invalid file extension.');
      return;
    }

    // attempt to save the uploaded file
    $file = file_save_upload(IMAGE_UPLOAD_CONTAINER, array(), file_directory_path());

    // set error if file was not uploaded
    if (!$file) {
      form_set_error(IMAGE_UPLOAD_CONTAINER, 'Error uploading file.');
      return;
    }
       
    // set files to form_state, to process when form is submitted
    $form_state['storage'][IMAGE_UPLOAD_CONTAINER] = $file;
       
  }
  else {
    // set error
    form_set_error(IMAGE_UPLOAD_CONTAINER, 'Error uploading file.');
    return;   
  }

}

/**
 * Implements form submit handler
 */
function helper_page_callback_upload_form_submit($form, &$form_state) {
  
  // create new node object
  $new_node = (object) array(
    'type' => 'image',
    'uid' => $GLOBALS['user']->uid,
    'name' => $GLOBALS['user']->name,
    'title' => t('YOUR NODE TITLE'),
    'status' => 1,
    'field_image' => array(
      (array) $form_state['storage'][IMAGE_UPLOAD_CONTAINER],
    ),
  );
    
  // save node
  node_save($new_node);
  
  // clear form storage, to allow form to submit
  $form_state['storage'] = array();
  
  // redirect user, set message, etc!

}
?>

After using the form to upload an image, the following node was created:

New image node

2 comments