<?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.
*/
$RecipeInfo['SelectQuery']['2.0b8'] = '2009-06-20';
// new connection standards - defaults
SDVA($SelectQuerySettings, array(
	'database' => '',
	'Seperator' => '.',
  'TableID' => '',
  'FieldID' => '`'
));

SDVA($SQofflimits, array('passwd')); //columns that cannot be queried

Markup('selectquery', 'fulltext', "/^\(: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 SelectQuery($params) {
	global $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 ($param['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 difined 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)) {
				$value = "'" . mysql_real_escape_string($value) . "'";
			}
	 		//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 ".$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);
		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 />";
			if ($rows < 1) return $out;
		 
			//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 />';

 	return $out;
} //end of function

function DatabaseField($fieldname) {
 global $SQdata;
 //return str_replace("\n","[[<<]]",$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 ($value+0 !== $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;
}