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

SelectQuery

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 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 uses the ADOdb abstraction layer to allow you to use any supported database.

Files

Last Version 1.x (does not use ADOdb): selectquery_v1_4.phpΔ
Latest Version (Build 6, March 18th, 2008): selectquery.phpΔ

New Installation

1. put selectquery.phpΔ in your cookbook directory
2. define $SQofflimits (at the beginning of the selectquery.php file) 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 and your given name in the Database Standard to your config after the database standard (example: variable '$SelectQuerySettings['database'] = "main"'
5. include "$FarmD/cookbook/selectquery.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/selectquery.php";
   }

6. edit your stylesheet to make table.selectquery look pretty ... otherwise it's just a plain, unstyled table. 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 migrate you 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.

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';

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 use 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

In your select query, you can also use the SQL 'as' clause to rename the select fields. This is usefull with link parameter (discussed below) to map any link to a field.

Additional parameters are optional:

• order="lastname,firstname" (to sort the results)
• limit=10
• display
• count
• link="lastname,People/EditForm,personid;email,emailform.php,email"

This last parameter means that the lastname field (when present) will be a link to another wiki page with the personid field passed as a parameter (for example People/EditForm?personid=42) and that the e-mail address (when present) will be a link to an external script with the e-mail address passed along. The field being passed need not be included in the list of columns. This linking feature is especially useful in conjunction with UpdateForm. Additional parameters can be added to include many link variables.

Pagination (count parameter)

You can have select query split up 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 formated - 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`}.

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

 (: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.

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

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

I had previously suggested this recipe would be supplanted by DataQuery, but now (March 2007) I find the two can do things together that neither can do separately. For example, if you put a SelectQuery in a pagelist template that you use with DataQuery, you effectively nest two queries, one inside the other! That's very useful for displaying data from programs like OsCommerce. So I will continue to support SelectQuery in the future.

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

Comments

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

See Also

MyPmWiki, UpdateForm, DataQuery

Contributors

Ben Stallings and Guy Moreau