For more information... RTFM! - Merry Christmas
NAVIGATION
PAGES THAT LINK HERE
ACCOUNT LOGIN

You are not logged in

Powered by Interchange version 5.7.0

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

Tag usage example

[query
    type=list
    more=1
    ml=10
    sql=|
        SELECT  sku, description, price
        FROM    products
        WHERE   price < [sql-quote][value mv_arg][/sql-quote]
|]
[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]
Matches [matches] of [match-count] shown.<br>
[more]
[/more-list]
[/query]

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 (@$result_hasharray) {
    $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

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

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

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

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

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]

more

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 Link to an external page 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

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

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

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

Warning

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

Category:  Interchange tags
Last modified by: Kevin Walsh
Modification date: Monday 24 September 2007 at 10:46 AM (EDT)
Home  |  Legal nonsense  |  Privacy policy  |  Donations  |  Contact us