In this code snippet tutorial, I'll show how you can use the PEAR library HTML_QuickForm2 to create a simple authentication form with a custom validation callback.

<?php

// start session
session_start();

// check if user needs to authenticate
if (empty($_SESSION['accountID'])) {
  
  // show form
  require_once('HTML/QuickForm2.php');
  // NOTE: have the form submit to itself
  $form = new HTML_QuickForm2('login', 'post', array('action' => $_SERVER['REQUEST_URI']));

  // add username field
  $username = $form->addElement('text', 'username')->setLabel('username:');
  $username->addRule('required', 'username is required.');

  // add password field
  $password = $form->addElement('password', 'password')->setLabel('password:');
  $password->addRule('required', 'password is required.');

  // add submit button
  $form->addElement('submit', null, array('value' => 'submit'));

  // add filter to trim all elements
  $form->addRecursiveFilter('trim');

  // add custom validation rule
  $form->addRule(
    'callback',
    'authentication failed.',
    array(
      'callback' => 'portal_authentication_validation',
    )
  );

  // check if form validates
  if ($form->validate()) {
    
    // at this point, the form has validated, set session data as authentication
    // NOTE: at this point, the account ID should be fetched from the database, etc
    $_SESSION['accountID'] = 'some_val';
    
    // redirect user (reload url)
    header("Location: " . $_SERVER['REQUEST_URI']);
    die;
    
  }
  // form did not pass validation, display form
  else {

    // display form
    echo $form;
  
  }
   
}
// user is already authenticated..
else {

  // do something here!
  echo "Hello Auth User!";

}

// defines custom validation callback function
function portal_authentication_validation($form_args) {

  /*
  Args..
  $form_args['username']
  $form_args['password']
  */

  // At this point, query the database to validate username/password, etc
  if ($user_and_password_validates) {
    return TRUE:
  }
  
  return FALSE;

}
?>

The above code will result in the following form:

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

Here's a quick code snippet I just wrote to dump and compress (gzip) all mysql databases on a server into separate files using PHP and PEAR.

<?php

// include PEAR DB library
require_once('DB.php');

// define the DSN in an array
// NOTE: user must have access to all databases
$dsn = array(
  'phptype' => 'mysql',
  'username' => 'YOURUSER',
  'password' => 'YOURPASSWORD',
  'hostspec' => 'HOSTNAME', // localhost?
);

// instantiate a PEAR DB object
$DB =& DB::connect($dsn);

// check for an error
if (DB::isError($DB)) die($DB->getMessage());

// set the DB fetch mode to associative
$DB->setFetchMode(DB_FETCHMODE_ASSOC);

// define sql statement
$sql = "show databases";

// fetch sql result
$databases = $DB->getAll($sql);

// loop through results
foreach ($databases as $index => $result) {

  // define the mysqldump command
  $command = "mysqldump -u " . $dsn['username'] . " "
    . "-p" . $dsn['password'] . " "
    . "-h " . $dsn['hostspec'] . " "
    . $result['Database'] . " | gzip > "
    . $result['Database'] . ".sql.gz";

  // execute command
  `$command`;
    
}
?>

In this tutorial I'll show how I used the Forms API to add a file upload field and attach the uploaded file in an email. I have experience using the PEAR libraries Mail and Mail_MIME to handle MIME/HTML emails and file attachments, so I decided to use them.

If you are unfamiliar with PEAR, here are a few quick tips:

# install pear via YUM (this will vary across operating system)
$ sudo yum install php-pear

# upgrade all PEAR packages
$ sudo yum upgrade-all

# check for Mail and Mail_MIME libraries
$ pear list | grep -i mail
Mail             1.1.14  stable
Mail_Mime        1.5.2   stable

# install a PEAR library
$ sudo pear install Mail

Now we can create our form callback function. NOTE: I'm keeping this form as simple as possible to focus on the file attachment functionality.

<?php
function _MYMODULE_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['upload'] = array(
    '#type' => 'file',
    '#title' => t('Attach a file'),
  );
    
  // add a submit button
  $form['submit'] = array(
    '#type' => 'submit',
    '#value' => 'Submit',
  );

}
?>

The above will create a basic form object with a file upload file and a submit button. The form can be included using the drupal_get_form function. Example:

<?php
$html = drupal_get_form('_MYMODULE_form');
?>

Next I added a validation function to validate the file upload.

<?php
function _MYMODULE_form_validate($form, &$form_state) {

  // define upload field name
  // NOTE: this should match the name of your form file field
  $fieldName = 'upload';
    
  // If a file was uploaded, process it.
  if (isset($_FILES['files']) && is_uploaded_file($_FILES['files']['tmp_name'][$fieldName])) {

    // attempt to save the uploaded file
    $file = file_save_upload($fieldName);

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

The above validation form will check to see if a file has been uploaded and set a form error as necessary. The last line of the function sets information about the successfully uploaded file to the $form_state array, which will then be passed to the submit handler function.

Next I created a form submit handler function which will create a Mail_Mime object, attach the file, send the email, and set a drupal message for the user to see.

<?php
function _MYMODULE_form_submit($form, &$form_state) {

  // create the email subject
  $subject = 'File attachment form submitted';
    
  // create the text version of the email body
  $body_text = "Dear Eric,\n\nblah blah blah.\nblah blah blah.\n\nRegards,\nEric";
    
  // create an html version of the email
  $body_html = str_replace("\n", "<br>", $body_text);

  // define who receives the email
  $to = "YOUREMAILADDRESS";

  // include pear libraries
  require_once('Mail.php');
  require_once('Mail/mime.php');
    
  // create new mail mime object
  $mime = new Mail_mime("\n");

  // add attachment
  if ($form_state['values']['file']) {
    $mime->addAttachment(
      $form_state['values']['file']->filepath,
      $form_state['values']['file']->filemime,
      $form_state['values']['file']->filename
    );
  }
    
  // set text message
  $mime->setTXTBody($body_text);
    
  // set html message
  $mime->setHTMLBody($body_html);
    
  // get message body
  $body = $mime->get();

  // define headers
  $hdrs = array(
    'From' => variable_get('site_mail','YOUREMAILADDRESS'),
    'Subject' => $subject,
  );
    
  // process headers
  $hdrs = $mime->headers($hdrs);
    
  // create mail object
  $mail =& Mail::factory('mail');

  // send email
  $mail->send($to, $hdrs, $body);   

  // set message to user
  drupal_set_message('The file attachment form has been submitted.');    

}
?>

If everything worked correctly, the form will be validated, submitted, and an email with the file attachment will be sent.

Here's common syntax I use for instantiating a PEAR DB class object.

<?php
require_once('DB.php');
$dsn = 'mysql://USER:PASSWORD@HOST/DATABASE';
$DB =& DB::connect($dsn);
if (DB::isError($DB)) {
  die($DB->getMessage());
}
$DB->setFetchMode(DB_FETCHMODE_ASSOC);
?>