Special SQL Variables

The $loop variable is set in a SQL statement as it is in a LOOP, though any SKIPped rows are not counted in it. At the start of the first iteration, its value is 0, and it is incremented at each iteration. At the end of the loop it is thus the number of rows returned by the SQL statement.

The $next variable is also set at every iteration: it is the number of SKIPped rows plus $loop plus 1. This is a convenient way to number hits counting from 1, and consistently across result pages with varying SKIP values. When the SQL loop ends, $next is set to the number of SKIPped rows plus the number of iterated rows: the value to use for SKIP in the next SQL statement, for the next result page.

When the SQL statement is first executed, at the start of the first iteration, the $indexcount variable is set to an estimate of how many total rows (i.e. as if no SKIP or MAX) the query will return. Its value is the number of matching rows found in the index(es) used by the query (if determinable). It is only an estimate, since further processing may eliminate some rows from the final result set. The last value of $next, in contrast, is always an exact row count (provided the loop was not exited early due to MAX or BREAK). However, since $indexcount is set at the start of the statement, it can be used to quickly indicate the (probable) number of result rows without looping through them all to get $loop. It is also not affected by the MAX value, as $next and $loop are:

<SQL SKIP=$skip MAX=10
     "select Title from books where Title like $query">
  <IF $loop eq 0>  <!-- print this before the first row -->
    First 10 hits out of a possible $indexcount hits:
  </IF>
  $Title
</SQL>

Note that if no indexes exist, none are used by the query, or an index is used but in a way that precludes pre-counting (e.g. in "bubble-up" mode), then $indexcount will be 0.

In versions after 3.0.942800000 19991116, the $rows.min and $rows.max variables provide more information than $indexcount alone. They are set to the minimum and maximum number of total rows the query will return, respectively, or -1 and -2 (i.e. less than 0) if unknown. $rows.max is similar to $indexcount: an upper limit to the result row count (ignoring SKIP and MAX). However, $rows.max is updated once it is possible to reflect the final (post-processed) count, or set to less than 0 if no index/row count information is available. $rows.min is a lower limit, but also contains a one-row look-ahead: if $rows.min is greater than $next at any time (inside the <sql> loop or after the end), then at least one more row is definitely available. This makes it easy to check when to print a "Next page" link.

The $rows.min and $rows.max variables are updated every iteration and at the end of the SQL loop. Once the total count is known exactly, both variables are equal. Until then, they provide a "window" that bounds the result count, which closes as rows are processed until the exact count is known. If the exact count is known initially - as in the case of a fully indexed query - then $rows.min will be equal to $rows.max from the first row onwards. This helps eliminate the guessing game of when $indexcount is accurate or not.

  In version 6 and later, the $sqlresult.returnedmin and $sqlresult.returnedmax variables are set, similarly to $rows.min and $rows.max. However, they are often more accurate and consistent, as their information is obtained directly from the Texis SQL engine, and thus are recommended over $rows.min/$rows.max.

Also in version 6 and later, $sqlresult.matchedmin and $sqlresult.matchedmax are set. These are similar to $sqlresult.returned..., but are the min/max total rows matched - i.e. by the WHERE clause, before likeprows, GROUP BY etc. reduce the returned result count. For example, in a LIKEP query, a maximum of 100 top-ranked results are generally returned (the default for likeprows), yet many more results might have actually matched the query. Thus, $sqlresult.returnedmin may not exceed 100, whereas $sqlresult.matchedmin may be much greater. The $sqlresult.returned... variables can thus be used to compute pagination links, and the $sqlresult.matched... variables used to compute the total hits for the ... of N hits message.

Also in version 6 and later, $sqlresult.indexcount is set, similarly to $indexcount, but is -1 (not 0) when unknown, to distinguish from 0 (known but no results).

The $null variable has significance for parameters, as noted above (see Parameter Substitution, here).

EXAMPLE

<$query = "John">
<TABLE>
  <TR><TH> Name </TH> <TH> Address </TH> <TH> Phone </TH></TR>
  <SQL "select Name, Address, Phone
        from customer
        where Name like $query">
    <TR>
      <TD> $Name </TD>
      <TD> $Address </TD>
      <TD> $Phone </TD>
    </TR>
  </SQL>
</TABLE>
There are $loop matching customers.

CAVEATS
Multiple-argument SQL commands were added in version 2.1.899200000 19980630. Nestable SQL statements were added in version 2.1.873500000 19980905.

Column variables returned by the SQL command are cleared first before the loop starts, i.e. previous values are lost. However, if no rows are returned by the command, then the variables are not cleared, since it is unknown what variables would be returned.

Since variables in the SQL command become Texis parameters, they are only permitted where parameters are allowed, e.g. field values, arguments to where clauses, etc. In particular, the SQL command name (e.g. select, insert) cannot be a variable. This is for security (see Parameter Substitution, here).

Care must be taken when passing the SQL command as a variable instead of a string literal (see SQL Command Construction, here) that the command cannot be abused by the user, e.g. all user variables should only be SQL parameters.

The $null variable is not inherited from the CGI environment; it must be explicitly set by the script. It is important to note that unset variables (no values) are treated as variables with a single empty-string value ("") for the purposes of SQL parameters and checking against $null.

The $indexcount variable is only an early estimate. If no index can be used to pre-count results, it will be 0. If post-processing of rows is required, it may be an overestimate.

Only variables that are parameters to a where clause can legally be dropped when empty (or matching $null). All other variables in the SQL command (e.g. field values for insert) must be set.

While SQL statements may be nested, this is generally not necessary and can degrade performance considerably if misused. A SQL join statement, or SQL statements in series, are usually better options.

Assigning to a returned variable while inside the loop will only modify the current value of the variable.

An exception to the one-row look-ahead nature of $rows.min / $sqlresult....min is non-select statements: looking ahead can cause more than the desired rows to be deleted/inserted/updated, so the look-ahead is not done.

SEE ALSO
DB, USER, PASS, SQLCACHE, vxinfo, pagelinks, LOOP, BREAK, TIMPORT