Ok - I guess I just needed to give my brain a break. After the weekend, I came in and was able to code it correctly. Here’s the solution--I hope it helps someone else! Enjoy!
1) Top-of-page snippet (I just threw this in our template since almost every page on our site will use this feature). The snippet pulls in the config file which contains all the SQL connection settings in an array. Then it loads the class and runs the "connectToDB" function of that class.
<?php
// get path to project folder
$pl_path = $modx->getOption('base_path').'assets/project/';
// load project class
$modx->getService('stuff', 'MyStuff', $pl_path.'classes/');
// load config
require($pl_path.'config.inc.php');
// connect to the database
$modx->stuff->connectToDB($SQL_ARRAY);
?>
2) Here’s the class that gets loaded. The "connectToDB" function also prepares the statement and uses bindParam (instead of bindValue); that way one only need change the value of the bound variable and then execute on every time you want to pull an image.
<?php
class MyStuff {
//*****************************************************************
// INITIALIZE VARIABLES
//*****************************************************************
private $dbh;
private $sth;
private $mySerial;
//*****************************************************************
// INITIALIZE DATABASE CONNECTION AND QUERY
//*****************************************************************
public function connectToDB ($SQL_ARRAY) {
// CREATE QUERY
try {
$this->dbh = new PDO('mysql:host='.$SQL_ARRAY['host'].';dbname='.$SQL_ARRAY['db'], $SQL_ARRAY['user'], $SQL_ARRAY['pass'], array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true ));
} catch (PDOException $e) { }
// CREATE QUERY
$sql = 'SELECT img.image_serial, eve.event_name '; //<--- select fields
$sql.= 'FROM pl_images AS img '; //<--- search main table
$sql.= 'LEFT JOIN pl_events AS eve ON img.image_event = eve.event_id '; //<--- join events table
$sql.= 'WHERE img.image_serial=:serial AND img.image_active=1 '; //<--- where clauses
$sql.= 'LIMIT 1 '; //<--- limit (just in case!)
// PREPARE QUERY
$this->sth = $this->dbh->prepare($sql);
$this->sth->bindParam(':serial', $this->mySerial, PDO::PARAM_STR);
}
//*****************************************************************
// QUERY THE DATABASE
//*****************************************************************
public function queryArchive ($serial) {
// ENSURE THAT SERIAL WAS PROVIDED
if ( isset($serial) ) {
// set current search serial number
$this->mySerial = $serial;
// run query
$this->sth->execute();
// return results
$row = $this->sth->fetch(PDO::FETCH_ASSOC);
// close query
$this->sth->closeCursor();
// TEST RESULTS
if ($row===false) {
return 'no results found for serial number: '.$serial;
} else {
return $row;
}
// IF NO SERIAL NUMBER
} else {
return 'serial number not provided';
}
}
}
?>
3) This snippet is called anywhere on the page where you want to place an image. Optional parameters include image width, float (left or right) or stack (left or right), a caption, and, of course, the serial number of the image. The bulk of the snippet code is offloaded into a file (#4 below).
<?php
// load necessary modules
$modx->runSnippet('load_mootools');
$modx->runSnippet('load_squeezebox');
$output = '';
// generate paths
$assets = $modx->getOption('base_url').'assets/';
$pl_url = $assets.'photolab/';
$pl_path = $modx->getOption('base_path').'assets/project/';
// include image code
// (moved to external file for easier editing)
require($pl_path.'processors/image.inc.php');
return $output;
?>
4. The contents of the file that the snippet loads. Note that the config file must be loaded again to be available to this snippet.
<?php
// include necessary functions & libraries
require($pl_path.'config.inc.php');
require_once($pl_path.'functions/filereader.func.php');
require_once($pl_path.'functions/photopath.func.php');
//*****************************************************************
// QUERY THE DATABASE
//*****************************************************************
$row = $modx->stuff->queryArchive($serial);
//*****************************************************************
// TEST AND SET OPTIONAL PARAMETERS
//*****************************************************************
// test for width setting - default is 250
$myWidth = (isset($width)) ? (int)$width : 250;
// minimum width is 75
$myWidth = ($myWidth < 75) ? 75 : $myWidth;
// maximum width is 736
$myWidth = ($myWidth > 736) ? 736 : $myWidth;
// select image type based on image size
if ($myWidth == 75) {
$type = 'thumb';
} elseif ($myWidth <= 300) {
$type = 'web';
} else {
$type = 'small';
}
// test for float setting - default is left
$myFloat = ( (empty($float) & empty($stack)) || $float=='left' ) ? 'float_left' : (($float=='right') ? 'float_right' : '');
// test for stack setting - default is none
$myFloat = $myFloat ? $myFloat : (( $stack=='left') ? 'stack_left' : (($stack=='right') ? 'stack_right' : '') );
// generate width string
$widthString = 'width: '.$myWidth.'px;';
//*****************************************************************
// GENERATE DETAIL PAGE
//*****************************************************************
if ( is_array($row) ) { //<--- for non-empty result
// generate image URL
$dispUrl = photoPath($row['image_serial'], $type, $pl_url);
// generate link to detail popup
$vars = 'serial='.$serial;
$detailUrl = $modx->makeUrl($DETAIL_PAGE, '', $vars);
// read in the image link template
$image_tpl = get_file_contents($pl_path.'templates/image.tpl.html');
// inject content
$placeholders = array('[[+pli_img]]', '[[+pli_link]]', '[[+pli_title]]', '[[+pli_class]]', '[[+pli_style]]', '[[+pli_caption]]');
$values = array($dispUrl, $detailUrl, $row['event_name'], $myFloat, $widthString, $caption);
$output = str_replace($placeholders, $values, $image_tpl);
} else { //<-- for empty result
$output = '';
}
//*****************************************************************
// OUTPUT DEBUG DATA
//*****************************************************************
if ($debug) {
$output .= '<div style="background: #a00; color: #fff; padding: 20px;">';
$output .= "Serial: <strong>$serial</strong><br/>";
$output .= "Result: <strong>".(is_array($row)?serialize($row):$row)."</strong><br />";
$output .= "Float In: <strong>$float</strong><br/>";
$output .= "Width In: <strong>$width</strong><br/>";
$output .= "Caption In: <strong>$caption</strong><br/>";
$output .= "Type: <strong>$type</strong><br/>";
$output .= "URL: <strong>$dispUrl</strong><br/>";
$output .= "Detail: <strong>$detailUrl</strong><br/>";
$output .= "Float Out: <strong>$myFloat</strong><br/>";
$output .= "Width Out: <strong>$myWidth</strong><br/>";
$output .= "Width String: <strong>$widthString</strong>";
$output .= '</div>';
}
?>
☆ A M B ☆