query

Summary

[query sql] ... [/query]

Parameter	Description	Default
sql	The SQL statement.	None
query	Alias for sql.	None
more	Allow search results to be paginated.	No
failure	Text to return if the query fails.	None
table	The name of the table to query (not usually required)	See parameter description
base	Alias for table	See parameter description
random	Randomly select a set of rows from the query's resultset (if "type=list").	No
type	One of "row_count", "html", "list" or "textref"	None
list_prefix	Override the name of the [list] sub-tag.	list
arrayref	Create a temporary Perl arrayref instead of returning results.	None
hashref	Create a temporary Perl hashref instead of returning results.	None
wantarray	Return an array containing some extra column information.	No
hide	Suppress any output text that would ordinarily be returned from this tag. (This universal parameter was introduced with Interchange version 5.5.2.)	No
interpolate	Parse Interchange tags, included in the body text, before this container tag executes.	No
reparse	Process any Interchange tags found in the text output from this tag.	Yes

The following parameters are only available to this tag if "type=list":

Parameter	Description	Default
form	Arguments for use in [more-list] URIs.	None
label	Label for use with multiple simultaneously-active search objects.	current
more_template	Override the default [more-list] display template.
prefix	Override the "PREFIX" used by the various [PREFIX-*] sub-tags.	sql
safe_data	Allow the "[" character to remain intact in returned data.	No

See the search parameters page for more parameters. Note that you must use the two-character short code, rather than the full name, with parameters listed on that page. Here is a quick list of some of the search parameters that are useful in this context:

Parameter	Description	Default
fm	Return results starting from the specified row number.	0
ml	Number of results to display on each page.	50
mm	Maximum number of results to display.	None
sp	Page that should be used to display the results.	results
st	Set to "db" for a database search or "text" for a text file search.	None

Examples

Perl example

my $sql = q{
SELECT sku, description, price
FROM products
WHERE price < 10
ORDER BY price DESC
};

my $result_array = $Tag->query({ sql => $sql });

my ($same_results, $col_name_hash, $col_name_array) = $Tag->query({ sql => $sql });

my $result_hasharray = $Tag->query({
sql => $sql,
hashref => 'my_results',
});

# $Tmp->{my_results} == $result_hasharray
# $Tmp->{''} == $result_hasharray == $same_results

my $out = "The returned structure is\n";
$out .= $Tag->uneval($results);

#
# loop through each of the rows
#
foreach (@$hash_results) {
$out .= "sku: $_->{sku}<br>";
$out .= "price: $_->{price}<br>";
$out .= "description: $_->{description}<br>";
$out .= "<br>\n";
}

or similarly with positional parameters:

$Tag->query($sql, $attribute_hash_reference, $body);

Note

The database access methods available via the table objects in the %Db hash are more efficient than this tag if you want to perform queries from within a block of Perl code.

Description

The [query] tag allows you to run SQL queries. If you are using an SQL database server then the tag will pass your SQL statement directly to the database and return the result.

If your table is not in an SQL database server (GDBM, text, LDAP and in-memory tables, for example) then Interchange will internally convert the query to an Interchange search specification and run your query that way. This means that you can use simple SQL queries regardless of the underlying database implementation.

As [query] is a "looping tag", its body text can contain a number of sub-tags to help you format the results. See the looping tags and sub-tags category for a list of available sub-tags. You should also read the looping tags introduction.

Note

Loop sub-tags are only available to this tag if the "type" parameter is set to "list".

Perl usage

If you using this tag via $Tag->query(), then you must be sure to set the tables parameter properly in the enclosing [perl] tag.

The types that return text to a page, such as "row_count", "html" and "textref", return an appropriate value as usual. Note that you may also have access to the query results, as an array reference in $Tmp->{''}, for the life of the page.

If you do not set a type then the results will be returned as an array of array references, as the default with no type is arrayref="".

If you call $Tag->query() in SCALAR context, and set "arrayref" or "hashref", it will return your results as a reference to an array of either arrayrefs or hashrefs, respectively (i.e., the same data structures you would get from Perl's DBI module, when calling fetchall_arrayref() and fetchall_hashref()).

In "type=list" context, the first returned element will be the aforementioned reference to your results. The second element will be a hash reference to your column names and the third element will be an an array reference to the list of column names.

See the Perl example at the top of this page.

Technical note

The Perl $Tag->query() call works a little differently in GlobalSubs and UserTags than within [perl] tags. Specifically, in a GlobalSub or global UserTag, if you call $Tag->query() in "type=list" context and want the three references (i.e., results, column hash and column array), then you need to set "wantarray=1".

Note

The database access methods available via the table objects in the %Db hash are more efficient than this tag if you want to perform queries from within a block of Perl code.

[PREFIX-quote] sub-tag

You may wrap values in a [PREFIX-quote]...[/PREFIX-quote] container, within the "sql" parameter text, instead of hard-quoting values. This is a security aid which helps prevent SQL-injection attacks.

The "PREFIX" defaults to "sql", and can be renamed using the "prefix" parameter.

See the Tag usage example.

Availability

This sub-tag was introduced in version 5.3.2, and is therefore not available for use with any earlier Interchange version.

All Interchange versions provide the inferior "sql" filter.

Parameters

sql

This is the text of your SQL query. The standard Interchange quoting rules apply. For example, use double quotes ("...") if you want to interpolate Interchange tags within your SQL statement or back ticks (`...`) to calculate a value, etc.

[query sql=|
SELECT description, price
FROM products
WHERE price < 10
|]
...
[/query]

You must set "more=1" to properly paginate results from list queries If you do not set "more=1", then [more-list] links to later pages will merely redisplay the first page of your results.

failure

Return the provided text if the query fails for any reason.

table

The table parameter sets the table to use for the query. The default is the first table listed in the DefaultTables local configuration directive, which is typically the "products" table.

type

If you are not setting the "arrayref" or "hashref" parameters, then this parameter defines the method this tag will use to return its results. The type should be one of the following:

Type

Returns

html

The html type returns the results in an HTML table. You will need to supply the enclosing "<table>" and "</table>" HTML tags. For example:

<table>
[query
type=html
sql=|
SELECT sku, description, price
FROM products
WHERE price > 12
ORDER BY price DESC
|][/query]
</table>

list

This allows you to use sub-tags to format the query's output and pagination.

See the Looping tags and sub-tags category for an introduction and a list of available sub-tags.

row_count

This causes the tag to only return the number of rows in the query result.

textref

This causes the tag to return a the query results as a serialised array of array references that Perl can evaluate with its eval() function. For example:

my $text = $Tag->query({
sql => 'SELECT sku, description, price FROM products',
type => 'textref',
});
my $rows = eval($text);
my $price = $rows->[0]->[2];

If you do not specify a type then this tag will create an arrayref as if you had set an arrayref="" parameter.

list_prefix

Requires type=list.

Setting "list_prefix=bar" overrides the default region tag name of "list". The best way to show this is by example. Compare the following two examples of list queries, the first using the defaults and the second with explicitly-set prefix and list_prefix parameters.

[query
type=list
more=1
ml=10
sql=|
SELECT sku, description, price
FROM products
WHERE price < 20
|]
[on-match]Matched<br>[/on-match]
[no-match]Not Found<br>[/no-match]

[list]
[sql-code] - [sql-param description] - [sql-price]<br>
[/list]

[more-list]
[more]
[/more-list]
[/query]

[query
type=list
more=1
ml=10
prefix=foo
list_prefix=bar
sql=|
SELECT sku, description, price
FROM products
WHERE price < 20
|]
[bar-on-match]Matched<br>[/bar-on-match]
[bar-no-match]Not Found<br>[/bar-no-match]

[bar]
[foo-code] - [foo-param description] - [foo-price]<br>
[/bar]

[bar-more-list]
[more]
[/bar-more-list]
[/query]

arrayref

Create a temporary Perl arrayref instead of returning results. The arrayref can be retrieved using $Tmp->{name}, where "name" is the name passed as a value to this parameter. The arrayref will also be available in $Tmp->{''}.

hashref

Create a temporary Perl hashref instead of returning results. The hashref can be retrieved using $Tmp->{name}, where "name" is the name passed as a value to this parameter. The hashref will also be available in $Tmp->{''}.

wantarray

This is relevant only when calling $Tag->query() within global Perl code, such as a GlobalSub or a global UserTag, where

Safe is not in use. In these cases, setting "wantarray=1" allows the following call to return references as it would if called within an ordinary [perl] tag.

$Tag->query({ wantarray => 1, ... });

Note

This does not force list context if you call $Tag->query in SCALAR context.

Here is another example of the use of the array references, from a global UserTag:

my $sku = 'os28044';
my $table = 'products';
my $sql = qq{
SELECT description, price
FROM $table
WHERE   sku = '$sku'
};
my ($results, $col_name_hashref, $col_name_arrayref) = $Tag->query({
wantarray => 1,
sql => $sql,
table => $table,
});

#
#   this will get the first column (description)
#
my $out = 'description: $results->[0]->[0]<br>";

#
#   and this the second column (price)
#
$out .= 'price: $results->[0]->[1]<br>";

#
# this will tell us the position, in the $results array,
# of the price column
#
$out .= 'position of price column: $col_name_hashref->{price}<br>";

return $out;

If the [query] returns more than one row, then the second row's description column could be found at be "$results->[1]->[0]".

Technical note

The ordinary [query]...[/query] usage forces SCALAR context on the call, and suppresses the return value for those types that would return references if $Tag->query() were called within a [perl] tag.

The wantarray parameter is required because GlobalSubs and UserTags are also affected by this.

form

You can use this to pass one or more form variables in the pagination links of a [more-list]. For example, use form="foo=bar" to include "foo=bar", as a URI argument in each of the pagination links.

Warning

Form variables, specified using this parameter, will not be available to the [cgi] tag in the initial result set. This is because the query returns the first page directly and no pagination link will have been followed.

label

If you are setting up multiple simultaneously-active search objects within a page, then this allows you to distinguish between them. The default label is "current". Most people will not need this.

more_template

Override the default display template value, used by the [more-list] sub-tag.

prefix

Setting "prefix=foo" overrides the "PREFIX" used by the various [PREFIX-*] sub-tags, and also by the [PREFIX-quote] container.

The default "prefix" is "sql".

safe_data

Setting "safe_data=1" allows the [sql-data] and [sql-param] sub-tag (etc.) to return values containing the "[" character.

Warning

Beware of reparsing issues. You should not use "safe_data" unless you really need it and you know what you're doing.

query

Summary

Examples

Tag usage example

Perl example

Description

Perl usage

[PREFIX-quote] sub-tag

Parameters

sql

more

failure

table

type

list_prefix

arrayref

hashref

wantarray

form

label

more_template

prefix

safe_data

Licence