|
The <SQL>
statement is used to execute SQL statements in
Vortex. In our patent
example, it's used in the <search>
function to search
the patent
table with the user's query from the form:
<SQL ROW SKIP=$skip MAX=10
"select pcountry, pnumber, pdate, id
from patent
where pabstract like $query and pdate >= $when
order by pdate asc">
...
</SQL>
|
The ROW
, SKIP
and MAX
flags we'll explore
in a moment. After them comes the SQL statement that we want to
execute, a select
in this case.
Returned variables
Columns that are returned by the SQL statement are assigned to
Vortex variables of the same name. In our case, we'll get back $pcountry
, $pnumber
and $pid
from our
selected columns. The statements inside the <SQL>
- </SQL>
block are executed once for every returned row, with the
row variables updated each time. The script uses this to print out
each patent number with a link.
ROW
flag
Normally when a <SQL>
loop finishes, the returned
variables are appended in lists. This makes the results available to
be <LOOP>
ed over again, without having to re-select them.
In our case we're only printing the results once, so we give the
ROW
flag: this keeps the result vars as single values.
In effect, vars are by default assigned at each row like <$x = $x 'newvalue'>
, whereas ROW
assigns them like
<$x = 'newvalue'>
. Because of the memory savings, ROW
should always be used unless the vars are
specifically needed again later. (Also, without ROW
the
returned vars are implicitly in a <LOOP>
statement.)
Limiting and skipping rows
The SKIP
flag says how many results to initially skip
before returning rows to Vortex. In our script, we skip $skip
rows, because on the next page of results, we want to skip the previous
page's rows. More on that when we explore pagination.
The MAX
flag is the maximum number of rows to return
(after the skip). We're only displaying 10 rows per page in our
script.
Renaming complex fields
As a side note, sometimes we might select a complex field name as
a result, eg. select max(i) from tbl
. The problem is max(i)
is not a valid Vortex variable name. We resolve this
by renaming the field inside the SQL statement. Here we rename it
to maxi
:
<SQL "select max(i) maxi from tbl">
...
</SQL>
|
|