<?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.
****
version 3.7: updated for php7 - Pete Kay pkay42@gmail.com
*/


$RecipeInfo['SelectQuery']['3.7'] = '2017-08-07';
// 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*$/", function ($m) { return SelectQuery($m[1]);});

Markup('databasefield', 'inline', "/{`(.*?)`}/", function ($m) { return DatabaseField($m[1]); });

Markup('ternarylogic', 'directives', "/{\((.*?) \? (.*?) : (.*?)\)}/", function ($m) { return TernaryLogic($m[1],$m[2],$m[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;
}