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 nbsp;= nbsp;$x nbsp;'newvalue'> , whereas ROW assigns them like <$x nbsp;= nbsp;'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 nbsp;max(i) nbsp;from nbsp;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>