It’s been awhile since I worked on this, but I’ll try to pull together the pieces here. No guarantees they’ll work "as is" though ...
I created a snippet which invoked loopdbchunk, because I needed to prepend output to what loopdbchunk returned, based on info on the url. That snippet, called from a document, looked something like this :
$ouput = "";
$url_parameters = ''; //used by loopdbchunk for pagination
$output .= "<h3>Category Search Results</h3><hr />";
$url_parameters = 'categoryname='.$categoryname; //$categoryname is santized and comes from the URL
$query = ''; //here is where the query is constructed
//below is the call to loopdbchunk, with the variables hopefully being self-evident
$loopOutput = $modx->runSnippet( 'loopdbchunk', array(
'dbhost' => $dbhost, 'dbuser' => $dbuser, 'dbpass' => $dbpass, 'dbname' => $dbname, 'dbtable' => $dbtable,
'sql' => $query,
'chunkName' => $chunk1,
'missingValTxt' => '',
'numLinks' => '2',
'perPage' => '10',
'URLparameters' => $url_parameters
)
);
//pull in output from a chunk, which was passed as an argument
$chunkOutput = $modx->getChunk($chunk2);
return $output.$chunkOutput.$loopOutput;
Okay, here’s what my version of the loopdbchunk snippet looks like :
<?php
// Snippet: loopdbchunk
// description:
// this snippet will loop over a database table and for
// each row will inject some (or all) of the fields into placeholders
// contained in a chunk.
// parameters:
// tableName - the relevant table name (required, unless 'sql' is defined)
// chunkName - the name of the chunk to format the data (required)
// orderby - a database field name to order the results by.
// add 'desc' to get the results in a reverse order.
// sql - a custom sql query.
// when this parameter is set, the 'orderby'
// parameters will be ignored.
// if this parameter is not set, the query will select all the columns
// from the given table.
// missingValTxt - an optional string to display when a database value
// is missing (defaults to 'Not Available').
// perPage - the number of items per page. (default: 0)
// numLinks - the number of page-number links shown from each side
// of the current page number. ( default: 3)
// header - text to be put before the loop
// footer - text to put after the loop
$loopdbPath = $modx->config['base_path'] . 'assets/snippets/loopdbchunk/';
include_once($loopdbPath . 'transformFuncs.php');
require_once($loopdbPath. "idw_loopdbchunk.php");
$loopdbParam = array();
$loopdbParam['sql'] = $sql;
if (! isset($tableName))
{
if(! isset($loopdbParam['sql']))
{
die('loopDbChunk: \'tableName\' AND \'sql\' parameters are missing! (one of them reuired. exiting...');
}
}
else
{
$loopdbParam['table'] = $tableName;
}
if (! isset($chunkName))
{
die('loopDbChunk: \'chunkName\' parameter missing! exiting...');
}
else
{
$loopdbParam['chunk'] = $chunkName;
}
$loopdbParam['order'] = isset($orderby) ? $orderby : '';
$loopdbParam['missingTxt'] = isset($missingValTxt) ? $missingValTxt : 'Not Available';
$loopdbParam['per_page'] = isset($perPage) ? intval($perPage) : 0;
$loopdbParam['num_links'] = isset($numLinks) ? intval($numLinks) : 3;
$loopdbParam['url_parameters'] = isset($URLparameters) ? $URLparameters.'&' : ''; //MattC
$loopdbParam['header'] = isset($header) ? $header : '';
$loopdbParam['footer'] = isset($footer) ? $footer : '';
$loopdbParam['thehost'] = isset($dbhost) ? $dbhost : ''; //MattC
$loopdbParam['theuser'] = isset($dbuser) ? $dbuser : ''; //MattC
$loopdbParam['thepass'] = isset($dbpass) ? $dbpass : ''; //MattC
$loopdbParam['thedatabase'] = isset($dbname) ? $dbname : ''; //MattC
$looper = new LoopDBChunk($loopdbParam); //MattC
return $looper->renderChunks();
?>
No doubt that could be modified in a more intelligent way than I did.
My version of the loopdbchunk code is included from an outside file, as are some transformation functions used in the html output.
Here’s what’s in idw_loopdbchunk :
<?php
/*
* Title: LoopDBChunk Class
* Desc: loop over a database table and replace placeholders in
* a chunk with the query results
* Author: Amir Marcovitz
* Version: 1.2
*/
/*added comment line*/
if (!class_exists('LoopDBChunk'))
{
class LoopDBChunk {
var $hostvar = ''; //MattC
var $uservar = ''; //MattC
var $passvar = ''; //MattC
var $databasevar = ''; //MattC
var $ext_dbase =''; //MattC
var $tableName = '';
var $chunkName = '';
var $sql = '';
var $orderby = '';
var $missingValText = '';
var $fieldNames = array();
var $header = '';
var $footer = '';
var $chunk = '';
// array [placeholder name] => [placeholder index within the chunk]
var $placeholders = array();
function LoopDBChunk($paramArray)
{
$this->hostvar = $paramArray['thehost']; //MattC
$this->uservar = $paramArray['theuser']; //MattC
$this->passvar = $paramArray['thepass']; //MattC
$this->databasevar = $paramArray['thedatabase']; //MattC
if (($this->hostvar !== '') && ($this->uservar !== '') && ($this->passvar !== '') && ($this->databasevar !== '')) //MattC
{
$this->ext_dbase = true;
}
else
{
$this->ext_dbase = false;
}
$this->tableName = $paramArray['table'];
$this->chunkName = $paramArray['chunk'];
$this->sql = $paramArray['sql'];
$this->orderby = $paramArray['order'];
$this->missingValText = $paramArray['missingTxt'];
$this->perPage = $paramArray['per_page'];
$this->numLinks = $paramArray['num_links'];
$this->header = $paramArray['header'];
$this->footer = $paramArray['footer'];
$this->URLparameters = $paramArray['url_parameters'];//MattC
$this->getPlaceholders();
} // end of function LoopDBChunk //MattC
function addFieldPh($ph, $idx)
{
$this->placeholders[] = $ph . '##' . $idx;
$pieces = explode(':', $ph);
if(count($pieces) > 1)
{
for($i = 2; $i < count($pieces); $i++)
{
$this->fieldNames[] = $pieces[$i];
}
}
else if($ph != 'lo_ord')
{
$this->fieldNames[] = $ph;
}
} //end of function addFieldPh //MattC
// description : parse the chunk, put all the placeholders names and
// their locations in the array 'placeholders'
function getPlaceholders()
{
global $modx;
$chunk = $modx->getChunk($this->chunkName);
$len = strlen($chunk);
$idx = strpos($chunk, '[+');
while(($idx !== false) && ($idx < $len))
{
$endIdx = strpos($chunk, '+]', $idx + 2);
$ph = substr($chunk, $idx + 2, $endIdx - $idx - 2);
$this->addFieldPh($ph, $idx);
$idx = strpos($chunk, '[+', $endIdx + 1);
}
} // end of function getPlaceholders //MattC
/**
* function: getChunkReplaced
* ---------------------------
* description: replace the placeholders in a chunk with
* data from the DB
* $row: one row of the query results
* $chunk: the chunk to be processed
*
* @return: the chunk with all the placeholders replaced
*/
function getChunkReplaced($row, $rowIdx)
{
$i = 0;
$shiftIdx = 0;
$record = $this->chunk;
foreach ($this->placeholders as $phIdx )
{
list($ph, $idxInChunck) = explode('##', $phIdx);
$origLen = strlen($ph) + 4;
$transformFunc = explode(":", $ph);
//var_dump($transformFunc);
if(count($transformFunc) > 1)
{
if(function_exists($transformFunc[1]))
{
$funcParams = array();
// get the function parameters
// for each parameter translate from field name to
// the actual value for this row in the database
for($i = 2; $i < count($transformFunc); $i++)
{
if(array_key_exists($transformFunc[$i], $row))
{
$funcParams[] = $row[$transformFunc[$i]];
}
else
{
if($transformFunc[$i] == 'lo_ord')
{
$funcParams[] = $rowIdx;
}
else
{
$funcParams[] = $transformFunc[$i];
}
}
}
//var_dump($funcParams);
$replaceVal = call_user_func_array($transformFunc[1], $funcParams);
}
}
else
{
$replaceVal = ($ph == 'lo_ord') ? $rowIdx+1 : $row[$ph];
if(empty($replaceVal) && ($ph != 'lo_ord'))
{
$replaceVal = $this->missingValText;
}
}
// do the actual replacement
$record = substr_replace($record,
$replaceVal,
$idxInChunck + $shiftIdx,
$origLen);
// update the shift of the placeholders index
$shiftIdx += strlen($replaceVal) - $origLen;
}
return($record);
} //end of function getChunkReplaced //MattC
/**
* function: getPageStart
* -----------------------
* description: look for a query string with the key 'pagestart'
* and return its value if found
*
*/
function getPageStart()
{
// Determine the current page number.
parse_str($_SERVER['QUERY_STRING'], $urlParams);
if(isset($_GET['pagestart']) && is_numeric($_GET['pagestart']))
{
return($_GET['pagestart']);
}
else
{
return (1);
}
} //end of function getPageStart //MattC
/**
* function: renderChunks
* -----------------------
* description: do the sql query,
* call getChunkReplaced() for each row of the result,
* and return the modified chunks.
*
*/
function renderChunks()
{
global $modx;
require_once("idw_pagination.php");
$query = '';
$rs = '';
$output = $this->header;
$pageStart = $this->getPageStart();
if($this->sql != '')
{
$query = str_replace('_eq_', '=', $this->sql);
}
else
{
$fields = implode(',', array_unique($this->fieldNames));
$query = 'SELECT ' . $fields . ' FROM ' . $this->tableName;
if ($this->orderby != '')
{
$query .= ' ORDER BY ' . $this->orderby;
}
}
if ($this->ext_dbase) //MattC
{
//try to connect to and select the database //MattC
mysql_connect($this->hostvar,$this->uservar,$this->passvar) OR DIE ('Unable to connect to database! Please try again later.'); //MattC
mysql_select_db($this->databasevar) or die( "Unable to select database"); //MattC
$rs = mysql_query($query); //replacement code //MattC
$numRows = mysql_num_rows($rs); //replacement code //MattC
}
else
{
$rs = $modx->db->query($query);
$numRows = $modx->db->getRecordCount($rs);
}
if($this->perPage > 0)
{
$from = $pageStart - 1;
$query .= ' LIMIT ' . $from . ', ' . $this->perPage;
}
if ($this->ext_dbase)
{
$rs = mysql_query($query); //replacement code //MattC
mysql_close(); // MattC
}
else
{
$rs = $modx->db->query($query);
}
$this->chunk = $modx->getChunk($this->chunkName);
$i = 0;
if ($this->ext_dbase)
{
if ($rs)
{
while( $row = mysql_fetch_assoc($rs) ) //replacement code //MattC
{
$output .= $this->getChunkReplaced($row, $i);
$i++;
}
}
}
else
{
while( $row = $modx->db->getRow($rs) )
{
$output .= $this->getChunkReplaced($row, $i);
$i++;
}
}
$output .= $this->footer;
if($this->perPage > 0)
{
$p = new Pagination(array('per_page' => $this->perPage, 'otherURLparameters'=>$this->URLparameters, //MattC
'num_links' => $this->numLinks, //set to 0 as hack to not show page numbers
'cur_item' => $pageStart,
'first_link' => '', //‹ First
'prev_link' => 'Previous', //<
'next_link' => 'Next', //>
'last_link' => '', //Last ›
'full_tag_open' => '', //'' or <div class="ldb_pagination_full">
'full_tag_close' => '', //'' or </div><!--ldb_pagination_full-->
'first_tag_open' => '', //'' or <div class="ldb_pagination_first">
'first_tag_close' => ' ', //' ' or </div><!--ldb_pagination_first-->
'prev_tag_open' => '<span class="ldb_pagination_prev" style="float:left;width:25%;text-align:left;"> ', //' '
'prev_tag_close' => ' </span><!--ldb_pagination_prev--><span style="float:left;width:50%;text-align:center;">', //''
'cur_tag_open' => '<span class="currentPage" > ', //set style="display:none;" if not showing pages
'cur_tag_close' => '</span><!--currentPage-->', //'</span>'
'next_tag_open' => '</span><span class="ldb_pagination_next" style="float:left;width:23%;text-align:right;">', //'set to 73% vs 23% if not showing pages'
'next_tag_close' => '</span><!--ldb_pagination_next-->', //' '
'last_tag_open' => '', //' ' or <div class="ldb_pagination_last">
'last_tag_close' => '', //'' or </div><!--ldb_pagination_last-->
'num_tag_open' => ' ', //' '
'num_tag_close' => '', //''
'total_rows' => $numRows ));
$output .= '<div class="ldb_pagination">' . $p->create_links() . '</div>';
}
return($output);
} //end of function renderChunks //MattC
} //end of class LoopDBChunk //MattC
} //end of class test //Mattc
?>
Same caveat as above re: intelligent coding applies here too. As I never intended this to be a formal update to the code, I just tried to highlight which lines I worked on by appending my initials, rather than laying out a full set of comments. So this also deals with pagination in a way I needed to deal with.
Since I had some issues with pagination for a single page, as well as other formatting, here is the version of the pagination class which I used :
<?php
/**
* CodeIgniter
*
* An open source application development framework for PHP 4.3.2 or newer
*
*
* @author Rick Ellis. adopted to MODx by Amir Marcovitz
* @copyright Copyright (c) 2006, EllisLab, Inc.
* @license http://www.codeignitor.com/user_guide/license.html
* @link http://www.codeigniter.com
* @since Version 1.0
* @filesource
*/
// ------------------------------------------------------------------------
/**
* Pagination Class
*
*
*
* @category Pagination
* @author Rick Ellis. adopted to MODx by Amir Marcovitz
* @link http://www.codeigniter.com/user_guide/libraries/pagination.html
*/
class Pagination {
var $base_url = ''; // The page we are linking to
var $total_rows = ''; // Total number of items (database results)
var $per_page = 10; // Max number of items you want shown per page
var $num_links = 2; // Number of "digit" links to show before/after the currently viewed page
var $cur_item = 1;
var $first_link = '';//'‹ First';
var $next_link = '';//'>';
var $prev_link = '';//'<';
var $last_link = '';//'Last ›';
var $uri_segment = 3;
var $full_tag_open = '';
var $full_tag_close = '';
var $first_tag_open = '';
var $first_tag_close = '';//' ';
var $last_tag_open = '';//' ';
var $last_tag_close = '';
var $cur_tag_open = '';//'<span id="currentPage"> ';
var $cur_tag_close = '';//'</span>';
var $next_tag_open = '';//' ';
var $next_tag_close = '';//' ';
var $prev_tag_open = '';//' ';
var $prev_tag_close = '';
var $num_tag_open = '';//' ';
var $num_tag_close = '';
var $otherURLparameters = '';
/**
* Constructor
*
* @access public
* @param array initialization parameters
*/
function Pagination($params = array())
{
if (count($params) > 0)
{
$this->initialize($params);
}
}
// --------------------------------------------------------------------
/**
* Initialize Preferences
*
* @access public
* @param array initialization parameters
* @return void
*/
function initialize($params = array())
{
if (count($params) > 0)
{
foreach ($params as $key => $val)
{
if (isset($this->$key))
{
$this->$key = $val;
}
}
}
}
// --------------------------------------------------------------------
/**
* Generate the pagination links
*
* @access public
* @return string
*/
function create_links()
{
global $modx;
// If our item count or per-page total is zero there is no need to continue.
if ($this->total_rows == 0 OR $this->per_page == 0)
{
return '';
}
// Calculate the total number of pages
$num_pages = ceil($this->total_rows / $this->per_page);
$idInfo = $modx->getTemplateVar('id');
$curId = $idInfo['value'];
$aliasInfo = $modx->getTemplateVar('alias');
$curAlias = $aliasInfo['value'];
if ( ! is_numeric($this->cur_item))
{
$this->cur_item = 1;
}
// Is there only one page? Hm... nothing more to do here then.
if ($num_pages == 1)
{
return '';
}
// Is the page number beyond the result range?
// If so we show the last page
if ($this->cur_item > $this->total_rows)
{
$this->cur_item = ($num_pages - 1) * $this->per_page;
}
$uri_page_number = $this->cur_item;
$this->curPage = floor(($this->cur_item/$this->per_page) + 1);
// Calculate the start and end numbers. These determine
// which number to start and end the digit links with
$start = (($this->curPage - $this->num_links) > 0) ? $this->curPage - $this->num_links : 1;
$end = (($this->curPage + $this->num_links) < $num_pages) ? $this->curPage + $this->num_links : $num_pages;
// And here we go...
$output = '';
if ($this->curPage == 1) //MattC
{
if (empty($this->first_link))
{
$output .= '<span style="float:left;width:25%;text-align:left;">'.' '.'</span><span style="float:left;width:50%;text-align:center;">';//MattC
}
}
// Render the "First" link
if ($this->curPage > $this->num_links)
{
$output .= $this->first_tag_open.'<a href="' . $modx->makeUrl($curId, $curAlias, $this->otherURLparameters.'pagestart=1') . '">'.$this->first_link.'</a>'.$this->first_tag_close;//MattC
}
// Render the "previous" link
if (($this->curPage - $this->num_links) >= 0)
{
$i = $this->cur_item - $this->per_page;
if ($i == 0) $i = '';
$output .= $this->prev_tag_open.'<a href="'. $modx->makeUrl($curId, $curAlias, $this->otherURLparameters.'pagestart=' . $i ) .'">'.$this->prev_link.'</a>'.$this->prev_tag_close;
}
// Write the digit links
for ($loop = $start; $loop <= $end; $loop++)
{
$i = ($loop - 1) * $this->per_page + 1;
if ($i >= 0)
{
if ($this->curPage == $loop)
{
$output .= $this->cur_tag_open.$loop.$this->cur_tag_close; // Current page
}
else
{
$n = ($i == 0) ? '' : $i;
$output .= $this->num_tag_open . '<a href="'. $modx->makeUrl($curId, $curAlias, $this->otherURLparameters.'pagestart=' . $n ) . '">' . $loop.'</a>'.$this->num_tag_close;
}
}
}
if ($this->curPage == 1)//MattC
{
if (empty($this->first_link))
{
$output .= "</span>";
}
}
// Render the "next" link
if ($this->curPage < $num_pages)
{
$output .= $this->next_tag_open . '<a href="'. $modx->makeUrl($curId, $curAlias, $this->otherURLparameters.'pagestart=' . ($this->curPage * $this->per_page + 1)) . '">' .$this->next_link.'</a>'.$this->next_tag_close;
}
// Render the "Last" link
if ((($this->curPage + $this->num_links) < $num_pages) || ($this->curpage == $num_pages)) //MattC
{
$i = (($num_pages * $this->per_page) - $this->per_page + 1);
$output .= $this->last_tag_open . '<a href="'. $modx->makeUrl($curId, $curAlias, $this->otherURLparameters.'pagestart=' . $i) . '">' . $this->last_link.'</a>'.$this->last_tag_close;
}
// Kill double slashes. Note: Sometimes we can end up with a double slash
// in the penultimate link so we'll kill all double slashes.
$output = preg_replace("#([^:])//+#", "\\1/", $output);
// Add the wrapper HTML if exists
$output = $this->full_tag_open.$output.$this->full_tag_close;
return $output;
}
}
// END Pagination Class
?>
The transformFuncs code contains functions like this
//chunk call : [+t:fixInt:myintvar+] vs [+myintvar+]
function fixInt($value)
{
$temp = $value;
if ($temp != "10" && $temp != "20" && $temp != "30")
{
$temp = preg_replace("/0/","", ($temp));
}
return $temp;
}
And these functions would get called in a chunk meant to format a row from a database like this :
<div class="an_event_entry">
<p><b> [+t:fixMonth:month+] [+t:fixInt:day+], [+year+] - [+t:fixInt:hour+]:[+minute+][+ampm+][+t:endTime:hour_end:minute_end:ampm_end+]</b> [+event+], [+location+], [+description+] [+t:fmtPhone:phone+]<br /> <a href="[+link+]" target="_blank">[+link_name+]</a> </p>
</div>
Sorry for the length of this - hope it helps.
EDIT : BTW, this can work with either the MODx database or an external database. Just specify the "db" variables appropriately. And this is for Evo.
Matt
