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
Prerequisites: Database Standard
Status: no current demo site
Maintainer: Guy Moreau, Vince Giambalvo
Categories: CMS, SystemTools, PHP72
Discussion: SelectQueryTalk

Questions answered by this recipe

  • How can I provide my users access to simple database queries without allowing all forms of queries?
  • How can I provide my users with an index of information that can be sorted multiple ways and that provides 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 is displayed by default in a table, and the cells in one or more columns of the table can be made into links to pass parameters to other pages.

SelectQuery version 2 and later uses the ADOdb abstraction layer to allow you to use any supported database.

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

New Installation

  1. Upload the selectquery3.7.phpΔ file into your cookbook directory
    • Older versions of PHP (5.2) might need to use older versions - see above
  2. To prevent 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 not already done: download, install and configure the Database Standard
  4. Add the $SelectQuerySettings['database'] variable per the Database Standard requirements and your given name in the Database Standard to your config after the database standard (example: $SelectQuerySettings['database'] = "main";
  5. Add include "$FarmD/cookbook/selectquery3.7.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/selectquery3.7.php";
    }
  6. To modify the default styles, edit your stylesheet to make table.selectquery look pretty. Similarly, span.rowcount can be styled or made invisible as you like.

Upgrade from 1.x to Version 2.0 Builds

There are now two methods to migrate to Version 2.0 of SelectQuery. The first one, made available in Beta 1, is to manually migrate your settings to the Database Standard settings. As of Beta 2 and all future builds, you can also just drop in the new version and the Database Standard and go, no other changes.

Manually Migrating Settings to Database Standard

  1. delete the constants DB_SERVER, DB_USER, AND DB_PASS in your config.php
    What if you use UpdateForm as well? It does not work without those! ~Someone
  2. if not already done: download, install and configure the Database Standard
  3. add the '$SelectQuerySettings['database']' variable and your given name in the Database Standard to your config after the database standard (example: variable '$SelectQuerySettings['database'] = "main"' e.g. if you connect to db called 'foo' you use this variable this way: '$SelectQuerySettings['database'] = 'foo';

Settings

There are four [$SelectQuerySettings['database']] settings, one of which is mandatory:

  • database (mandatory)
  • Seperator (. default value)
  • FieldID (` default value)
  • TableID (no default value)

Seperator

Used to change the default "." notation in a query

FieldID

Used to change the field identifiers in an SQL Query.

TableID

Used to add a table identifier in an SQL Query in case of special characters.

Notes

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. 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 at 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.