* Version 1.4 : AS clause in linked colums codded by Guy Moreau * Version 2.0 Beta 1, March 29th 2007, by Guy Moreau . * * 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 * * 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 * * 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'].'
'; $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
"; foreach ($params as $key => $value) { print "params key: '".$key."' value: ".$value."
"; } } // 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'] .'
'; } $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.'
'; $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.

"; 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 .= ''; } else { $out .= '
'; } //check if headers should show if (!in_array('noheader', $display)) { //insert the headers that are not hidden if (!in_array('div', $display)) { $out .= '
'; } else { $out .= '
'; } //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 .='
'; } else { //divs $out .= '
'.$columns[$intcount]['field']['name'].'
'; } } } if (!in_array('div', $display)) { $out .= ''; } else { $out .= ''; } } //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 .= ''; } else { //div $out .= '
'; } //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","
",$queryd->fields($i)); //create cell if (!in_array('div', $display)) { //table $out .= '
'; } else { //divs $out .= ''; } } //end of if clause } //end of "for field" //close row if (!in_array('div', $display)) { $out .= ''; } else { //close the group $out .= ''; } //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 .= '
'. $columns[$intcount]['field']['name'].'
'; } else { //div $out .= '
'; } 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 .= '
'; } else { //close the group $out .= ''; } //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) .'
'; 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 $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; }