Database Reference
In-Depth Information
Discussion
To validate input values against the contents of a lookup table, the techniques are some‐
what similar to those shown in
Recipe 12.8
for checking
ENUM
and
SET
columns. How‐
ever, whereas
ENUM
and
SET
columns usually have a small number of member values, a
lookup table can have an essentially unlimited number of values. You might not want
to read them all into memory.
Validation of input values against the contents of a lookup table can be done several
ways, as illustrated in the following discussion. The tests shown in the examples perform
comparisons against values exactly as they are stored in the lookup table. To perform
case-insensitive comparisons, convert all values to a consistent lettercase. (See the dis‐
cussion of case conversion in
Recipe 12.8
.)
Issue individual statements
For one-shot operations, test a value by checking whether it's listed in the lookup table.
The following query returns true (nonzero) for a value that is present and false other‐
wise:
$valid
=
$dbh
->
selectrow_array
(
"SELECT COUNT(*) FROM $tbl_name WHERE val = ?"
,
undef
,
$val
);
This kind of test may be suitable for purposes such as checking a value submitted in a
web form, but is inefficient for validating large datasets. It has no memory for the results
of previous tests for values that have been seen before; consequently, you execute a query
for every input value.
Construct a hash from the entire lookup table
To validate a large number of values, it's more efficient to pull the lookup values into
memory, save them in a data structure, and check each input value against the contents
of that structure. Using an in-memory lookup avoids the overhead of executing a query
for each value.
First, run a query to retrieve all the lookup table values and construct a hash from them:
my
%members
;
# hash for lookup values
my
$sth
=
$dbh
->
prepare
(
"SELECT val FROM $tbl_name"
);
$sth
->
execute
();
while
(
my
(
$val
)
=
$sth
->
fetchrow_array
())
{
$members
{
$val
}
=
1
;
}
Perform a hash key existence test to check a given value:
$valid
=
exists
(
$members
{
$val
});
