<?php if (!defined('PmWiki')) exit();
/**
* PmWiki module to run a MySQL SELECT query based on info provided as parameters
* in the (:selectquery:) tag or via a form.
* Versions prior to 1.3 Written for Permaculture Collaborative,
* Copyleft June 2006, 23 February 2007 by Ben Stallings <ben@interdependentweb.com>
* Version 1.4 : AS clause in linked colums codded by Guy Moreau <gmyx@gpws.ca>
* Version 2.0 Beta 1, March 29th 2007, by Guy Moreau <gmyx@gpws.ca>.
* * Added display options:
* * norowcount: do not display the number of selected rows
* * noheaders: do not display the header row
* * div: display using divs instead of tables (example of use is with single column data)
* * debug: allows to output the select string for debug purposes
* * Converted to database standard
* * Fixed a bug with fields that were both in the columns list and linked fields not showing
* Current TODO:
* * currently cannot handle some fully qualified and some not fully qualified when dealing with links
* Version 2.0 Beta 2, April 9th, 2007, by Guy Moreau <gmyx@gpws.ca>
* * Attemp at backwards compatibility: uses the 1.x constants and maps them to a DB standard offering
* * Added RecipeInfo
* * Added pagination variables and markup - as request by GhostRider.
* Version 2.0 Beta 3, April 3rd, 2007, by Guy Moreau <gmyx@gpws.ca>
* * Major rewrite of code to fix fully qualified names
* * Bug fix: SelectQuery will not display columns that are fully qualified
* * Multiple parameters can be added to the link array
* * Made $SQofflimits a SDVA array so that it can be configured in config.php as per Ben's request
* * Output is in strait HTML - faster processing!
* Version 2.0 Beta 4, July 06, 2007 by Guy Moreau <gmyx@gpws.ca>
* * Bug fix: An mysql statement was left in the error code. Converted to adodb.
* * Bug fix: A string literal in the link statement would cause recipy to fail
* Version 2.0 Beta 5, January 27th, 2008 by Guy Moerau <gmyx@gpws.ca>
* * Bug Fix: 'as' statement was not being honered in column headers
* * Clean up some code in the query row section - redundent access to field data
* * Bug Fix: On some installs, results were always blank.
* Version 2.0 build 6, March 18th, 2008 by Guy Moreau <gmyx@gpws.ca>
* * No more beta, just builds.
* * Added 'type' parameter to match/against pair to use like instead of full text search
* Version 2.0 build 7, December 21, 2008 by Guy Moreau <gmyx@gpws.ca>
* * New variables:
* * * $SQDelim incase the period is something else
* * * $SQFieldId incase the DB uses something else than '`'
* * Bug Fix: backtics causes the parser to ignore fields names
* Version 2.0 Build 8, June 20, 2009 by Guy moreau <gmyx@gpws.ca>
* * Changed Variables intoduced in b7 into the $SelectQuerySettings[] array since it didn't work
* * * $SQDelim becomes 'Seperator', defaults to '.'
* * * $SQFieldID becomes 'FieldId', defaults to '`'
* * Added Varibalbe 'TableId' to $SelectQuerySettings[], default none
* * By Request: New paramerter to select run-time the connection for multiple connections
* * * connection = "" selects the array with the same name in config.php to use
* * Bug Fix: Empty FieldID causes warnings has been fixed.
* future TODO:
* * examine possible table enhancements
* * examine possible page id overloads
* * format pagination section
* * Get rid of the custom markup for {`variables`} and (conditionals), using $FmtPV and $Conditionals instead.
* * one mysql statement remains, but in an obsure section of the code. Need to find a suitable replacement
* * after 2.0 release, add id to create more accessible tables
* * add a config variable to define wildcard value
*
* * Database constants (DB_*) must be defined in config.php or here. (applies to version 1.4 and earlier only or version 2.0 beta 2 and later)
* OR the Database standard must be installed and configured for version 2.0 beta 1 or later.
****************
10/05/2013 Added by VG:
Added display option pmtable, which instead of html output uses the pmwiki simple table syntax. Currently there is no justification option, text is left (default) justified, and numeric data right justified. This can be changed by adding an option and improving the sq_reformat proceedure, which actually takes the lines, puts in the || and appropriate spacing for the automatic format.
Added nodata="message text" for message to be displayed when the requested data set is empty. This is quite different from an error condition.
Got rid of the mysql statement (it was mysql_real_escape_string ) replaced by the adodb function qstr.
*/
$RecipeInfo['SelectQuery']['3.0'] = '2013-09-20';
// new connection standards - defaults
SDVA($SelectQuerySettings, array(
'database' => '',
'Seperator' => '.',
'TableID' => '',
'FieldID' => '`'
));
SDVA($SQofflimits, array('passwd')); //columns that cannot be queried
Markup('selectquery', '>if', "/^\(:selectquery[ ]*(.*?):\)\s*$/e", "SelectQuery(PSS('$1'))");
Markup('databasefield', 'inline', "/{`(.*?)`}/e", "DatabaseField('$1')");
Markup('ternarylogic', 'directives', "/{\((.*?) \? (.*?) : (.*?)\)}/e", "TernaryLogic('$1','$2','$3')");
if (!is_array($SQdata)) $SQdata = array();
SDVA($SQdata,$_REQUEST);
/*SDVA ($SelectQuerySettings = array(
'Seperator' => '.',
'TableID' => '',
'FieldID' => '`'));*/
# Function to convert HTML lines to pmwiki simple table format.
function sq_reformat($line) {
$aline = $line . "||";
# $aline = preg_replace('/([a-zA-Z)])\|\|/','$1 ||', $aline);
$aline = preg_replace('/\|\|([0-9]+)\|\|/','|| $1||', $aline);
$aline = str_replace("\n"," ", $aline);
return($aline);
}
function SelectQuery($params) {
global $FmtPV,$SQofflimits, $SQdata, $UpdateUserID, $UpdateUsername, $SelectQuerySettings, $DB;
// combine parameters passed in the (:selectquery:) tag with those submitted in a form
$params = ParseArgs($params);
SDVA($params,$SQdata);
// check for run-time override
if ($params['connection'] == '') {
//check for backwards compatible upgrade to DBStandard
if ($SelectQuerySettings['database'] == '') {
// check if the constants are defined
if (defined('DB_SERVER')) {
// first constant defined - try to map to the db standards
foreach ($Databases as $DBSName => $DBSItem) {
//check it's properties
if ($DBSItem['hostname'] == DB_SERVER &&
$DBSItem['database'] == DB_NAME &&
$DBSItem['username'] == DB_USER &&
$DBSItem['password'] == DB_PASS) {
// we've got a match!
$DBToUse = $DBSName;
}
}
//if all constantd defined and still no match - we can add it ourselves
if ($DBSItem = '' && defined('DB_NAME') && defined('DB_USER') && defined('DB_PASS')) {
// we can go ahead and add it to the standard
$Databases['SelectQueryContantsConverted'] = array(
'driver' => 'mysql',
'hostname' => DB_SERVER,
'database' => DB_NAME,
'username' => DB_USER,
'password' => DB_PASS);
$DBToUse ='SelectQueryContantsConverted';
}
}
} else {
//print 'DBS name: '.$SelectQuerySettings['database'].'<br />';
$DBToUse = $SelectQuerySettings['database'];
}
} else {
//connection override
$DBToUse = $Databases[$params['connection']];
}
//check to see if we have a valid source
if ($DBToUse=='') {
die("No connection strings provided to SelectQuery. Please either configure the 1.x Constants or the \$SelectQuerySettings['database'] variable and the database standard");
}
// where statements may contain characters that get escaped or translated by the wiki
$where = html_entity_decode(stripslashes($params['where']));
//set up array of display args
$display = explode(',',$params['display']);
//output if debug the params variable
if (in_array('debug', $display)) {
print "SelectQuery Version: 2.0 Build 8, June 20th, 2009<br />";
foreach ($SelectQuerySettings as $key => $value) {
print "delims key: '".$key."' value: ".$value."<br />";
}
foreach ($params as $key => $value) {
print "params key: '".$key."' value: ".$value."<br />";
}
}
// ensure the Database Standard is loaded
include_once("adodb-connect.php");
// Connect to Database
$dblink = ADOdbConnect($DBToUse);
if ($dblink !== TRUE) return $dblink;
//handle additional parameters submitted as options
foreach (explode(',', $params['options']) as $option) {
if ($option>'') {
$shortname = (strpos($option, '.') ? substr($option, (strpos($option, '.')+1)) : $option);
//check each parameter for validity and add quotes if necessary
if ($option == $UpdateUserID) $value = $UpdateUsername;
else $value = $params[$shortname];
if (get_magic_quotes_gpc()) {
$value = stripslashes($value);
}
if (!is_numeric($value)) {
// One of these should work:
//here is the call to replace
// $value = "'" . mysql_real_escape_string($value ) . "'";
//Note that qstr adds quotes around everything, so we do not have to:
$value = $DB[$DBToUse] -> qstr($value, get_magic_quotes_gpc());
}
//append parameter to the query
$where = "$option = $value". ($where>"" ? " AND $where" : "");
}
}
//add in 'match' and 'against' parameters
if (($params['match']>'') and ($params['against']>'')) {
$against = $_REQUEST[$params['against']];
if ($params['type']=='like' || $against == "" || $against = "*" || strpos($against,'*') == 0)
{
if ($params['type']=='like' && strpos($against,'*') <= 0)
{
//append the % sings
$like = "%" . $_REQUEST[$params['against']] . "%";
}
else
{
$like = str_replace("*","%",$_REQUEST[$params['against']]);
}
//do a LIKE search
$where = "(".str_replace(","," LIKE '$like' OR ",$params['match'])." LIKE '$like')". ($where>"" ? " AND $where" : "");
} else { //do a fulltext search*/
$where = "MATCH (".$params['match'].") AGAINST ('"
. ($_REQUEST[$params['against']] ? $_REQUEST[$params['against']] : $params['against'])
. "' IN BOOLEAN MODE)". ($where>"" ? " AND $where" : "");
}
}
if (($params['columns']>'') and ($params['tables']>'') and ($where>'')) { //ready to go
// check for semicolons, which may indicate a hacking attempt
if ((strpos($params['columns'],';')) or (strpos($params['tables'],';')) or (strpos($where,';'))) {
$out = "%red%Please do not attempt to send multiple SQL statements using this program.";
return $out;
}
//check for wildcards * and ? which cannot work
if ((strpos($params['columns'], '*')) or (strpos($params['columns'], '?')) or (strpos($params['columns'], '%'))) {
$out = "%red%Wildcards (*, ? and %) are not supported.";
return $out;
}
//check for forbidden columns
$badcolumn = 0;
foreach ($SQofflimits as $offlimit) {
$offlimit = strtolower($offlimit);
if ((in_array($offlimit,explode(',', $params['columns']))) or (strpos(strtolower($params['link']),$offlimit))) {$badcolumn++;}
}
if ($badcolumn>0) {
$out = "%red%One or more columns you have requested are off limits to this program.";
return $out;
}
//create a temporary array to hold links - it will get integrated into columns array
$linkparams = explode(';', $params['link']);
//create a colums array
/* array format
columns (
columnid (
field (
table
name
as
isliteral //boolean - can only be false here
qutoed //boolean - it "`" needs to be filtered
)
link (
field (
table
name
as
isliteral //boolean
qutoed //boolean - it "`" needs to be filtered
)
target //can be anything
paramscount
params(
param (
table
name
as
isliteral //boolean - can only be false here
qutoed //boolean - it "`" needs to be filtered
)
)
)
display //boolean
)
)
*/
$columns=array();
$columncount=0;
$querycolumns=array();
foreach (explode(',',$params['columns']) as $col) {
//get the table name - if any
$table = (strpos($col, $SelectQuerySettings['Seperator']) ? substr($col, 0, (strpos($col, $SelectQuerySettings['Seperator']))) : '');
//sepearete the table from the name ( as )
$name = (strpos($col, $SelectQuerySettings['Seperator']) ? substr($col, (strpos($col, $SelectQuerySettings['Seperator'])+1)) : $col);
//check for as clause
if (strpos(strtolower($name), ' as ')) {
//strip the name from the as clause - first seperate the colon
$as = substr($name, strpos($name, ' as ') + 4);
$name = substr($name, 0, strpos($name, ' as '));
} else {
//get the column name- no as clause
$as = '';
}
//check for field filter
if (in_array('debug', $display)) {
print 'Testing for field id ' . $SelectQuerySettings['FieldID'] . ' in field: ' . $name . '; ';
if ($SelectQuerySettings['FieldID'] != '') {
if (strpos($name, $SelectQuerySettings['FieldID']) === false) {
print 'Not found.';
} else {
print 'Found at ' . strpos($name, $SelectQuerySettings['FieldID']) . '. Real Name: ' . substr($name, 1, strlen($name) - 2);
}
}
print '<br />';
}
if ($SelectQuerySettings['FieldID'] != '') {
if (strpos($name, $SelectQuerySettings['FieldID']) === false) {
//no field id
$quoted = false;
} else {
//filter it out - we will include it later
$name = substr($name, 1, strlen($name) - 2);
$quoted = true;
}
} else {
$quoted = false;
}
//create the field array
$field = array(
'isliteral' => false,
'table' => $table,
'name' => $name,
'as' => $as,
'quoted' => $quoted);
//save the values
array_push($columns,1,array(
'field' => $field,
'display' => true));
//add it to the query columns array
$querycolumns = AddToArray($querycolumns, $field);
//add to column count
$columncount++;
} //end of foreach
//process all links
if ($params['link']>'') {
for ($lid = 0; $lid < count($linkparams); $lid++) {
$param = explode(',', $linkparams[$lid]);
//check for string literal as name
if (strrpos($param[0], "'") !== false) {
//need to check for a 'as' parameter'
//create field and append to query columns
$field = array (
'isliteral' => true,
'table' => '',
'name' => substr($param[0],1,strlen($param[0]) -2),
'as' => '',
'quoted' => false);
} else {
//check for fully qualified name in target paramerter
$fqt = (strpos($param[0], $SelectQuerySettings['Seperator']) ? substr($param[0], 0, (strpos($param[0], $SelectQuerySettings['Seperator']))) : '');
$fqn = (strpos($param[0], $SelectQuerySettings['Seperator']) ? substr($param[0], (strpos($param[0], $SelectQuerySettings['Seperator'])+1)) : $param[0]);
//check for as clause in target aurgument
$fqas = ((strpos(strtolower($fqn), ' as ') ? substr($fqn, strpos($fqn, ' as ') + 4) : ''));
$fqn = ((strpos(strtolower($fqn), ' as ') ? substr($fqn, 0, strpos($fqn, ' as ')) : $fqn));
//create field and append to query columns
$field = array (
'isliteral' => false,
'table' => $fqt,
'name' => $fqn,
'as' => $fqas,
'quoted' => false);
}
//append to query columns
$querycolumns = AddToArray($querycolumns, $field);
//build params array
$linkarray = array();
for ($lpaid = 2; $lpaid < count($param); $lpaid ++) {
$lpaname = $param[$lpaid];
//now split between table, name
$lpatable = (strpos($lpaname, $SelectQuerySettings['Seperator']) ? substr($lpaname, 0, (strpos($lpaname, $SelectQuerySettings['Seperator']))) : '');
$lpaname = (strpos($lpaname, $SelectQuerySettings['Seperator']) ? substr($lpaname, (strpos($lpaname, $SelectQuerySettings['Seperator'])+1)) : $lpaname);
//split between name and as
$lpaas = (strpos($lpaname, ' as ') ? substr($lpaname, strpos($lpaname, ' as ') + 4) : '');
$lpaname = (strpos($lpaname, ' as ') ? substr($lpaname, 0, strpos($lpaname,' as ')) : $lpaname);
//check for backticks
if ($SelectQuerySettings['FieldID'] != '') {
if (strpos($lpaname, $SelectQuerySettings['FieldID']) !== false) {
$lpaname = substr($lpaname, 1, strlen($lpaname) - 2);
$lpaquoted = true;
} else {
$lpaquoted = false;
}
} else {
$lpaquoted = false;
}
//create it's field array
$lpafield = array(
'isliteral' => false,
'table' => $lpatable,
'name' => $lpaname,
'as' => $lpaas,
'quoted' => $lpaquoted);
$linkarray[$lpaid-2] = $lpafield;
//append to query columns
$querycolumns = AddToArray($querycolumns, $lpafield);
}
//build link array
$link = array(
'field' => $field,
'target' => $param[1],
'paramscount' => count($param) - 2,
'params' => $linkarray);
//check for need to include in a columns' link parameter or create new column
$linkfound = false;
foreach ($columns as $id => $col) {
//get the field
$colfield = $col['field'];
if (in_array('debug', $display)) print 'checking: '.$colfield['name']. ' vs ' .$field['name'].'<br />';
if ($colfield['table'] == $field['table'] && $colfield['name'] == $field['name']) {
//append the link to this column
$columns[$id]['link'] = $link;
//set flag to true
$linkfound = true;
if (in_array('debug', $display)) print 'linking: '.$field['name'].'<br />';
}
}
//if not found add it here as display = false
if ($linkfound == false) {
if (strrpos($param[0], "'") !== false) {
//add it visible!
array_push($columns,1,array(
'field' => $field,
'display' => true,
'link' => $link));
//add to column count
$columncount++;
} else {
//add it!
array_push($columns,1,array(
'field' => $field,
'display' => false,
'link' => $link));
//add to column count
$columncount++;
}
}
}
}
//prepare the tables and colums for the query
foreach ($querycolumns as $col) {
// add this column to the queried columns
if ($col['name'] > '') {
if (in_array('debug', $display)) {
print 'Processing : ' . $col['name'] .' and isliteral:'. $col['isliteral']. '<br />';
}
if ($col['isliteral'] == false) {
$qcolumns .= ($qcolumns>'' ? ', ' : '') . ($col['table']>'' ? $col['table'] . $SelectQuerySettings['Seperator'] : '') . $SelectQuerySettings['FieldId'] . $col['name'] . $SelectQuerySettings['FieldID'];
} else {
$qcolumns .= ($qcolumns>'' ? ', ' : '') . "'" . $col['name'] . "'";
}
}
/*foreach ($col as $key => $colitem){
print 'Processing: (' . $key . ') ' . $colitem . '<br />';
} */
}
//so run the query already
$query = "SELECT DISTINCT ".$qcolumns
. " FROM " . $SelectQuerySettings['TableID'] . $params['tables'] . $SelectQuerySettings['TableID'] . " WHERE $where"
. ($params['order']>'' ? " ORDER BY ".$params['order'] : "")
. ($params['limit']>'' ? " LIMIT ".$params['limit'] : "");
//check for debug
if (in_array('debug', $display)) print 'query: '.$query.'<br />';
//ensure we use associative RSs
$DB[$DBToUse]->SetFetchMode(ADODB_FETCH_ASSOC);
$queryd = $DB[$DBToUse]->Execute($query);
############### Here is the query
if ($queryd) {
//display number of rows found
$rows = $queryd->RecordCount() ;
if (!in_array('custom', $display) && !in_array('norowcount', $display)) $out = "$rows rows selected.<br /><br />";
################
##Set a variable for number of rows
################
$FmtPV['$RowCount'] = "$rows";
###########
###Now lets see what we can do with no data.
if ($rows < 1){
if (isset($params['nodata']) ) {
$nodatamsga = $params['nodata'];
if (is_array($nodatamsga)) {$nodatamsg = implode(' ', $nodatamsga);}
else $nodatamsg = $nodatamsga;
$out = $nodatamsg;
$out .= "<br>";
return $out; }}
##################
##Here is the pmtable stuff
##################
if (in_array('pmqtable', $display)) {
$total = $queryd->GetArray($rows);
$totfields = array_keys($total[0]);
$totvalues = array_map(array_values,$total);
$totarray = array_map("sq_reformat",$totvalues);
$out = "";
if (!in_array('noheader',$display)) {
$realhdr = "||!".implode(" ||!",$totfields)." ||\n";
$out .= $realhdr; }
$out .= implode("\n", $totarray);
return($out);}
##### Return now to the original selectquery stuff.
//create page variables and conditionals that can be used in the page
SDVA($SQdata,$queryd->GetAssoc());
//$FmtPV("SQData") = $queryd->GetAssoc();
$queryd->MoveFirst(); //reset the row counter
if (in_array('custom', $display)) return; //skip displaying the table
//display the table or div depending on display
if (!in_array('div', $display)) {
$out .= '<table class="selectquery">';
} else {
$out .= '<div class="selectquery">';
}
//check if headers should show
if (!in_array('noheader', $display)) {
//insert the headers that are not hidden
if (!in_array('div', $display)) {
$out .= '<tr>';
} else {
$out .= '<div class="selectqueryrow">';
}
//ensure we are allowed to display
for ($count=0;$count<$columncount;$count++) {
$intcount = ($count * 2) + 1;
if ($columns[$intcount]['display']==true) {
//check display method
if ($columns[$intcount]['field']['as'] > '') {
if (!in_array('div', $display)) {
//table
$out .='<th>'. $columns[$intcount]['field']['as'].'</th>';
} else {
//divs
$out .= '<div class="selectqueryheaders">'.$columns[$intcount]['field']['as'].'</div>';
}
} else {
if (!in_array('div', $display)) {
//table
$out .='<th>'. $columns[$intcount]['field']['name'].'</th>';
} else {
//divs
$out .= '<div class="selectqueryheaders">'.$columns[$intcount]['field']['name'].'</div>';
}
}
}
}
if (!in_array('div', $display)) {
$out .= '</tr>';
} else {
$out .= '</div>';
}
}
//display the rows
$DoLoop = true;
//check for pagination
if ($params['count']>'') {
//check for page info
if ($params['SCPage']>'') {
//move the first record to the current page
$queryd->Move(($params['SCPage'] - 1) * $params['count']);
} //else first page
}
while (!$queryd->EOF && $DoLoop == true) {
//start the row
//check display method
if (!in_array('div', $display)) {
//table
$out .= '<tr>';
} else {
//div
$out .= '<div class="selectqueryrow">';
}
//do all the fields (colums)
for ($i=0;$i < $queryd->FieldCount();$i++) {
$intcount = ($i * 2) + 1;
//ensure we are allowed to display
//$field = $queryd->FetchField($i);
//$fname = $field->name;
if ($columns[$intcount]['display'] == true) {
//we can display
//wikify line breaks
//$data = str_replace("\n","<br />",$queryd->Fields($i));
$data = str_replace("\n","<br />",$queryd->Fields($columns[$intcount]['field']['name']));
//create cell
if (!in_array('div', $display)) {
//table
$out .= '<td>';
} else {
//div
$out .= '<div class="selectquerycell">';
}
if ($columns[$intcount]['link']>'') {
$link = $columns[$intcount]['link'];
//start link
$linkdata = '[['.$link['target'];
for ($linkcount=0; $linkcount<$link['paramscount'];$linkcount++) {
$fieldname = ($link['params'][$linkcount]['tale']>''? $link['params'][$linkcount]['table'].'.'.$link['params'][$linkcount]['name'] : $link['params'][$linkcount]['name']);
//check for as clause in param
if ($link['params'][$linkcount]['as'] > '') {
//link column with as clause
$linkdata .= '?'.$link['params'][$linkcount]['as'].'='
. urlencode($queryd->Fields($fieldname));
} else {
//link column - no as clause
$linkdata .= '?'.$link['params'][$linkcount]['name'].'='
. urlencode($queryd->Fields($fieldname));
}
}
//finish link
$data = $linkdata . ' | ' .$data . ']]';
}
//right-justify numbers, left-justify text
//$out .= (is_numeric($data) ? ' '.$data : $data.' ');
$out .= $data;
//determine terimnator
if (!in_array('div', $display)) {
//table
$out .= '</td>';
} else {
//divs
$out .= '</div>';
}
} //end of if clause
} //end of "for field"
//close row
if (!in_array('div', $display)) {
$out .= '</tr>';
} else {
//close the group
$out .= '</div>';
}
//check if we move on or if we have reached out page limit
if ($params['count']>'') {
$RowCount++;
if ($RowCount == $params['count']) {
//that's it folks!
$DoLoop = false;
} else {
//move to next row
$queryd->MoveNext();
}
} else {
//move to next row
$queryd->MoveNext();
}
} //end of "while eof"
//if divs - ensure all closed
if (!in_array('div', $display)) {
//close the table
$out .= '</table>';
} else {
//close the group
$out .= '</div>';
}
//finish pagination
if ($params['count']>'') {
//add pagination fields here
//first
$out.=chr(10).'[[{$Name}?SCPage=1|First]] ';
//previous -- if not on page 1
if ($params['SCPage']==1){
$out.='| Previous ';
} else {
$out.='| [[{$Name}?SCPage='.($params['SCPage'] - 1).'|Previous]] ';
}
//determine total number of pages
$TotalPages = ceil($rows / $params['count']);
//page# of #
if ($params['SCPage'] == '') {
$out .= '| 1 of '.$TotalPages.' ';
} else {
$out .= '| '.$params['SCPage'].' of '.$TotalPages.' ';
}
//next
if ($params['SCPage'] == $TotalPages) {
$out .= '| Next ';
} else {
$out .= '| [[{$Name}?SCPage='.($params['SCPage'] + 1).'|Next]] ';
}
//last
$out .= '| [[{$Name}?SCPage='.$TotalPages.'|Last]]';
}
} else { //query didn't work
//$out = "%red%$query\\\\\n".mysql_error();
$out = "%red%$query\\\\\n".$DB[$DBToUse]->ErrorMsg();
}
} //end of "if all parameters present"
//debug
if (in_array('debug', $display)) print 'out: '.htmlentities($out,ENT_NOQUOTES) .'<br />';
##############
##Now lets fix for pmtables
if (in_array('pmtable', $display)) {
$pmout = str_replace('<table class="selectquery">','',$out);
$pmout = str_replace('<tr><th>','||!',$pmout);
$pmout = str_replace('<th>','||!',$pmout);
$pmout = str_replace('<tr><td>', '||', $pmout);
$pmout = str_replace('</td><td>','||',$pmout);
$pmout = explode("</tr>", $pmout); # Convert to individual lines
array_pop($pmout);
$pmout = array_map("sq_reformat",$pmout);
$out = implode("\n",$pmout);
}
return $out;
} //end of function
function DatabaseField($fieldname) {
global $SQdata;
$out = "Entering Databasefield -- Name -- $fieldnname";
# $sss = serialize($SQdata);
# $out .= $sss . "<br>";
# $out .= "Output: ".$SQdata[$fieldname];
# $out .= "<br>";
# return($out);
//return str_replace("\n","[[<<]]",$SQdata[$fieldname]);
echo "Output is $SQdata[$fieldname]";
return nl2br($SQdata[$fieldname]);
}
function TernaryLogic($if,$then,$else) {
global $SQdata;
//return print_r($SQdata,true);
if (!isset($SQdata)) return;
$if = html_entity_decode($if); //turn $lt; back to < so it evaluates properly
//put parentheses around conditionals separated by " and "
$ifarray = explode(" and ",$if);
if (count($ifarray)>1) {
for ($i=0;$i<count($ifarray);$i++) {
$ifarray[$i] = '('.$ifarray[$i].')';
}
$if = implode(" and ",$ifarray);
}
foreach ($SQdata as $key => $value) { //substitute values for variables
$key = '`'.$key.'`';
if (! is_numeric($value)) {$ifvalue = "'$value'";} else {$ifvalue = $value;}
$if = str_replace($key,$ifvalue,$if);
$then = str_replace($key,$value,$then);
$else = str_replace($key,$value,$else);
}
//uncomment to debug
//return "if $if then $then else $else";
//return "\$if = ($if);";
//evaluate ternary logic
eval("\$if = ($if);");
return ($if ? $then : $else);
}
function AddToArray($SourceArray, $ItemToAdd) {
foreach ($SourceArray as $ArrayItem) {
// as clause is ignored - handled internaly by SelectQuery and not by SQL
if ($ArrayItem['table'] == $ItemToAdd['table'] && $ArrayItem['name'] == $ItemToAdd['name']){
//already in list
return $SourceArray;
} else if ($ArrayItem['table'] == '' && $ItemToAdd['table'] == '' && $ArrayItem['name'] == $ItemToAdd['name']){
//already in list
return $SourceArray;
}
}
//not in list, add it
array_push($SourceArray, 1, $ItemToAdd);
return $SourceArray;
}