smd_query

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.

Features

• 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 to the plugin’s container to display the results
• <txp:else /> supported
• Column headings may be output using a second form
• Result sets can be paginated, with support for a paging form

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.

Tag: <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; even INSERT and UPDATE if you are careful

Use the following attributes to configure the plugin. The default value is unset unless otherwise noted.

Attributes

Simple queries

column

Comma-separated list of columns to retrieve from the database.

table

Name of the table to retrieve the columns from (non-Txp tables are also supported if they are in the same database).

where

Any extra clause you wish to specify

Default: 1=1 (i.e. “the whole darn table”).

Advanced queries

query

Any valid 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’s 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 insanely clever with your query.

Default: auto.

populate

You usually use {replacement} variables or <txp:smd_query_info /> in your smd_query container to access any fields you grab, 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 regular Txp tags inside your form/container. See example 13.

Forms and paging

form

The Txp 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.

<txp:else /> is supported.

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.

above.

both of them separated by delim.

Default: below.

preparse

Alter the parse order of the form/container. Options are:

0: any container or Form will have its replacement variables swapped for content first and then it will be parsed to process any Txp tags.

1: the Form/container is parsed first before the replacements are made. This is 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.

Filters

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 HTML tag to surround the label with. Specify it without angle brackets (e.g. labeltag="h3").

wraptag

The 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 unique 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 or SQL error message. Using silent="1" will attempt to hide this error message.

var_prefix

The prefix to use for the page and counting {replacement} variables.

Default: smd_.

count

Can be either “up” or “down” to alter the order the plugin counts row numbers. See {smd_thisrow}.

Default: up.

raw_vals

Whether to expose raw values or processed values to the replacement/container tags.

Default: 0 (use sanitized values where possible)

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 Textpattern 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:

1. <txp:variable />
2. $_SERVER var
3. image
4. file
5. link
6. global article
7. current article
8. URL var
9. default value
10. 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. If you wish to use replacements as attributes to other Txp tags, you should use single not double quotes. These uses are all fine:

{custom_5}
<span>{custom_5}</span>
<txp:custom_field name='{custom_5}' />

whereas this will probably not work as expected:

<txp:custom_field name="{custom_5}" />

In addition to the replacements from your query, the following replacements are added to each row (note that the ‘smd_’ prefix can be altered with the var_prefix attribute):

• {smd_allrows} : the total number of rows in this result set¹
• {smd_pages} : the total number of pages in this result set¹
• {smd_thispage} : the current page number being viewed¹
• {smd_rows} : the total number of rows visible on this page
• {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)
• {smd_usedrow} : the current row number of rows that have actually been displayed. If your smd_query container employed a conditional, for example, that only output certain rows then this counter only increments for those rows you display. Bear in mind that using conditionals inside smd_query can mess paging and other counters up, so it’s best to avoid paging if you are using conditional output.

¹ These 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).

Tag: <txp:smd_query_info>

While {replacements} are handy, sometimes a real tag is better. smd_query gives you the choice, because there are sometimes advantages to using a tag over replacements. The primary reason is that you can list items of the same type that you want, then use the wraptag, break and class attributes to wrap and separate them. This can result in shorter code and may save having to use a conditional tag around optional data.

The tag’s attributes are:

type

The type of data you want to display. Choose from:

field for standard field information, e.g. named columns from the database

page for page-based information if you are using paging (some paging info is available in the field type, as noted in the replacements section)

col for column heading information

Default: field

item

Comma-separated list of items you want to display. They are case-sensitive.

Can be any column name you have elected to pull from the database or a paging variable, but they must all be of the same type (use the debug="1" attribute to see a list of all available items from each type).

wraptag

The HTML tag to surround the entire list.

class

CSS class name to apply to the wraptag.

break

Each returned item of data will be wrapped with this tag, or if a tag is not used the given text will be put between each item.

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

urlfilter

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:

urlfilter="/[^\d]+/"

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.

urlreplace

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:

urlreplace="-"

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.

Examples

Example 1: Simple image select query

<txp:smd_query column="*"
     table="txp_image"
     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}">
      <txp:smd_query_info item="filename" />
    </txp:file_download_link></td>
  <td>
    <txp:smd_query_info item="description" />
  </td>
  <td>downloads: {downloads}</td>
<txp:else />
  <p>No recent downloads, sorry</p>
</txp:smd_query>

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. Note that it also uses <txp:smd_query_info /> to display user-uploaded information as we don’t implicitly trust our users not to put rogue filenames / descriptions in.

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}">
    <txp:smd_query_info item="title" />
  </txp:permlink>
</txp:smd_query>

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:

government,conspiracy,id,card,data,biometric,bad,idea

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. 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}">
     <txp:smd_query_info item="filename" />
  </txp:file_download_link>
</txp:smd_query>

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 > and < 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">
   <txp:permlink>
      <txp:smd_query_info item="Title" />
   </txp:permlink>
</txp:smd_query>

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

Notes:

• 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).
• the item name is case-sensitive in the <txp:smd_query_info /> tag

Example 8: filtered URL params (better!)

<txp:smd_query query="SELECT Title
     FROM textpattern
     WHERE status = '?user_status'"
     urlfilter="/[^1-5]/"
     defaults="user_status: 4">
   <txp:permlink>
      <txp:smd_query_info item="Title" />
   </txp:permlink>
</txp:smd_query>

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 />
</txp:smd_query>

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">
<td>{name}</td>
<td>{RealName}</td>
</txp:output_form>

and like this for more detailed output:

<txp:output_form form="user_table">
<td>{name}</td>
<td>{RealName}</td>
<td>{email}</td>
<td>{last_access}</td>
</txp:output_form>

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"
     pageform="page_info">
   <txp:smd_query_info item="RealName" /> ({privs})
</txp:smd_query>

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">
   <txp:smd_query_info item="RealName" /> ({privs})
</txp:smd_query>

In page_info:

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

Or using the <txp:smd_query_info /> tag in a few places (which is probably overkill in this case and is just for demonstration of the type attribute):

Page
 <txp:smd_query_info item="smd_thispage, smd_pages" break=" of " /> |
Showing rows <txp:smd_query_info type="page"
     item="smd_row_start, smd_row_end" break=" to " />
of <txp:smd_query_info item="smd_allrows" /> |
<txp:smd_query_if_prev>
  <a href="<txp:permlink />?smd_qpg={smd_prevpage}">
     Previous {smd_rows_prev}</a>
</txp:smd_query_if_prev>
<txp:smd_query_if_next>
  <a href="<txp:permlink />?smd_qpg={smd_nextpage}">
     Next {smd_rows_next}</a>
</txp:smd_query_if_next>

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"
     pageform="page_info"
     pagevar="SMD_QUERY_UNIQUE_ID">
   <txp:smd_query_info item="RealName" /> ({privs})
</txp:smd_query>

In page_info:

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

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"
     populate="article">
   <txp:title /> [ <txp:posted /> ]
</txp:smd_query>==

Note that in versions of TXP earlier than 4.3.0, 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 Txp 4.3.0 and higher you can omit the unix_timestamp() columns.

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"
     form="show_users">
<txp:else />
<p>No user info</p>
</txp:smd_query>

Your show_users form can contain usual replacement variables, tags 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>
</txp:smd_query>==

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!

Author

Stef Dawson