<?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!
* 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.
* 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.0b3'] = '2007-04-23';
// new connection standards - defaults
SDVA($SelectQuerySettings, array(
'database' => ''
));
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);
function SelectQuery($params) {
global $SQofflimits, $SQdata, $UpdateUserID, $UpdateUsername, $SelectQuerySettings, $DB;
//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'];
}
//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");
}
// combine parameters passed in the (:selectquery:) tag with those submitted in a form
$params = ParseArgs($params);
SDVA($params,$SQdata);
// 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 Beta 3<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($SelectQuerySettings['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 ($against == "") $against = "*";
if (strpos($against,'*')=== 0) {
//do a LIKE search
$like = str_replace("*","%",$_REQUEST[$params['against']]);
$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 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
)
link (
field (
table
name
as
)
target //can be anything
paramscount
params(
param (
table
name
as
)
)
)
display //boolean
)
)
*/
$columns=array();
$columncount=0;
$querycolumns=array();
foreach (explode(',',$params['columns']) as $col) {
//get the table name - if any
$table = (strpos($col, '.') ? substr($col, 0, (strpos($col, '.'))) : '');
//sepearete the table from the name ( as )
$name = (strpos($col, '.') ? substr($col, (strpos($col, '.')+1)) : $col);
//check for as clause
if (strpos(strtolower($name), ' as ')) {
//slip 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 = '';
}
//create the field array
$field = array(
'table' => $table,
'name' => $name,
'as' => $as);
//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 fully qualified name in target paramerter
$fqt = (strpos($param[0], '.') ? substr($param[0], 0, (strpos($param[0], '.'))) : '');
$fqn = (strpos($param[0], '.') ? substr($param[0], (strpos($param[0], '.')+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 (
'table' => $fqt,
'name' => $fqn,
'as' => $fqas);
//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, '.') ? substr($lpaname, 0, (strpos($lpaname, '.'))) : '');
$lpaname = (strpos($lpaname, '.') ? substr($lpaname, (strpos($lpaname, '.')+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);
//create it's field array
$lpafield = array(
'table' => $lpatable,
'name' => $lpaname,
'as' => $lpaas);
$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 ($colfield['table'] == $field['table'] && $colfield['name'] == $field['name']) {
//appent the link to this column
$columns[$id]['link'] = $link;
//set flag to true
$linkfound = true;
}
}
//if not found add it here as display = false
if ($linkfound == false) {
//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'] .'<br />';
}
$qcolumns .= ($qcolumns>'' ? ', ' : '') . ($col['table']>'' ? $col['table'] . '.' : '') . $col['name'];
}
}
//so run the query already
$query = "SELECT ".$qcolumns
. " FROM ".$params['tables']." 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 />';
$queryd = $DB[$SelectQuerySettings['database']]->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());
$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 (!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));
//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();
}
} //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;
}
}
//not in list, add it
array_push($SourceArray, 1, $ItemToAdd);
return $SourceArray;
}