SQL to the masses

n: smd_query | v: 0.50 | f: /

Documentation for the Textpattern plugin smd_query by Stef Dawson follows this short message from our sponsor ;-)

Plugin list buttonPlugin download buttonCompressed plugin download button


The laziest tag ever! Allows you to make ad-hoc queries to the database and process the results, row by row, in a form or container.


  • Supports simple queries with a reduced syntax (SELECT cols FROM table WHERE clause) or your own custom queries
  • Read information from any part of the current article, image (planned), file, link, <txp:variable /> or URL line. If any fields are missing you can specify a default value
  • Optionally filter the URL input using regular expressions, for safety
  • Each row can be passed to a form (or the plugin can be used as a container tag with or without <txp:else />) to display the results
  • Column headings may be output using a second form
  • Result sets can be paginated, with support for a paging form


Stef Dawson

Installation / Uninstallation

Download the plugin from either textpattern.org, or the software page, paste the code into the TXP Admin -> Plugins pane, install and enable the plugin. Visit the forum thread for more info or to report on the success or otherwise of the plugin.

Uninstall by simply deleting the plugin from the Admin->Plugins pane.

<txp:smd_query /> usage

Use this tag in any page, form, article, file, link, etc context to grab stuff from the database. The plugin can operate in one of two modes:

  1. simple mode just allows SELECT stuff FROM table WHERE clause
  2. advanced mode uses the query attribute so you can design your own query. It can include COUNT (*), joins, anything; perhaps even INSERT and UPDATE (?) although it’s untested and liable to flooding


Simple queries

  • column : comma-separated list of columns to retrieve from the database
  • table : name of the table to retrieve the columns from (yes, non-TXP tables are also supported if they are in the same database :-)
  • where : any extra clause you wish to specify. Defaults to “the whole darn table”

Advanced queries

  • query : any query you like can be used here. Overrides column, table and where
  • mode : you should not need to alter this parameter as it is set to automatically detect the query type. If you are using SELECT or SHOW statements, the mode is set to output; for any other type of query (e.g. INSERT/UPDATE) it is set to input. The only difference between the two modes is that if it set to input you can use smd_query as a self-closing tag because it does not use the form/container to parse the result set. Change this parameter only if the plugin detects the mode wrongly or you are doing something unorthodox with your query. Default: auto
  • populate : you usually use {replacement} variables in your smd_query container but if you are dealing with the native Textpattern content types (article, image, file, link) you can inform smd_query which of the four you are using via this attribute. You can then use TXP tags inside your form/container. See example 13

Forms and paging

  • form : the form to use to parse each returned row. See replacements. If not specified, the plugin will use anything contained between its opening and closing tag
  • colsform : optional TXP form to parse any header row containing column names (of limited use)
  • pageform : optional TXP form used to specify the layout of any paging navigation and statistics such as page number, quantity of records per page, total number of records, etc.
  • pagepos : the position of the paging information. Options are below (the default), above, or both of them separated by delim
  • preparse : normally, any container or form will have its replacement variables swapped for content and then it will be parsed to process any TXP tags. If you wish to reverse this order so that the container is parsed first before the replacements are made, specify preparse="1". Very useful when using the <txp:yield /> tag (see example 9). Default: 0
  • limit : show this many results per page. Has no bearing on any SQL LIMIT you may set. Setting a limit in the smd_query tag switches paging on automatically so you can use the <txp:older /> and <txp:newer /> tags inside your pageform to step through each page of results. You may also construct your own paging (see example 11)
  • offset : skip this many rows before outputting the results
  • pagevar : if you are putting an smd_query on the same page as a standard article list, the built-in newer and older tags will clash with those of smd_query; clicking next/prev will step through both your result set and your article list. Specify a different variable name here so the two lists can be navigated independently, e.g. pagevar="qpage". Note that if you change this, you will have to generate your own custom newer/older links (see example 11) and the conditional tags. You may also use the special value pagevar="SMD_QUERY_UNIQUE_ID" which will assign the paging variable to this specific instance of your query. This will allow you to use multiple smd_query tags on a single page and navigate them independently using the same pageform (see example 12 for details). Default: pg


  • urlfilter : filter URL input with this list of regular expressions (each separated by delim)
  • urlreplace : replace each filtered URL element listed in urlfilter with this list of regular expressions (each separated by delim). If not used, anything matching urlfilter will be removed from any URL variables. See Filtering and injection
  • defaults : comma separated list of values to use in the event some field you specified doesn’t exist. Each default should be given as name: value (the : is configurable via the paramdelim attribute). For example defaults="id: 1, my_cat: mammals, user_sec: ?defsec" would mean that if the id field was blank, the number 1 would be used; if the variable my_cat was empty, the word mammals would be used; and if the user_sec variable was empty, use the default value as found in the variable defsec (which could have been set via a <txp:variable /> earlier in the page)
  • escape : list of column names with which to escape HTML entities. Useful if you have returned body or excerpt blocks that may contain apostrophes that could kill tags inside the smd_query container

Tag/class/formatting attributes

  • label : label to display above the entire output
  • labeltag : the (X)HTML tag to surround the label with. Specify it without angle brackets (e.g. labeltag="h3")
  • wraptag : the (X)HTML tag to surround the entire output (e.g. wraptag="table")
  • html_id : HTML ID to apply to the wraptag
  • class : CSS class name to apply to the wraptag
  • break : each returned row of data will be wrapped with this tag (e.g. break="tr")
  • breakclass : CSS class name to apply to the break tag

Plugin customisation

  • strictfields : when using ‘?’ fields, spaces are allowed in field names. Set strictfields="1" to forbid spaces. Default: 0
  • delim : the delimiter to use between patterns in urlfilter and urlreplace. Default: comma (,)
  • paramdelim : the delimiter to use between name-value pairs in defaults. Default: colon (:)
  • hashsize : (should not be needed) the plugin assigns a 32-character, unique reference to the current smd_query based on your query attributes. hashsize governs the mechanism for making this long reference shorter. It comprises two numbers separated by a colon; the first is the length of the uniqe ID, the second is how many characters to skip past each time a character is chosen. For example, if the unique_reference was 0cf285879bf9d6b812539eb748fbc8f6 then hashsize="6:5" would make a 6-character unique ID using every 5th character; in other words 05f898. If at any time, you “fall off” the end of the long string, the plugin wraps back to the beginning of the string and continues counting. Default: 6:5
  • silent : if your query contains an error (wrong column name or some malformed input), the plugin will issue a TXP error message. Using silent="1" will attempt to hide this error message
  • count : Can be either “up” (the default) or “down”. See {smd_thisrow}
  • debug : set to 1 to show some debug output; use 2 to show a bit more detail

The attributes query, column, table and where can contain replacements themselves to read values from the current context. Specify the field name with a ? in front of it (e.g. query="SELECT * FROM txp_image WHERE category='?category1' OR category='?category2') would show images that had their category set to one of the article’s categories.

The ‘?’ fields can be any item from the TXP universe, including anything set in a <txp:variable /> or some user-input on the URL address bar. Fields are processed in the following order; as soon as a matching entry is found, the rest are not checked:

<txp:variable /> -> $_SERVER var -> image -> file -> link -> global article -> current article -> URL var -> default value -> verbatim (without ‘?’)

This hierarchy allows some degree of safety: since TXP variables are ultimately set by you, they are checked first, then gradually less specific stuff is checked until URL variables are considered at the bottom of the food chain.

Replacement tags

In your output form you may specify any column name surrounded with {} characters to display that field. So if your query was SELECT id, name, category FROM txp_image WHERE ext=".jpg" you would have the following replacements available:

  • {id} : the image ID
  • {name} : the image filename
  • {category} : the image category

Just put those names into your form among other normal HTML or TXP tags, and the relevant value from that row will be displayed. The replacements honour any AS statement you may employ to rename them.

In addition, the following replacements are added to each row:

  • {smd_allrows} : the total number of rows in the result set1
  • {smd_rows} : the total number of rows visible on this page
  • {smd_pages} : the total number of pages in this result set1
  • {smd_thispage} : the current page number being viewed1
  • {smd_thisrow} : the current row number on this page
  • {smd_thisindex} : the current row number (zero-based) on this page
  • {smd_cursor} : the current row number from the start of the result set
  • {smd_cursorindex} : the current row number from the start of the result set (zero-based)

1 These three items are also available in your designated pageform. The pageform can also utilise these extra replacements:

  • {smd_prevpage} : the previous page number (empty if on first page)
  • {smd_nextpage} : the next page number (empty if on last page)
  • {smd_row_start} : the first row number being displayed
  • {smd_row_end} : the last row number being displayed
  • {smd_rows_prev} : the number of rows on the previous page. Will either be the value of your limit, or 0 if on the first page
  • {smd_rows_next} : the number of rows on the next page
  • {smd_query_unique_id} : the unique ID assigned to this smd_query tag (see the hashsize attribute and example 12 for more)

These are useful for tables to show row numbers, but can also be used for pagination or can be tested with smd_if to take action from within your form. {smd_thisrow}, {smd_thisindex}, {smd_cursor}, and {smd_cursorindex} count up or down depending on the count attribute ({smd_row_start} and {smd_row_end} also change accordingly).

<txp:smd_query_if_prev> / <txp:smd_query_if_next>

Use these container tags to determine if there is a next or previous page and take action if so. Can only be used inside pageform, thus all paging replacement variables are available inside these tags.

<txp:smd_query_if_prev>Previous page</txp:smd_query_if_prev>
<txp:smd_query_if_next>Next page</txp:smd_query_if_next>

See example 11 for more.

Filtering and injection

After great deliberation, access to the URL line has been granted so you may employ user-entered data in your queries, allowing complete flexibility for your user base. However, as Peter Parker’s conscience might say:

With great power comes great responsibility

Not everybody out there is trustworthy so heed this warning: Assume ALL user input is tainted. Check everything. If you want to know more about what people can do with access to one simple portion of your SQL query, Google for ‘SQL injection’.

For those still reading, the good news is that the plugin does everything it can to pre-filter stuff on the URL line before it gets to the query. This should make your user input safe enough, but for the paranoid (or sensible) there are two attributes you can use to clamp down allowable user input. If you know anything about regular expressions or are familiar with the PHP function preg_replace() then you’ll be right at home because, put simply, you can optionally pass every URL variable through it to remove stuff you don’t want.


This takes a comma-separated list (at least by default; override the comma with the delim attribute if you need to use commas in your filter strings) of complete regular expression patterns that you wish to search for, in every URL variable. For example, if you wanted to ensure that your users only entered digits you could specify this:


Briefly, the starting and trailing / marks delimit a regular expression — they must always be present. The square brackets denote a group of characters, the circumflex negates the group, the \d means “any digit” and the + specifies that you want it to check for one or more of the preceding things. In other words, look for anything in the input that is not one or more digits. That would match any letters, quotes, special characters, anything at all that wasn’t a zero to nine.

You can specify more than one filter like this:

urlfilter="/\d/, /\s/"

That would look for any single digit and any single space character. That’s a simple example and you could do it all in one regex, but splitting them up can help you filter stuff better (see urlreplace for an example).

By default, if you just specify urlfilter without urlreplace, anything that matches your filter patterns will be removed from the user input.


The other half of the filtering jigsaw allows you to not just remove anything that matches, but actually replace it with something else. Specify a fixed string, a list of fixed strings or more URL patterns to replace whatever matches your urlfilter. Using the first filter example from above, you could replace anything that is not a digit with a hyphen by specifying:


So if you allowed a URL variable called digits and a site visitor entered ?digits=Zaphod 4 Trillian, your URL variable would become: -------4--------. Not much use, but hey, it’s an example!

As with urlfilter you can specify more than one replacement and they will pair up with their corresponding filter. In other words, if you take the second filter above (urlfilter="/\d/, /\s/") and used this:

urlreplace=", -"

Then any digit in your user input would be removed (there is nothing before the comma) and any space character would be replaced with a hyphen.

If at any time a field gives an empty result (i.e. it totally fails any urlfilter tests or simply returns nothing because it has not been set), any defaults assigned to that variable will be used instead. If there are no defaults, the name of the variable itself (minus its ?) will be used.

With these two filters at your disposal and the ability to specify default values for user variables, you can make your queries much safer to the outside world and start using HTML forms to gather input from users that can then be plugged into queries, fairly safe in the knowledge that your database is not going to implode.

But please remember:

Assume all user input is tainted: check everything.


Example 1: Simple image select query

<txp:smd_query column="*"
     where="category='mammal' OR category='bird'"
     form="dbout" wraptag="ul" break="li" />

With form dbout containing:

<a href="/images/{id}{ext}" /><txp:thumbnail name="{name}" /></a>

Will render an unordered list of thumbnails with links to the fullsize image if the category is either mammal or bird.

Example 2: link category list to parent

<txp:smd_query query="SELECT DISTINCT
     txc.name FROM txp_category AS txc, textpattern AS txp
     WHERE type='article' AND parent='animals'
     AND (txc.name = txp.category1 OR txc.name = txp.category2)
     form="dbout" wraptag="ul" break="li" />

With form dbout containing:

<txp:category name="{name}" link="1" title="1" />

Will render a list of linkable category names that contain articles with categories that have the given parent. If a category is unused it will not be listed.

Example 3: child category counts

<txp:smd_query query="SELECT DISTINCT
     txc.name, COUNT(*) AS count FROM txp_category AS txc,
     textpattern AS txp
     WHERE type='article' AND parent='?custom3'
     AND (txc.name = txp.category1 OR txc.name = txp.category2)
     GROUP BY txc.name"
     form="dbout" wraptag="ul" break="li" />

With form dbout containing:

<txp:category name="{name}" link="1" title="1" /> ({count})

Will read the parent item from the custom3 field and render a similar list to Example 2 but with the article counts added in parentheses afterwards.

Example 4: Top 10 downloads

<txp:smd_query column="*" table="txp_file"
     where="(category='?category1' OR category='?category2')
     AND status=4 ORDER BY downloads desc LIMIT 10"
     wraptag="table" break="tr"
     label="Most popular downloads" labeltag="h3">
  <td><txp:file_download_link id="{id}">{filename}</txp:file_download_link></td>
  <td>downloads: {downloads}</td>
<txp:else />
  <p>No recent downloads, sorry</p>

This one uses the plugin as a container tag instead of a form and tabulates the top 10 downloads (status=live) that have a category matching either of the current article’s categories, with most popular listed first. If there are no downloads, the <txp:else /> portion displays a message.

Example 5: Article keywords related to link

Very interesting use case here. Put this in the plainlinks form:

<txp:linkdesctitle />
<txp:smd_query query="SELECT DISTINCT
     txp.id, txp.title FROM textpattern AS txp
     WHERE (txp.keywords LIKE '%,?category%,'
     OR txp.keywords LIKE '%?category%,'
     OR txp.keywords LIKE '%,?category%')
     GROUP BY txp.title"
     wraptag="ul" break="li">
  <txp:permlink id="{id}">{title}</txp:permlink>

When you execute <txp:linklist /> from a page you will get a list of links as usual, but under each one you will see a hyperlinked list of articles that are related (by keyword) to the category of the link.

The reason it is compared three times is because article keywords are stored like this in the database:


If each category word was compared only once without commas (i.e. txp.keywords LIKE '%?category%') then a link with category piracy would cause any article containing keyword conspiracy to be included. Essentially, by comparing the category either surrounded by commas, with a comma after it, or with a comma before it, the search is restricted to only match whole words.

Example 6: Comparison in queries

<txp:smd_query query="SELECT *
     FROM txp_file WHERE downloads &gt;= 42">
  <txp:file_download_link id="{id}">

Shows links to all downloads where the download count is greater than or equal to 42. Note that under TXP 4.0.6 and below you must use the HTML entity names for &gt; and &lt; or the parser gets confused.

Example 7: unfiltered URL params (bad)

(a bad query)

<txp:variable name="cutoff"
     value="42" />
<txp:smd_query query="SELECT Title
     FROM textpattern
     WHERE id < '?usercut'"
     defaults="usercut: ?cutoff">

Shows hyperlinks to only those articles with an ID below the number given by the user on the URL line. If the value is not supplied, the default value from the TXP variable is used instead (42 in this case).

NOTE: validation is not performed and you cannot guarantee that the usercut variable is going to be numeric. You should not use this query on a production site unless you add a urlfilter to remove any non-numeric characters (see next example for a better query).

Example 8: filtered URL params (better!)

<txp:smd_query query="SELECT Title
     FROM textpattern
     WHERE status = '?user_status'"
     defaults="user_status: 4">

Pulls all articles out of the database that match the given status. This is a more robust query than Example 7 because it checks if the user_status field is 1, 2, 3, 4, or 5 (the regex specifies to remove everything from the user_status variable that is not in the range 1-5). If this condition is not met — e.g. the user specifies user_status=6 or user_status="abc" — then user_status will be set to 4. Note that using user_status="Zaphod 4 Trillian" on the URL address bar will actually pass the test because all characters other than the number ‘4’ will be removed.

You could use a <txp:variable /> if you wish and set all your defaults in a special form, ready to use throughout your page. In that case — if you had created a variable called dflt_stat — you might prefer to use defaults="user_status: ?dflt_stat".

Query-tastic :-)

Example 9: Using preparse with <txp:yield />

Sometimes you may want to re-use a query in a few places throughout your site and show different content. For example, the same query could be used for logged-in and not-logged-in users but you’d see more detail if you were logged in. Normally you would need to write the query more than once, which is far from ideal. This technique allows you to write the query just once and reuse the form. Put this in a form called user_table:

<txp:smd_query query="SELECT * FROM txp_users"
     wraptag="table" break="tr" preparse="1">
   <txp:yield />

Using <txp:output_form> as a container (in TXP 4.2.0 or higher) you can then call the query like this to show basic info:

<txp:output_form form="user_table">

and like this for more detailed output:

<txp:output_form form="user_table">

Note that when using smd_query in this manner you must remember to use preparse="1" because you need to fetch the contents of the smd_query container (the <txp:yield /> tag in this case), parse it so it gets the contents of <txp:output_form>’s container and then applies the replacements. Without the preparse, the plugin tries to apply the replacements directly to the smd_query container, which does not actually contain any {...} tags.

Example 10: pagination

Iterate over some TXP user information, 5 people at a time:

<txp:smd_query query="SELECT * from txp_users"
     limit="5" wraptag="ul" break="li"
   {RealName} ({privs})

In page_info:

Page {smd_thispage} of {smd_pages} |
Showing records {smd_row_start} to {smd_row_end}
of {smd_allrows} |
<txp:older>Next {smd_rows_next}</txp:older> |
<txp:newer>Previous {smd_rows_prev}</txp:newer>

Underneath your result set you would then see the information regarding which page and rows your visitors were currently viewing. You would also see next/prev links to the rest of the results.

Example 11: custom pagination

There is a problem with example 10 ; if you use txp:older and txp:newer when you are showing a standard article list; the paging tags will step through both your result set and your articles. To break the association between them you need to alter the variable that TXP uses to control paging. It is called pgand you’ll notice it in the URL (?pg=2 for example) as you step through article lists.

Using the pagevar attribute you can tell smd_query to watch for your own variable instead of the default pg and thus build your own next/prev links that only control smd_query.

<txp:smd_query query="SELECT * from txp_users"
     limit="5" wraptag="ul" break="li"
     pageform="page_info" pagevar="smd_qpg">
   {RealName} ({privs})

In page_info:

Page {smd_thispage} of {smd_pages} |
   Showing records {smd_row_start}
   to {smd_row_end} of {smd_allrows} |
  <a href="<txp:permlink />?smd_qpg={smd_prevpage}">
     Previous {smd_rows_prev}</a>
  <a href="<txp:permlink />?smd_qpg={smd_nextpage}">
     Next {smd_rows_next}</a>

Example 12: using SMD_QUERY_UNIQUE_ID

If you wish to use more than one smd_query on a single page but share a pageform between them you can use SMD_QUERY_UNIQUE_ID as the paging variable:

<txp:smd_query query="SELECT * from txp_users"
     limit="5" wraptag="ul" break="li"
   {RealName} ({privs})

In page_info:

Page {smd_thispage} of {smd_pages} |
   Showing records {smd_row_start}
   to {smd_row_end} of {smd_allrows} |
  <a href="<txp:permlink />?{smd_query_unique_id}={smd_prevpage}">
     Previous {smd_rows_prev}</a>
  <a href="<txp:permlink />?{smd_query_unique_id}={smd_nextpage}">
     Next {smd_rows_next}</a>

Note this is just a simple example: you will have to be more clever than that if you are paging independent sets of rows because you will need to incorporate the paging variable from both smd_query tags in your pageform.

Example 13: TXP tags in container

==<txp:smd_query query="SELECT *,
     unix_timestamp(Posted) as uPosted,
     unix_timestamp(LastMod) as uLastMod,
     unix_timestamp(Expires) as uExpires
     FROM textpattern WHERE Status IN (4,5)"
     wraptag="ul" break="li" html_id="myQuery"
   <txp:title /> [ <txp:posted /> ]

Note that the populate attribute relies on you extracting all columns to satisfy textpattern’s internal functions so this feature works correctly. A simple select * from ... will not work. In future versions of TXP this might change and a simple select * may then be enough.

For reference, these are the extra columns required in 4.2.0 (and earlier):

  • Article: unix_timestamp(Posted) as uPosted, unix_timestamp(LastMod) as uLastMod, unix_timestamp(Expires) as uExpires
  • Image: none
  • File: none
  • Link: unix_timestamp(date) as uDate

Example 14: <txp:else /> with forms

If you wish to use txp tags with an ‘else’ clause, you usually need to employ a container. As a convenience, smd_query allows you to use the container’s <txp:else /> clause with a form so you can re-use the query output and display different results in the event the query returns nothing.

<txp:smd_query query="SELECT * FROM txp_users"
<txp:else />
<p>No user info</p>

Your show_users form can contain usual replacement variables and markup to format the results. Perhaps later you may wish to re-use the show_users output in another query:

==<txp:smd_query query="SELECT * FROM txp_users WHERE
     RealName like '%?usr%'" form="show_users">
<txp:else />
<p>No matching users found</p>

Note that you can display a different error message but use the same form (we’re escaping Textile here with == so it doesn’t interpret the percent signs as <span> elements).

If you are careful and know you will never use a particular form with an smd_query container you can hard-code your ‘else’ clause directly in your form and use smd_query as a self-closing tag. Your form will look a bit odd with a seeming ‘dangling’ else, but it will work due to the way the TXP parser operates. If you do try and use a form with a <txp:else /> in it as well as calling the form using an smd_query with a <txp:else /> in its container, Textpattern will throw an error (usually the, perhaps unexpected, tag does not exist error). Be careful!


  • 22 May 08 | 0.10 | Initial release
  • 23 May 08 | 0.11 | Allowed maths in queries (use html entities in TXP4.0.6) and fixed WHERE clause to default to 1=1 if none supplied (both thanks jm) ; added more detailed file and link support
  • 14 Jul 08 | 0.12 | Added txp:else support in container (thanks jakob) ; added silent and count attributes, and the replacement tags {smd_rows} {smd_thisrow} {smd_thisindex}
  • 23 Nov 08 | 0.20 | Added <txp:variable /> support ; enabled URL variable support ; added urlfilter, urlreplace, delim, paramdelim and defaults attributes
  • 17 Mar 09 | 0.21 | Added $_SERVER var support
  • 16 Oct 09 | 0.22 | Added escape attribute (thanks jakob) ; added preparse attribute
  • 02 Dec 09 | 0.30 | Added unicode support and strictfields to fix a few bugs (thanks speeke) ; added direct pagination support
  • 05 Dec 09 | 0.40 | Added pagevar, {smd_prevpage}, {smd_nextpage}, {smd_rows_prev} and {smd_rows_next}, <txp:smd_query_if_prev> and <txp:smd_query_if_next> ; removed pgonly as paging can now only be performed in the pageform
  • 17 Jan 10 | 0.41 | Added hashsize, mode and {smd_query_unique_id}
  • 31 Aug 10 | 0.50 | form overrides container ; container’s else automatically works in forms ; added populate (thanks atbradley); fixed PHP 4 compatibility and added html_id and breakclass (thanks makss) ; SHOW defaults to output mode

Source code

If you’d rather wander aimlessly through thousands of lines of PHP source code, you’ll need to step into the view source page.

Legacy software

If, for some inexplicable reason, you need last century's version of a plugin, it can probably be found on the plugin archive page.

Experimental software

If you’re feeling brave, or fancy trying something new, you can test out some of my beta code. It can be found on the plugin beta page.