• View
• Edit
• History
• Attach
• Print
• Backlinks

SelectQuery

Questions answered by this recipe

• How can I empower my users to embed simple database queries in wiki pages without allowing them full access to the database?
• How can I provide my users with a sortable index of information complete with links to other pages?

Description

SelectQuery allows access only to the MySQL SELECT command, with only valid parameters included, and with specified fields (such as passwords or personal info) excluded. Output of the query can be formatted in a variety of ways, and the cells in one or more columns can be made into links to pass parameters to other pages.

Please note: SelectQuery versions 2 and 3 use the ADOdb abstraction layer to allow use of any supported database. SelectQuery version 4 uses PDO instead. As a result, some database types are no longer supported by this recipe. Please consult the lists of supported database types and choose the version suited to your needs.

Files

Version 1.x (does not use ADOdb): selectquery_v1_4.phpΔ
Version 2 (Build 8, June 20th, 2009): selectquery.phpΔ
Version 3.0 (September 20, 2013): selectquery3.phpΔ

Version 4.0 (December 13, 2017): selectquery4.0.phpΔ - PDO instead of ADOdb

New Installation

1. Upload the selectquery4.0.phpΔ file into your cookbook directory
   • Older versions of PHP might need to use older versions - see above
2. To prevent access to fields that are off limits, define $SQofflimits as an array of fields you don't want displayed (by default 'passwd' is off limits).
3. If using version 2 or 3: download, install and configure the Database Standard
4. Define the $Databases and $SelectQuerySettings variables in config.php as described below.
5. Add include "$FarmD/cookbook/selectquery4.0.php" in your config.php. To be extra safe, you may want to include it only when displaying pages that require a password for editing, for example:

   if (FmtPageName('$Group',$pagename) == 'Administration') {
    include "$FarmD/cookbook/selectquery4.0.php";
   }

Settings

The $Databases variable is configured the same way as in DatabaseStandard.

$SelectQuerySettings should be an array with the following keys, only the first of which is mandatory:

• database (the connection_name in $Databases that you wish to use with this recipe)
• Separator (. default value)
• FieldID (` default value)
• TableID (no default value)

Separator

Used to separate a table name from a field name, when specifying both. In MySQL, this is done with a period, for example Table.Field, and accordingly the default value for this setting is a period.

FieldID

Used to indicate the name of a field that might otherwise be misunderstood by the database to mean something else. For example, `delete`. In MySQL, this is done with `backticks`, and that is the default.

TableID

Similar to the FieldID but not usually necessary.

Usage

Three parameters are required for the query to execute: columns, tables, and where. These can be defined either inside the (:selectquery:) tag, or in a form, or a combination of the two. For example:

(:selectquery columns="firstname,lastname,email" tables=people where="email like '%@%'":)

will run immediately when the page is viewed, while

 (:selectquery columns="firstname,lastname,email" tables=people:)
 (:input form:)
 (:input radio where value="lastname IS NOT NULL":) valid last name
 (:input radio where value="email LIKE '%@%'":) valid email
 (:input submit value="Run Query":)
 (:input end:)

will wait to run until the form is submitted. These three variables are checked for the presence of semicolons, which may indicate someone is attempting to sneak in an extra SQL command, or may just mean they made a mistake in typing! I recommend defining the tables and columns within the selectquery tag whenever possible so that they aren't visible in the HTML code.

The where parameter can also be built from form inputs using the options parameter. This is useful if the search criteria are coming from another page, or for making search forms like this:

 (:selectquery columns=firstname,lastname,email tables=people options=lastname:)
 (:input form:)
 Last name to search for: (:input text lastname:)
 (:input submit value="Run Query":)
 (:input end:)

The form above will only find exact matches of lastname. To search for a string of characters in one or more fields, use the match and against parameters, like this:

 (:selectquery columns=firstname,lastname,email tables=people 
match=firstname,lastname against=searchfor type=like:)
 (:input form:)
 Name to search for: (:input text searchfor:)
 (:input submit value="Run Query":)
 (:input end:)

If type=like is provided, as shown above, wildcards (%) are automatically added before and after the search string, so that for example "cali" will match "California" and "Mexicali" but also "supercalifragilisticexpialidocious." If type=like is omitted, you must have a fulltext index on the target fields in the database, or it won't work.

Joining multiple tables

For small amounts of data, you can get away with joining tables in the where parameter, like this:

(:selectquery columns=firstname,lastname,startdate,member_type,notes 
tables=people,members where="people.personid = members.personid":)

However, for larger datasets, you will get better performance if you specify the join more properly in the tables parameter instead, like this:

(:selectquery columns=firstname,lastname,startdate,member_type,notes 
tables="people JOIN members ON people.personid = members.personid" where=1:)

Customizing column labels

You can change the label on any column by specifying "as" (in upper or lowercase) in the columns parameter, like this (line break added for readability):

(:selectquery columns="firstname AS First,lastname AS Last,startdate AS 'Start Date',
member_type AS Type,m.notes AS Notes" tables="people p JOIN members m ON p.personid = m.personid" where=1:)

Notice that because "Start Date" has a space in it, we must put single quotes around it.

Additional parameters that are optional:

• connection="connection_name" (specify a different database than the one in $SelectQuerySettings)
• order="lastname,firstname" (sort the results)
• limit=10 (limit the number of results returned)
• limit="10,10" (display the second page of the results that were limited above)
• display (customize the output, see below)
• link (link results to other pages, see below)

Link parameter

The link parameter create a wiki link on a column. The format is 'field, destination, parameter'. This means that the linked field (which must be specified in the columns parameter) will be a linked to a destination page -- using standard wiki link formatting -- with another field's data passed as a parameter. (The parameter field must also be specified in the columns parameter.) You can create multiple links by separating them with a semicolon. This linking feature is especially useful in conjunction with UpdateForm.

Link Example 1:

(:selectquery columns=personid,firstname,lastname tables=people where=1 link=lastname,UpdateForm/People,personid:)

By the above example, the lastname field will be linked to UpdateForm/People as UpdateForm/People?personid=42.

Link Example 2:

(:selectquery columns=personid,lastname,email tables=people where=1 link="lastname,UpdateForm/People,personid;email,PmForm/MailForm,email":)

This example adds to example 1 by adding a second link so that the e-mail address will be a link to a PmForm with the e-mail address passed along. Note that it is not currently possible to link to a page outside the wiki.

Display Settings

There are several display options available in Select Query. These are:

• custom
• norowcount
• noheader
• div
• debug

Custom Display

If you only have one record to display, the default table display is probably not what you're looking for. In that case, simply specify display=custom, and then you can arrange your fields however you like, marking them up as {`fieldname`}. In addition you can use {`RowCount`} to display how many rows were returned by the query.

(Technical note: The reason we can't use PmWiki-standard Page Variables for field values is that the selectquery tag is processed after page variables are processed, so that you can use page variables inside the selectquery tag to customize queries based on variable values. I tried it both ways and found that adding yet another custom markup for field values was more powerful and useful than using the standard page variable markup and losing the ability to use variables in queries. I chose the backquote character because of its use in MySQL to designate field names.)

(Note: PmWiki is case-sensitive, and depending on your database, your field name casing may be different than what you specified. For example, MySQL requires all field names to be lowercase. Try the query with the default table display first, to see how the field names are coming back from the database, and then switch to display=custom.)

(:selectquery columns=firstname,lastname,email tables=people options=lastname display=custom:)
(:input form /pmwiki/SelectQuery/CustomDisplay get:)
Last name to search for: (:input text lastname:)
(:input submit value="Run Query":)
(:input end:)

[[mailto:{`email`} | {`firstname`} {`lastname`}]]

DIV display

If you want to avoid tables, for example because you have only have one column to display, you can have SelectQuery display the results using divs instead, and then customize their display in CSS. The div classes are nested as follows:

• selectquery
  • selectqueryheaders
    • selectquerycell
  • selectqueryrow
    • selectquerycell

norowcount & noheader

You may not always want the row count and headers to display. By adding these display options, the row count and / or the headers section will be skipped.

Debug

The debug display option will add information to the top of the page that may be useful for diagnosing why a query is not working as intended.

Connection

The connection option allows defining more than one database connection in your config.php and selecting at run-time the connection to use. If not specified, the $SelectQuerySettings['database'] settings will be used.

Conditional Syntax

Conditional syntax can be done as a "ternary operator" of the form (if ? then : else), continuing to enclose field names in `backquotes`. The "if" condition will be evaluated as PHP code, not as wiki conditional markup, so you must use a double == instead of a single =. For example:

 (:selectquery columns="status,expiredate" tables=members 
  options=userid display=custom:)
 {(`status` == 'paid' ? Your membership is paid through `expiredate`. 
  : Your membership has not been paid.)}

(Technical note: Similarly to the field values, the reason custom markup is necessary is because the selectquery tag is processed after the standard conditional markup tags, allowing you to make queries run or not depending on wiki variables. I considered this functionality to be more important than using standard markup.)

Multiple conditions (using boolean operators "and" and "or") can be specified provided you put parentheses around each condition. The program will automatically add parentheses around parameters separated by " and " (spaces included). For example:

 {(`lastname` and `expiredate` > now() ? yes : no)}

will evaluate correctly because parentheses will be added automatically, and

 {((`lastname`)&&(`expiredate`>now()) ? yes : no)}

will work because parentheses are provided, and even

 {((`lastname`) or (`firstname`) and `expiredate` > now() ? yes : no)}

will work (though maybe not as you had in mind!), but

 {(`lastname` && `expiredate`>now() ? yes : no)}

will not work because parentheses are not automatically added for &&, and

 {(`lastname`and`expiredate`>now() ? yes : no)}

will not work because there are no spaces around the "and". I can only take ya so far!

Pitfalls to watch out for:

• As with most PmWiki markup, linebreaks inside tags will break the tags. If you copy the examples above, be sure to remove any linebreaks that appear between (: and :).
• If the :) is the last thing in the page, PmWiki will sometimes fail to process it, so if you're having trouble, try adding a space or linebreak at the end of the page.
• Those who are not familiar with PHP syntax should note the double == sign in the first example of conditional synax above. A single = in this context will always make your condition false (if it doesn't generate an error message!), because PHP will try to assign a value to another value, and that can't be done!
• If your field names have the same names as reserved words in MySQL -- for example, delete -- you may need to enclose them in `backquotes` (or whatever character you've defined as $SelectQuerySettings[FieldID]) like this: columns=firstname,`delete`
• Fulltext searching only works if you have a fulltext index defined for those columns in your table. This is fairly easy to set up, and the error messages should be instructive, but it's good to know about it in advance.

Release Notes

If the recipe has multiple releases, then release notes can be placed here. Note that it's often easier for people to work with "release dates" instead of "version numbers".

2006-05-14 First version posted.

2006-06-07 Version 1.1 adds custom display and ternary operators.

2006-08-28 Version 1.2 adds fulltext searching, minor bug fixes.

2007-02-08 Version 1.3 adds the ability to limit a query by the userid, as in UpdateForm.

2007-03-25 Version 1.4 adds the ability to use the 'as' clause to change the name of fields, including in the linked fields options

2007-03-29 Version 2.0 Beta 1:

• 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 of a) the link data, b) the select string for debug purposes, c) the output string
• Converted to database standard
• Fixed a bug with fields that were both in the columns list and linked fields not showing

2007-04-09 Version 2.0 Beta 2:

• Added Pagination feature
• Added backwards compatibility of connection settings (not fully tested)
• Added RecipeInfo

2007-04-23 Version 2.0 Beta 3:

• Major rewrite of code to fix fully qualified names issues
• 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!

2007-07-06 Version 2.0 Beta 4:

• 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

2008-01-28 Version 2.0 Beta 5:

• Bug Fix: 'as' statement was not being honered in column headers
• Clean up some code in the query row section - redundant access to field data
• Bug Fix: On some installs, results were always blank.

2008-03-18: Version 2.0 Build 6:

• No more betas! Considered stable and will be using builds instead.
• New feature: added the 'like' parameter to match/against to allow using the like statement instead

2008-12-27: Version 2.0 Build 7:

• New variables:
• * $SQFieldId in case the DB uses something else than '`'
• Bug Fix: back tics causes the parser to ignore fields names, rendering no data.

2009-06-30: Version 2.0 Build 8:

• Changed Variables introduced in b7 into the $SelectQuerySettings[] array since it didn't work
• * $SQDelim becomes 'Seperator', defaults to '.'
• * $SQFieldID becomes 'FieldId', defaults to '`'
• Added Variable 'TableId' to $SelectQuerySettings[], default none
• By Request: New parameter 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.

2013-09-20 Version 3.0:

• Added option nodata="The dataset is empty" to display a configurable message if there are no lines returned by the query.
• Added an option to display, display=pmtable" to format the output as a PmWiki Simple Table, instead of html. The reason was to be able to use the Sortable Tables recipe to be able to dynamically sort the tables. In addition, if you do not specify noheaders, a pmwiki table header row will be generated.
• Minor corrections, especially one to allow the connection= parameter to work.
• Not Done: Setting page variables or page text variables with the query. For reasons, see my comments at the end of the talk page

2017-12-15 Version 4.0:

• Rewrote the code as object-oriented programming.
• Removed undocumented, obsolete, and unnecessary functionality.
• Switched to using PDO instead of ADOdb.

Comments

As of March 18th, 2008 the comments have been Moved to the SelectQuery-Talk page.

See Also

MyPmWiki, UpdateForm, DataQuery

Contributors

Ben Stallings and Guy Moreau