A MiniVend
SQL database can be accessed with the same tags as any of the other databases can. In addition to those standard methods, direct
SQL support is provided with the
[sql function] TEXT [/sql identifier]
tag set. The MiniVend database identifier only needs to be set if the table resides in a different database than the main products database -- if you don't use
SQL for the products database you will
ALWAYS need to set it.
For any of these, you may pass arguments with the [arg]argument[/arg]
quoting method, which substitutes the contained value for successive values
of %s in the query. For example:
[sql html]
[arg][value passed_title][/arg]
[arg][value passed_artist][/arg]
select code, title, title from products
where artist = %s and title = %s
[/sql]
* optional parameter
SQL Any valid SQL query (usually a select)
[sql array] SQL [/sql identifier*]
A complete array of arrays, suitable for eval by Perl, can be returned by this query. This tag pair encloses any valid SQL query, and returns the results (if any) as a string representing rows and columns, in Perl array syntax. If placed in an embedded Perl area as:
[perl interpolate=1]
my $string =<<'EOF'; [sql array]select * from arbitrary where code <= '19'[/sql arbitrary]
EOF
my $ary = eval $string;
my $out = '';
my $i;
foreach $i (@$ary) {
$out .= $i->[0];
$out .= "<BR>";
}
$out;
[/perl]
NOTE: The 'EOF' string terminator must START the line, and not have trailing characters. DOS users, beware of carriage returns!
[sql hash] SQL [/sql identifier*]
A complete hash of hashes, suitable for eval by Perl, can be returned by this query. This tag pair encloses any valid SQL query, and returns the results (if any) as a string representing rows and columns, in Perl associative array, or hash, syntax. If placed in an embedded Perl area as:
[perl interpolate=1]
my $string =<<'EOF'; [sql type=hash base=arbitrary]select * from arbitrary where code <= '19'[/sql]
EOF
my $hash = eval $string;
my $out = '';
my $key;
foreach $key (keys %$hash) {
$out .= $key->{field1};
$out .= "<BR>";
}
$out;
[/perl]
Any arbitrary SQL query can be passed with this method. No return text will be sent. This might be used for passing an order to an order database, perhaps on the order report or receipt page. An example might be:
[sql type=set base=orders interpolate=1]
insert into orders
values
('[value mv_order_number]',
'[value name escape]',
'[value address escape]',
'[value city escape]',
'[value state escape]',
'[value zip escape]',
'[value phone escape]',
'[item-list]
Item: [item-code] Quan: [item-quantity] Price: [item-price]
[/item-list]'
)
[/sql]
The values entered by the user are escaped, which prevents errors if quote characters have slipped into their entry.
[sql param] SQL [/sql identifier*]
A list of keys, or in fact any SQL fields, can be returned as a set of parameters suitable for passing to a program or list primitive. This tag pair encloses any valid SQL query, and returns the results (if any) as a series of space separated fields, enclosed in quotes. This folds the entire return into a single row, so it may be used as a list of keys.
This tag is deprecated.
[sql html] SQL [/sql identifier*]
This tag returns a set of HTML table rows with bold field names at the top, followed by each row in a set of table cells. The <TABLE> and </TABLE> tags are not supplied, so you can set your own border and shading options. Example:
<TABLE BORDER=2> [sql type=html]select * from arbitrary where code > '19' order by field2[/sql] </TABLE>
<TABLE BORDER=2>
<TR><TH><B>SKU</B></TH><TH><B>Description</B></TH><TH><B>Price</B></TH>
[sql type=list
query="select code,desc,price from arbitrary where code > '19' order by field2"]
<TR>
<TD>[page [sql-code]][sql-code]</A></TD>
<TD>[sql-param desc]</TD>
<TD>[sql-param price]</TD>
</TR>
[/sql]
</TABLE>
This is the fastest way to return data from an SQL query.
It uses the same tags as in the [loop ....], except prefixed with sql. Available are the following, in order of interpolation:
[sql-param n] Field n of the returned query (in the row) [sql-param name] Field "name" of the returned query (in the row) [if-sql-field fld] Returns enclosed text only product field not empty [/if-sql-field] Terminator for above [if-sql-data db fld] Returns enclosed text only if data field not empty [/if-sql-field] Terminator for above [sql-increment] Returns integer count of row [sql-code] The first field of each row returned [sql-data db fld] Database field for [sql-code] [sql-description] Product description for [sql-code] [sql-field fld] Product field for [sql-code] [sql-link] Same as item-link [sql-price q*] Price for [sql_code], optional quantity q