|
| 9.5.4 Altering the Query with $null |
|
Our patent example takes
two search arguments from the user, $query
and $when
, which we use in our SQL statement:
<$null = "">
<SQL ROW SKIP=$skip MAX=10
"select pcountry, pnumber, pdate, id
from patent
where pabstract like $query and pdate >= $when
order by pdate asc">
|
But what if the user only enters a text query, with a blank date?
We need to remove the "and pdate >= $when
" part of the SQL
clause. Similarly, if the text $query
was blank, we'd need
to remove "pabstract like $query
" and leave in the other
part.
The $null variable
It's a hassle to have to generate every permutation of the SQL
where
clause based on what the user entered. That's
where the special variable $null
comes in.
By setting $null
, we automatically drop parts of the
statement associated with unused parameters. If $null
is
set to contain any values (one empty string in our case), then any <SQL>
parameters that match any of its values are dropped from
the query, including the clause they're in. Unset (no-value)
variables are considering empty strings for this comparision.
That's why we set <$null = "">
just above the statement.
Now, if the user leaves $when
empty, it will match a value
of $null
, and its clause will be dropped. The SQL run
by our <SQL>
statement above would actually become:
select pcountry, pnumber, pdate, id
from patent
where pabstract like $query
order by pdate asc
|
which is what we want (no date search), without us having to
re-work the SQL statement. Similarly, if $query
is empty,
the like
clause is dropped. Had we set <$null = "" "any">
,
then not only empty fields but the word "any
" entered by the user
would cause the clause to drop. Pretty nifty huh?
|