SelectQuery

Summary: Run a SQL SELECT query based on parameters submitted
Version: 2.0 Build 8, June 30th, 2009
Version: 3.0, September 20th, 2013
Version: 4.0, December 13th, 2017
Prerequisites: SQL database supported by PDO
Status: demo at [(approve links) edit diff]
Maintainer: Ben Stallings
Categories: CMS, SystemTools, PHP72
Discussion: SelectQueryTalk

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 [(approve links) edit diff] 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Δ

For differences between version2 and version 3 see below.
Version 3.7 (August 07, 2017): selectquery3.7.phpΔ - updated for PHP7

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. 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,petname tables=people,pets 
where="people.peopleid = pets.ownerid" 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 word anywhere in a larger field, use the match and against parameters, like this:

 (:selectquery columns=firstname,lastname,email,petname tables=people,pets 
where="people.peopleid = pets.ownerid" match=medical_history against=searchfor:)
 (:input form:)
 Condition to search for: (:input text searchfor:)
 (:input submit value="Run Query":)
 (:input end:)

As of Version 2.0 Build 6, there is an additional parameter that can be set for match/against. This parameter is the type parameter, when set to 'like' will use the like statement instead of match/against. Furthermore, leading and trailing wild cards are automatically added to any like parameter if there is not leading wild card.

Important note about multiple tables

If you using more than one table and are getting timeout messages, make sure that your tables statement are using a join clause or the where statement is doing the join. Otherwise, SQL will put table B for every row in table A.

'as' clause

You can change the fields display name in either the fields list of the link parameter. To do this simply add the ' as ' statement to the appropriate locations.

Example: (:selectquery columns="firstname,lastname,email as E-Mail" tables=people:)

Additional parameters that are optional:

  • order="lastname,firstname" (to sort the results)
  • limit=10 (limits the number of results returned)
  • display (affect the output, see below)
  • count (creates pagination, see below)
  • link="lastname,People/EditForm,personid;email,emailform.php,email" (hot linking, see below)
  • connection="connectioname"

Link parameter

The link parameter create a wiki link on a column. The format is 'target, page, source(s)'. This means that the target field (which can be a literal using the single quote) will be a linked to another page -- using standard wiki link formatting -- with the source field data passed as a parameter. You can create multiple links by separating them with a semicolon. The field being passed need not be included in the list of columns, it will automatically be added. This linking feature is especially useful in conjunction with UpdateForm.

Link Example 1:

link="lastname,People/EditForm,personid" By the above example, the lastname field will be linked to People/EditForm as People/EditForm?personid=42. Note the semicolon is optional with a single link.

Link Example 2:

link="lastname,People/EditForm,personid;email,emailform.php,email" This example adds to example 1 by adding a second link so that the e-mail address will be a link to an external script with the e-mail address passed along.

count parameter (Pagination)

You can have results split up the number of the rows of large results by adding the count parameter to specify how many records per page to display. If the count parameter is included, the navigational links (First, Previous, Next and Last) are automatically added to the bottom of the query results. Currently, they are not formatted - they display inline on the left side of the query delimited by the pipe symbol.

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 table layout 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`}. Important note, depending on your database, your field name casing may be different than inputted and pmwiki is case-sensitive. For example, mysql forces all field names to lowercase.

(MySQL uses `backquotes` to denote field and table names, but I readily admit this markup is a kludge. I was unable to get PmWiki to recognize my changes to $FmtPV at the time I was writing the recipe, and now that I know better I haven't gotten around to making the necessary changes. Meanwhile, the idiosyncratic markup works, so I will continue to support it once I do get around to supporting standard markup.)

Note: The same effect can be achieved by replacing custom with 'div,norowcount,noheader'. The difference is custom skips the display section of the recipe, while 'div,norowcount,noheader' will just display the data.
Example:
 (:selectquery columns=firstname tables=users options=userid 
  display=custom:)
 Well, hello there, {`firstname`}!

DIV display

If you only have one column to display, or you want to avoid tables, you can have SelectQuery display the results using DIVs instead. The divs are named:

  • selectquery
  • selectqueryheaders
  • selectqueryrow
  • selectquerycell

No Row Count & No Headers

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 a print out to the top of the page containing information that may be useful to debug the recipe in a wiki.

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

(Similarly, I was unable to get PmWiki to recognize my changes to the $Conditionals, so I had to come up with my own conditional syntax. A future version will support standard conditional markup, but meanwhile this gets the job done.)

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. 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.)}

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` 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-06-12 Minor bug fixes.

2006-07-09 Minor bug fixes.

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

2006-10-26 Minor bug fix.

2006-11-08 Minor bug fix.

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

2007-02-23 Minor bug fix.

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

Future plans

The conditionals markup will be rewritten to match pmwiki standard markup. See commments section for other possible ideas on this recipe. As of version 2.0 build 6, there is preliminary work on this. Please test and comment.

Final Notes

The SelectQuery recipe is now being developed and maintained by Guy Moreau

Comments

As of March 18th, 2008 the comments have been Moved to the SelectQueryTalk page.

See Also

MyPmWiki, UpdateForm, DataQuery

Contributors

Ben Stallings and Guy Moreau

User notes +1: If you use, used or reviewed this recipe, you can add your name. These statistics appear in the Cookbook listings and will help newcomers browsing through the wiki.